Full text search performance problem

Hi,
We are using Full Text Search on SQL 2000. The search performance is
fine.
Our problem is that when a record is added to the table, the average
time it takes before we can find it with a CONTAINS is 20 seconds.
This is with only one user connected.
The tables has only 200000 rows. The field is a VARCHAR of less than
200 characters.
I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
fast disks. If the exact spec is important I can find out.
Thanks for your help.
Sylvain
Sylvain,
Are you using "Change Tracking" with "Update Index in Background" and
concerned that once a record (row) is added that it takes 20 seconds before
a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
the record (row) where search_word exists?
If not, is your concern more about the total time it takes to return a
results using the above FTS query? If so, you may want to use CONTAINSTABLE
and the Top_N_Rank parameter, for example:
use Northwind
go
SELECT e.EmployeeID, e.LastName, ct.[RANK]
FROM Employees AS e
JOIN CONTAINSTABLE(Employees, Notes, 'French',10) AS ct ON e.EmployeeID
= ct.[KEY]
ORDER BY ct.[RANK] DESC
Note, the value 10 in the above query as this represents the Top N (10) by
Rank value. Using this parameter on containstable or freetexttable can
improve the FTS query performance, but you should experiment with the value
as if you set it too low, you can miss some results, see KB article 240833
(Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP"
at
http://support.microsoft.com//defaul...b;EN-US;240833 for more
info on this.
You should also review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well.
Regards,
John
"Sylvain Hamel" <redhotsly@.hotmail.com> wrote in message
news:4716a77b.0408311303.531320fa@.posting.google.c om...
> Hi,
> We are using Full Text Search on SQL 2000. The search performance is
> fine.
> Our problem is that when a record is added to the table, the average
> time it takes before we can find it with a CONTAINS is 20 seconds.
> This is with only one user connected.
> The tables has only 200000 rows. The field is a VARCHAR of less than
> 200 characters.
> I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
> fast disks. If the exact spec is important I can find out.
> Thanks for your help.
> Sylvain
|||John,

> Are you using "Change Tracking" with "Update Index in Background" and
Yes.

> concerned that once a record (row) is added that it takes 20 seconds before
> a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
> the record (row) where search_word exists?
This is exactly my problem. Within the first 20 seconds, the SELECT return
zero rows. After, it works fine and it's fast.
Regards,
Sylvain
|||Hi Sylvain,
I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
on this issue along with a repro (see the attached SQL script file) sometime
ago and have pinged him since with no reply <sigh>... While I'm sure he's
busy, I had hoped to get a better response on this issue from Microsoft...
Basically, SQL Server 2000 should push modified rows to the un-documented
system table sysfulltextnotify and then to the MSSearch controlled FT
Catalog for updating. I've been told that this push should happen within 1
second, but can lag depending upon other activity on the server as well as
within SQL Server, but 20 seconds is much longer than my repro
demonstrates...
The attached SQL script file demonstrates that even with inserting one row
can take up to 5 to 7 seconds with no other activity on the server and no
other activity within SQL Server. I had hoped to get this addressed and
hopefully filed as a bug, but that did not happen and may not happen as the
time to post the updated row to the MSSearch FT Catalog has never been
documented and so no guaranteed posting time was ever documented...
If you want I can pursue this again with the FTS Dev Lead, if this is
affecting your production server's FT Indexing and FT Search performance and
therefore leading to dissatisfaction for your users. Let me know if you want
me to pursue this.
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...[vbcol=seagreen]
> John,
>
> Yes.
before[vbcol=seagreen]
return
> This is exactly my problem. Within the first 20 seconds, the SELECT return
> zero rows. After, it works fine and it's fast.
> Regards,
> Sylvain
>
begin 666 FTS_with_Change_Tracking_pooling.sql
M#0HM+0E&:6QE;F%M93H)1E137W=I=&A?0VAA;F=E7U1R86-K:6YG7W!O;VQI
M;F<N<W%L#0HM+0E0=7)P;W-E.@.E4;R!T97-T($9422!A;F0@.1E13('=I=&@.@.
M0VAA;F=E('1R86-K:6YG(&%N9"!U<&1A=&4@.:6YD97@.@.:6X@.8F%C:V=R;W5N
M9"!A;F0@.<WES9G5L;'1E>'1N;W1I9GD@.<&]O;&EN9PT*+2T)16YV:7)O;65N
M=#H)57-I;F<@.4U%,(#(P,# @.4U S(&]N(%=I;F1O=W,@.4V5R=F5R(#(P,#,@.
M4E1-#0HM+0E-;V1I9FEE9#H),3$Z,S @.04T@.,R\Q,2\R,# T#0H-"@.T*#0IU
M<V4@.<'5B<PT*9V\-"G-E;&5C="! 0'9E<G-I;VX@.+2T@.36EC<F]S;V9T(%-1
M3"!397)V97(@.(#(P,# @.+2 X+C P+C<V," H4U S*2!A;F0@.."XP,"XU,S0@.
M*%-0,BD-"F=O#0II9B!E>&ES=',@.*'-E;&5C=" J(&9R;VT@.<WES;V)J96-T
M<R!W:&5R92!I9" ](&]B:F5C=%]I9"@.G1E135&%B;&4G*2D-"B @.9')O<"!T
M86)L92!&5%-486)L90T*9V\-"D-214%412!404),12!&5%-486)L92 H#0H@.
M($ME>4-O; EI;G0@.241%3E1)5%D@.*#$L,2D@.3D]4($Y53$P@.#0H@.(" @.0T].
M4U1204E.5"!&5%-486)L95])1%@.@.4%))34%262!+15D@.0TQ54U1%4D5$+ T*
M("!497AT0V]L"71E>'0@.3E5,3"P-"B @.26UA9V5#;VP):6UA9V4@.3E5,3"P-
M"B @.17AT0V]L"6-H87(H,RD@.3E5,3"P@.+2T@.8V%N(&)E('-Y<VYA;64-"B @.
M5&EM95-T86UP0V]L"71I;65S=&%M<"!.54Q,( T**0T*9V\-"@.T*+2T@.5')U
M;F-A=&4@.1&%T82P@.:68@.;F5C97-S87)Y+BX-"BTM(%1254Y#051%(%1!0DQ%
M($944U1A8FQE#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G36%R>2!H860@.
M82!L:71T;&4@.;&%M8B<L(')E<&QI8V%T92@.G,'@.Q)RPQ,# I+" G='AT)RP@.
M3E5,3"D-"DEN<V5R="!&5%-486)L92!V86QU97,@.*"=->2!D;V<@.8F%R:V5D
M(&QO=61L>2<L(')E<&QI8V%T92@.G,'@.W)RPS,# I+" G='AT)RP@.3E5,3"D@.
M#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G5&AE(&9O>"!J=6UP960@.; W9E
M<B!T:&4@.9F5N8V4G+"!R97!L:6-A=&4H)S!X."<L.# P*2P@.)W1X="<L($Y5
M3$PI#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G22!D;W9E(&EN=&\@.=&AE
M('!O;F0G+"!R97!L:6-A=&4H)S!X,B<L,3 P*2P@.)W1X="<L($Y53$PI#0H-
M"@.T*+2T@.1G5L;"U497AT($EN9&5X:6YG("A&5$DI('-E='5P('5S:6YG(")#
M:&%N9V4@.5')A8VMI;F<B(&%N9" B57!D871E($EN9&5X(&EN($)A8VMG<F]U
M;F0B+BXN#0IE>&5C('-P7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G
M+"=C<F5A=&4G#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L
M)V-R96%T92<L)T944T-A=&%L;V<G+"=&5%-486)L95])1%@.G#0IE>&5C('-P
M7V9U;&QT97AT7V-O;'5M;B G1E135&%B;&4G+"=497AT0V]L)RPG861D)R M
M+2!A<W-U;65S($5N9VQI<V@.@.;&%N9W5A9V4@.=&5X= T*97AE8R!S<%]F=6QL
M=&5X=%]C;VQU;6X@.)T944U1A8FQE)RPG26UA9V5#;VPG+"=A9&0G+" P># T
M,#DL("=%>'1#;VPG(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN
M(&%N9"!C;VYF:7)M('9I82 B36EC<F]S;V9T(%-E87)C:"(@.<V]U<F-E(&5N
M=')I97,@.:6X@.=&AE('-E<G9E<B=S($%P<&QI8V%T:6]N($5V96YT(&QO9RXN
M+@.T*15A%0R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?
M8VAA;F=E7W1R86-K:6YG)PT*+RH@.+2T@.<F5T=7)N<R H;VX@.4U%,(#(P,# @.
M4U S*0T*5V%R;FEN9SH@.5&%B;&4@.)T944U1A8FQE)R!D;V5S(&YO= "!H879E
M('1H92!O<'1I;VX@.)W1E>'0@.:6X@.<F]W)R!E;F%B;&5D(&%N9"!H87,@.9G5L
M;"UT97AT(&EN9&5X960@.8V]L=6UN<R -"G1H870@.87)E(&]F('1Y<&4@.:6UA
M9V4L('1E>'0L(&]R(&YT97AT+B!&=6QL+71E>'0@.8VAA;F=E('1R86-K:6YG
M(&-A;FYO="!T<F%C:R!74DE4151%6%0@.;W(@.55!$051%5$585"!O< &5R871I
M;VYS( T*<&5R9F]R;65D(&]N('1H97-E(&-O;'5M;G,N#0HJ+PT*15A%0R!S
M<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?8F%C:V=R;W5N
M9%]U<&1A=&5I;F1E>"<-"F=O#0H-"@.T*+2T@.16YV:7)O;65N=#H@.4U%,(#(P
M,# @.4U R(&]N(%=I;C)+("8@.4U%,(#(P,# @.4U S(&]N(%=I;C(P,#,-"@.T*
M+2T@.4F5P;R!I;G-T<G5C=&EO;G,Z#0HM+2 Q+B!#:&%N9V4@.=&AE(%=!251&
M3U(@.1$5,05D@.)S P.C P.C!8)R!8('9A;'5E+"!S=&%R=&EN9R!A=" Q('-E
M8V]N9"!A;F0@.:6YC<F5M96YT:6YG('5N=&EL('1H92!S96QE8W0@.< F5T=7)N
M<R Q(')O=RX-"BTM(#(N($AI9VAL:6=H="!T:&4@.8F5L;W<@.8V]D92!A;F0@.
M97AE8W5T92X-"@.T*+2T@.26YS97)T(&%D9&ET:6]N86P@.86YD('5N:7%U92!W
M;W)D('=I=&@.@.0U0@.86YD(%5):4(@.96YA8FQE9" M($1E;&5T92P@.26YS97)T
M(&%N9"!I;F-R96%S92!W86ET9F]R(&1E;&%Y('5N=&EL('-E;&5C=" Q(')O
M=PT*1$5,151%($944U1A8FQE('=H97)E(%1E>'1#;VP@.;&EK9 2 G)71E>'0E
M)R M+2!D96QE=&4@.:6YS97)T960@.<F]W+"!I9B!N96-E<W-A<GD-"F=O#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#4G+"!.54Q,+" G9&]C)RP@.3E5,3"D@.#0IG;PT*5T%)5$9/4B!$14Q!62 G
M,# Z,# Z,#4G("TM('=A:70@.9F]R('@.@.<V5C;VYD<R!A;F0@.=&AE;B!R=6X@.
M4U%,($944R!Q=65R>2!T;R!G970@.97-T:6UA=&4@.;V8@.<F5T=7)N('1I;64-
M"G-E;&5C="!497AT0V]L(&9R;VT@.1E135&%B;&4@.=VAE<F4@.8V]N=&%I;G,H
M5&5X=$-O;"PG=&5S="<I#0HO*B M+2!W:71H(%=!251&3U(@.1$5,05D@.<V5T
M('1O(#4@.<V5C;VYD<SH-"B@.Q(')O=RAS*2!A9F9E8W1E9"D-"B@.Q(')O=RAS
M*2!A9F9E8W1E9"D-"@.T*5&5X=$-O;" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.#0HM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2 -"E1E<W0@.5$585"!$871A
M(&9O<B!R;W<@.-0T**#$@.<F]W*',I(&%F9F5C=&5D*0T**B\-"@.T*+2T@.4W5M
M;6%R>3H@.37D@.=&5S=&EN9R!I;F1I8V%T97,@.=&AA="!O;FQY( &%F=&5R(&$@.
M-2!T;R V('-E8V]N9',@.=V%I=&9O<B!D96QA>2!W:6QL('1H92!S96QE8W0@.
M<F5T=7)N(&$@.:&ET(&%N9"!I9B!Y;W4@.:6YC<F5A<V4-"BTM(" @.(" @.(" @.
M('1H92!W86ET9F]R(&1E;&%Y('1O(#<@.<V5C;VYD<RP@.:70@.<W1I;&P@.;6%Y
M(&YO="!C;VYS:7-T96YT;'D@.<F5T=7)N(#$@.<F]W+B!!9&1I=&EO;F%L;'DL
M('-O;65T:6UE(&5V96X@.:68@.>6]U(&1O('5S90T*+2T)(" @.(#4@.<V5C;VYD
M<RP@.86YD(&EF('EO=2!R92UR=6X@.=&AE(&AI9VAL:6=H=&5D( '%U97)Y+"!Y
M;W4@.;6%Y('-T:6QL(&YO="!G970@.82!R971U<FX@.;V8@.,2!R;W<N#0H-"@.T*
M#0H-"B\J("TM($-L96%N('5P.B!5;BUC;VUM96YT('1H92!F;VQL;W=I;F<@.
M8V]D92!T;R!R96UO=F4@.=&AE($94($-A=&%L;V<L('1A8FQE<PT*=7-E(%!U
M8G,-"F=O#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L)V1R
M;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L;V<@.)T944T-A=&%L;V<G+"=D
C<F]P)PT*9')O<"!T86)L92!&5%-486)L90T*9V\-"BHO#0H`
`
end
|||John, we have opened a call with microsoft regarding this issue. I'll let you
know. Thank you for your help.
Sylvain
"John Kane" wrote:

> Hi Sylvain,
> I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
> on this issue along with a repro (see the attached SQL script file) sometime
> ago and have pinged him since with no reply <sigh>... While I'm sure he's
> busy, I had hoped to get a better response on this issue from Microsoft...
> Basically, SQL Server 2000 should push modified rows to the un-documented
> system table sysfulltextnotify and then to the MSSearch controlled FT
> Catalog for updating. I've been told that this push should happen within 1
> second, but can lag depending upon other activity on the server as well as
> within SQL Server, but 20 seconds is much longer than my repro
> demonstrates...
> The attached SQL script file demonstrates that even with inserting one row
> can take up to 5 to 7 seconds with no other activity on the server and no
> other activity within SQL Server. I had hoped to get this addressed and
> hopefully filed as a bug, but that did not happen and may not happen as the
> time to post the updated row to the MSSearch FT Catalog has never been
> documented and so no guaranteed posting time was ever documented...
> If you want I can pursue this again with the FTS Dev Lead, if this is
> affecting your production server's FT Indexing and FT Search performance and
> therefore leading to dissatisfaction for your users. Let me know if you want
> me to pursue this.
> Thanks,
> John
>
>
> "Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
> news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...
> before
> return
>
>
|||You're welcome, Sylvain,
Yes, please do let me know what you find out from Microsoft!
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:8B6465AF-A649-4014-A066-44C146A81375@.microsoft.com...
> John, we have opened a call with microsoft regarding this issue. I'll let
you[vbcol=seagreen]
> know. Thank you for your help.
> Sylvain
> "John Kane" wrote:
Lead)[vbcol=seagreen]
sometime[vbcol=seagreen]
he's[vbcol=seagreen]
Microsoft...[vbcol=seagreen]
un-documented[vbcol=seagreen]
1[vbcol=seagreen]
as[vbcol=seagreen]
row[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
want[vbcol=seagreen]
and[vbcol=seagreen]
return[vbcol=seagreen]

Full text search help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full text Search doesnt find a specific string

Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.

Im guessing here, but I think it is looking for records having one or more "words" that start with 1 (e.g. 1hs, 1tv, 1rquut). I'm not certain that the process would find just "1").

That's my story and I'm sticking with it.

Regards,

hmscott|||Thanks for your suggestion. Could you do me one more favor and modify the SQL query that I had put up in the previuos post to return the record that I am talking about.sql

Full Text Index

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thankshelloplease help me !!!thanks|||

Yavari:

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thanks

I have no idea what you are asking. Could you rephrase your question(s?) differently?

fts within a single record

I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.I believe that you can do this with Full Text Search. You must, however, have a finite set of columns to search upon. You must also have a primary key for the Full Text Search engine to be able to identify the specific record with matching search criteria.

You must enable install and enable Full Text Search (it is not installed by default). You must then create the Full Text Search process (I cheat like crazy and use the wizard in EM; this is NOT recommended). It is recommended that you use QA and create the processes yourself.

You will need to populate the Full Text catalogue initially and then create a schedule to update the Full Text catalogue periodically.

Regards,

Hugh Scott

Originally posted by wooliewillie
I am hoping that someone has an "it's obvious" solution. I have a bunch (~100) of text fields in a single record. I want a user to be able to search all of the fields (within this single record) for a string, and to return the field number(s) of where the string was found. Is there a way to do this within the MSSQL server's built in fts catalog, (or some other product) or do I need to code this by hand? I could limit the searchable strings to a finite set, if needed.