question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig

question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

-

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

-

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?

Question about Merge Replication

How is it possible to use single database as a pull subscriber to one Merge Publication and as a Merge Publisher to few push subcriptions?! Actually i have a 3 tiers heirachy in which i need to update data in all directions. For this i need to use Merge replication at all layers but the problem is at the middle layer ( intermidiate Database ).

Please help me.

Regards,

Sorry, it's not clear what you're asking. Are you asking how merge handles changes at differenet nodes, or are you asking about internal details? And I'm not clear what you mean by "middle layer", or intermediate database.

I'm not sure what a three tier hierarchy in your scenario is, but you can have one machine be a publisher/distributor, and the other two machines be subscribers. Changes made at the publisher will flow down to the two subscribers. Changes made at the subscribers will flow up to the publisher, and if necessary, back down to the other subscriber.

|||Sorry if the question was not clear. In Simple words i need to implement a "Publishing-Subscriber Model". Means i need my subscriber be publisher for another subscriber.

Regards,|||What is the reason for needing to republish? You should only want to do this if you need to scale out. Is this what you're trying to accomplish?

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?
There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I think that it is a flaw. I tried casting '.' to integer and decimal and
got an error.
Russel Loski, MCSD.Net
"Izzy" wrote:

> SQL Server 2000
> SELECT ISNUMERIC('.')
> Returns 1
> Why would a single period evaluate to being numeric? Is this a flaw?
>
|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definition
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I suggest the following article:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '£', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definition
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I suggest the following article:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --

Question about ISNUMERIC function

SQL Server 2000
SELECT ISNUMERIC('.')
Returns 1
Why would a single period evaluate to being numeric? Is this a flaw?There has been ambiguity about this function for some time. According to BOL
it must be able to be evaluated to a valid integer, floating point number,
money or decimal data type. We can see that the '.' character works for
money, although not decimal, so is therefore (using definition above)
designated as numeric. Perhaps then the question becomes should it be
allowed to be casted into a money datatype? In the same way we could argue
for/against '', '$', '+' etc.
select cast('.' as money)
select cast('.' as decimal(10,5))
SELECT ISNUMERIC('.')
I'd be interested in the history of this one - why '.' is a valid money
value but not a valid decimal. Maybe something to do with old accounting
systems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I think that it is a flaw. I tried casting '.' to integer and decimal and
got an error.
Russel Loski, MCSD.Net
"Izzy" wrote:

> SQL Server 2000
> SELECT ISNUMERIC('.')
> Returns 1
> Why would a single period evaluate to being numeric? Is this a flaw?
>|||The thing that makes is valid (according to the definition in BOL) is that
it can be casted to the money datatype. So, according to the BOL definition
it is correct, but we could discuss the ability to run:
select cast('.' as money)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I understand why that returns 0.00, but you would think at least one
numerical digit would have to be present on either side of the decimal
point before SQL Server would allow the cast operation to complete
successfully.
The issue I had was trying to convert char(11) data to INT. Maybe I
should just write my own function and call it ISINT(), and have the
function return either a 0 or 1.
Does anyone have a function like that already written?
Paul Ibison wrote:
> The thing that makes is valid (according to the definition in BOL) is that
> it can be casted to the money datatype. So, according to the BOL definitio
n
> it is correct, but we could discuss the ability to run:
> select cast('.' as money)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I suggest the following article:
http://classicasp.aspfaq.com/genera...-isnumeric.html
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||LOL, I like the names of the new functions "IsReallyNumeric", too
funny.
Thanks for the suggestion.
Jens wrote:
> I suggest the following article:
> http://classicasp.aspfaq.com/genera...-isnumeric.html
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --sql

question about full-text search

Hi all,
I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
in my sql catalog.
I am runing queries agaist the catalog using CONTAINSTABLE
select [KEY] objDocumento, tf.RANK RANK from
CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
The problem is that some results returned by the query do not to mach
my search. How can this be possible.
For example i am looking for: 42.55.14.1.2
but some results containing similar words like
42.55.14.2
42.55.14.9.3
42.55.14.6.1
are also returned in the results set
Questions:
1.- Does the CONTAINSTABLE have problems to deal with period '.'
2.- How can I limit my search only to exact words
obs: I have also tried to put the search word into "" (ex:
"42.55.14.1.2") , but same results
Thank you very much
Alberto
Yes, it thinks the . is white space, so your search is done on 42 55 14 1 2.
Matches will be to 42 55 14 1 2 or 42!55!14!1!2, in other words any non
alphanumeric character between the numbers will yield a match.
however, I am unable to repro your problem. Can you tell me what version of
SQL you are running, and the version of the OS, also please issue the
following query and tell me what you get.
select @.@.language.
What word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132582567.679893.208520@.g14g2000cwa.googlegr oups.com...
> Hi all,
> I have a single table (TAB_FULL_TEXT_NET) , with only one field indexed
> in my sql catalog.
> I am runing queries agaist the catalog using CONTAINSTABLE
> select [KEY] objDocumento, tf.RANK RANK from
> CONTAINSTABLE(TAB_FULL_TEXT_NET, *, '42.55.14.1.2', 300) tf
> The problem is that some results returned by the query do not to mach
> my search. How can this be possible.
> For example i am looking for: 42.55.14.1.2
> but some results containing similar words like
> 42.55.14.2
> 42.55.14.9.3
> 42.55.14.6.1
> are also returned in the results set
> Questions:
> 1.- Does the CONTAINSTABLE have problems to deal with period '.'
> 2.- How can I limit my search only to exact words
> obs: I have also tried to put the search word into "" (ex:
> "42.55.14.1.2") , but same results
> Thank you very much
> Alberto
>
|||Thanks for your quick answer!
well I am using English (USA) as word breaker, but most of the text is
in portuguese ! (is that a problem?)
about the version, see below
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
what do you suggest me to deal with that situation?..
thanks
Alberto
|||Are the documents stored in the image data type column? Are there any html
tags in the content?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1132595252.224093.110370@.g49g2000cwa.googlegr oups.com...
> Thanks for your quick answer!
> well I am using English (USA) as word breaker, but most of the text is
> in portuguese ! (is that a problem?)
> about the version, see below
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> what do you suggest me to deal with that situation?..
> thanks
> Alberto
>
|||Hi again..
well, I have 2 tables in my catalog
Table 1: one (only text) field indexed
Table 2: One binary field indexed
about html text...not in the text field (table 1), but i am not sure
about Table 2, because users can include html files in the binary
field...
well, any help to deal with the period ('.') would be apreciated...
thanks
Alberto