Re: Fulltext search too slow

DC wrote on Thu, 02 Aug 2007 09:36:01 -0700:


expression: "bank*", hits: 177558, elapsedms:
20746
expression: "citibank*", hits: 20478, elapsedms: 296

That was uncached. Why did "bank*" take so much longer compared to
"citibank*"?

Because "bank*" returned 177558 results, whereas "citibank*" returned only
20478. The smaller the result set the quicker the query will run, and it
appears that once you hit a large number of results (actual number will
depend on various factors, but lets say 30,000 for sake of argument here)
then there is a huge performance penalty. Also try generating some execution
plans and see if maybe some of them are generating parallelism - if so, and
you have hyperthreading CPUs, you might want to consider adjusting the
MAXDOP to the number of physical CPUs, or even try just 1.


Hm. This one was fine too, and the expressions were not cached. What
was the problem with the initial "bank*"?

It was the very first search, and also returned a lot of results - maybe
it's also due to having had the initial connections to the FTS service and
catalog caching included in it's execution time.

I read something about the ft engine trying to connect to the internet
for it's wordbreaker, maybe that has to do with it? I will check.

That should only affect indexing itself when parsing the rows into words,
not the searching.

Dan


.

Loading