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]