473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Fastest way to handle the search

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
Jul 23 '05 #1
2 2448
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.public.sqlserver.fulltext. 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)
Jul 23 '05 #2
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
Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Simon | last post by:
Hi, If I have a string, (variable len), and I am looking for the first position of one char in array starting from position 'x' For example, // the 'haystack' $string = "PHP is great,...
6
by: Jonathan | last post by:
I am hoping that someone more experienced than myself can point me towards what might be the fastest data lookup method to use for storing ip addresses. My situation is that I will need to maintain...
9
by: danny van elsen | last post by:
hello all, I have an application in which I build a list<node>, with potentially thousands of nodes. each node has an "index", and all nodes are ordered by this index. this index reflects a...
60
by: Julie | last post by:
What is the *fastest* way in .NET to search large on-disk text files (100+ MB) for a given string. The files are unindexed and unsorted, and for the purposes of my immediate requirements, can't...
2
by: The One We Call 'Dave' | last post by:
I have a list of DateTime objects stored in a collection: SortedList<DateTime,Object> MyDates=new SortedList<DateTime,Object>(); The dates, which can be accessed via MyDates.Keys, are stored in...
4
by: Fulvio | last post by:
*********************** Your mail has been scanned by InterScan MSS. *********************** Hello, I'm poor in knoweledge of python, sorry. What's the fastest result between : if item...
3
by: Harry Haller | last post by:
What is the fastest way to search a client-side database? I have about 60-65 kb of data downloaded to the client which is present in 3 dynamically created list boxes. The boxes are filled from 3...
1
by: Harry Haller | last post by:
What is the fastest way to search a client-side database? I have about 60-65 kb of data downloaded to the client which is present in 3 dynamically created list boxes. The boxes are filled from 3...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.