SQL Server Full-Text Search - Very slow FTS query

Author

Very slow FTS query

Jarmo Lautamäki

2005-04-10, 9:23 am

Hi!
We have Sql Server 2000 in our server (NT 4). Server have 1GB RAM and 1,4GHz
processor. Our database have now about
+350.000 rows with information of images. Table have lot of columns
including information about image name, keywords, location, price, color
mode etc. So our database don´t include the images itself, just a path to
the location of every image. Keywords -field have data for example like
this:
cat,animal,pet,home,
child with pet,child. Now our search use Full-Text
Search which sounded like good idea in the beginning but now it have had
problems that really reduce our search engine´s performance. Also search
results are not exact enough. Some of our images have also photographer´s
name in keywords -column and if photographer´s name is, for example, Peter
Moss, his pictures appears in web-page when customer want to search "moss"
(nature-like) -pictures.
Another problem is that Full-Text Search started to be very slow when query
result contains thousands of rows. When search term gives maximum 3000
rows, search is fast but larger searches take from 6 to 20 seconds to
finish which is not good. I have noticed also that first search is always
very slow, but next ones are faster. It seems that engine is just
"starting" when first query started to run. But always when search result
start to go over about 3000 records, customer must wait tens of seconds.
That is too much.
Is there better and faster way to handle the queries? Is it better to
rebuild the database somehow and use another method to search than Full-
Text Search? I don´t know how to handle the database other way when every
image have about 10 to even 50 different keywords to search.
We have made web interface and search code with Coldfusion. Coldfusion
Server then take care of sending all queries to Sql Server.
I hope that somebody have some idea how to speed up our picture search. I
tried the OPTION(FAST n) -method, but it made the searh even slower. Because
it seems that Coldfusion don´t return any results before the whole query is
over.

Hilary Cotter

2005-04-10, 8:23 pm

The best way to handle results coming back from other columns is to only
search on one column at a time.

For instance if you want to search on keywords you would have to do
something like this

Select * from TableName where contains(KeywordColu
mn,'Test')

Regarding your second part of the question, you get best performance by
limiting your results set to a few hindered rows. Consult this kb article
for more information on how to do this.

http://support.microsoft.com//defau...kb;EN-US;240833

"Jarmo Lautamäki" <jarmo@kuvaporssi.fi> wrote in message
news:OyMIH0cPFHA.2132@TK2MSFTNGP14.phx.gbl...
> Hi!
> We have Sql Server 2000 in our server (NT 4). Server have 1GB RAM and
> 1,4GHz processor. Our database have now about
> +350.000 rows with information of images. Table have lot of columns
> including information about image name, keywords, location, price, color
> mode etc. So our database don´t include the images itself, just a path to
> the location of every image. Keywords -field have data for example like
> this:
> cat,animal,pet,home,
child with pet,child. Now our search use Full-Text
> Search which sounded like good idea in the beginning but now it have had
> problems that really reduce our search engine´s performance. Also search
> results are not exact enough. Some of our images have also photographer´s
> name in keywords -column and if photographer´s name is, for example, Peter
> Moss, his pictures appears in web-page when customer want to search "moss"
> (nature-like) -pictures.
> Another problem is that Full-Text Search started to be very slow when
> query
> result contains thousands of rows. When search term gives maximum 3000
> rows, search is fast but larger searches take from 6 to 20 seconds to
> finish which is not good. I have noticed also that first search is always
> very slow, but next ones are faster. It seems that engine is just
> "starting" when first query started to run. But always when search result
> start to go over about 3000 records, customer must wait tens of seconds.
> That is too much.
> Is there better and faster way to handle the queries? Is it better to
> rebuild the database somehow and use another method to search than Full-
> Text Search? I don´t know how to handle the database other way when every
> image have about 10 to even 50 different keywords to search.
> We have made web interface and search code with Coldfusion. Coldfusion
> Server then take care of sending all queries to Sql Server.
> I hope that somebody have some idea how to speed up our picture search. I
> tried the OPTION(FAST n) -method, but it made the searh even slower.
> Because it seems that Coldfusion don´t return any results before the whole
> query is over.
>

Sponsored Links