Re: Fulltext search too slow
- From: "Daniel Crichton" <msnews@xxxxxxxxxxxxxxxx>
- Date: Thu, 2 Aug 2007 16:27:40 +0100
DC wrote on Thu, 02 Aug 2007 06:57:59 -0700:
Hi,
I am using an FT index on a column (varchar(4000) with an average
length of 100) in a table that contains about 1 million rows.
My tiny performance test looks like
declare @t datetime set @t = getdate()
select count(*) from containstable(table_name, expression,
'"searchthis*"')
print datediff (ms, @t, getdate())
If "searchthis" matches about 1000 rows, the search will usually take
about 2 seconds. But when it matches a lot of rows (some terms will
match up to 200.000 rows) then the search takes up to one minute. With
5.000 matches the search already takes about 15 seconds. It helps a bit
to restrict the ft search to Top-20000-of-rows but not enough and
I need many results (not to display them but to display how many
results of what type were found).
This is too slow for my application and I am sure the performance has
degraded for some reason. I am rebuilding the ft index every week and
reorganizing it every night. CPU and disk queues look relaxed so there
might be a less apparent bottleneck.
Does someone know where I should start looking for an enhancement, or
are these very rough performance figures normal for a 4 * 3 GHz
machine?
Thanks for any hint in advance,
Regards
DC
There are many factors that will affect the speed of your queries, so it's
hard to recommend any one thing to look for.
I've got a 2 * 3 Ghz server here that I run my websites on, with a product
database of around 750000 items. For searches with small numbers of results
(less than 5000 for instance), it runs pretty quickly and there is little
difference between using a wildcard and not. Trying to find a word that will
give me around 20000 results without a wildcard, and maybe double that with
one, is going to take me a while to nail down so I can't really run any sort
of comparison here myself, but I can tell you that if I search for "the" it
takes 28 seconds to return just over 300,000 results, and with "the*" it
takes only 13 secs to bring back just over 320,000 results. However, this
was after some intial runs so that data is cached - with uncached data it
takes 4 or 5 times as long. Of course, searching for "the" followed by
"the*" is not a great test, as some of the data from the first search will
remain cached for the second one to use, but with repeated runs of just
"the" I seem to get no improvement beyond 25 seconds or so, yet "the*" drops
from 13 secs to 6 secs with repeats.
You'll have to do some extensive testing to work out how to improve the
result speed, but the obvious one is to limit the number of results as far
as possible, and to try to only allow searching for words that will return
few results.
Given the results I've found in my own database I'm going to run some tests
for actual searches customers have entered and investigate whether
wildcarding them will in general reduce the search times (obviously at the
expensive of less exact matches being returned), however our average search
response time is under a second for customer entered searches anyway so it
may just be a waste of time for me (it's rare that a customer will enter a
single common word like "the" or "of" to search for, they tend to enter a
few words that return few matches).
Dan
.
- Follow-Ups:
- Re: Fulltext search too slow
- From: DC
- Re: Fulltext search too slow
- References:
- Fulltext search too slow
- From: DC
- Fulltext search too slow
- Prev by Date: Fulltext search too slow
- Next by Date: Re: Fulltext search too slow
- Previous by thread: Fulltext search too slow
- Next by thread: Re: Fulltext search too slow
- Index(es):
Relevant Pages
|
