Fastest way to handle the search

[Zambo via SQLMonster.com]

Hi!
We have Sql Server 2000 in our server (NT 4). 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.
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.

--
Message posted via http://www.sqlmonster.com

[Hugo Kornelis]

On Sat, 09 Apr 2005 08:30:43 GMT, Zambo via SQLMonster.com wrote:

(snip)

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.
Hi Zamobo,

That's easy to fix (easy from the designer's POV - it'll be a lot of
work for the data-entry person): add a column for the photographer's
name (set it to allow NULL if it's unknown for some of the images) and
move the name from the keywords column to the new column. Then change
the web form to accept seperate keywords and photographer name for the
search.

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.
You might try if adding OPTION (FAST n) at the end of your query (where
n is replaced by some number) helps. This is an optimizer hint,
basically telling the optimzer to find a plan where the firs n rows are
returned as soon as possible, even if that means that the total query
execution might take longer. With appropriate handling at the front end,
you could use it to bring up the first page of results quickly, while
still gathering remaining results in the background. The slower
execution of the total query won't be a problem if the user is checking
the first page of results.

I must add that I'm not familiar with Full-Text Search, so I don't know
if OPTION (FAST n) really works in combination with FTS.

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?

Based on your description, I think that FTS is the best way to handle
this. But you might consider posting your question in
microsoft.publi c.sqlserver.ful ltext. As the name of that group implies,
it's dedicated to Full-Text Search issues.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Zambo via SQLMonster.com]

Thanks for the answer. Unfortunately it seems that OPTION(FAST n) is not
the answer in my case; query was much more slower when I asked the search
to return first 20 records faster. We have 20 pictures/page in our query
result. It seems that Coldfusion don?t return any records before the whole
query is over. I read from somewhere that FTS goes slow when there are
thousands of rows returned. But there must be a way to speed it up.

--
Message posted via http://www.sqlmonster.com