Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Searching on

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2000-12-31 : 12:13:29
Many web sites like this one give each article their own web page. My site stores each article as a field in the database. This article shows you how I wrote a search function that ranks the results.

Article Link.

Starting Member

38 Posts

Posted - 2002-02-09 : 03:43:26
Ok, so I've read the thread. And that ryhmes a little.

My question is why does your hosting provider not support full-text indexes? Thats basically crazy. Your code performs what seems to be a linear search!! I think they'd rather have you consume less CPU and search an index.. Of course, someone's probably made that decision and won't budge. Better keep your site backed up in case they go belly up!

Maybe you should try to be polite and give them a call and explain just how your site is taking LOTS of extra CPU resources during searches...

I had a blonde, a brunette and a red beer tonight, so my comments may be a little more colorful than normal.

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2002-02-09 : 09:35:00
My question is why does your hosting provider not support full-text indexes?

My guess is that they don't want the headache of maintaining the full-text indexes, and can't or don't want to trust the users to maintain it themselves. Keeping full-text indexes up-to-date can also be a CPU hog, especially if they run every hour or so. If you're on a shared SQL Server, and 50 people or more like to refresh their full-text indexes every hour on the hour, ohhhhhhhhhhhhhhhhh boy!

From the host's perspective, the best way to avoid a problem like that is to restrict full-text indexing. And a majority of people would never use it anyway. Besides, it gives them the opportunity to charge more for an additional service for those that do want it!

Go to Top of Page

Starting Member

3 Posts

Posted - 2004-02-27 : 06:20:52
Does anybody have an idea of how to implement a weighting system into this search?

I've had a think and come up with two ideas, one potentially more efficient than the other, I say potentially cos I've yet to figure out exactly how to get it to work (I'd class my SQL skills as intermediate to advanced).

Method one (inefficient)[u]
Repeat the INSERT/SELECT to be weighted a number of times (to increase the ranking count). So if I wanted my keywords to be ranked 3x higher than my article content then this:
INSERT #SearchResults
FROM view_Search
WHERE Keywords LIKE @like_text

would be repeated 3 times, and my INSERT/SELECT on the 'Blurb' column would only be run once.

Obviously this is pretty inefficient.

[u]Method two (more efficient)

For each insert into #SearchResults insert the ItemID (In my case UIN) and a weight value, so as per above, a 'keyword' hit would have a weight of 3 and a 'blurb' hit would only have a weight of one.

What I can't get working here is a SUM of the weights for each unique ItemID (UIN) in the final SELECT...

Could anybody help?
(I'm also experimenting with ways of returning a % accuracy result based on the rating and a figure I just sucked out my thumb)
Go to Top of Page

Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-02-27 : 18:45:32
I would choose the second way. That's what the site does currently. I actually have one weight for the title, one for the description and a third if it's found in the body of the article.

SELECT ItemID, sum(weight)
from ...
Group by ItemID
Order by 2 DESC

Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Starting Member

3 Posts

Posted - 2004-03-01 : 06:13:13
Thanks Graz,

Looks like the solution was a hell of a lot simpler than I thought (you don't wanna see the silly complicated queries I'd come up with).

I've successfully implemented a rating system, including a rating overload feature for an exact title match, this has the benefit of boosting an exact match over other results which may have an equal rating.

I've also included an accuracy column which returns a percentage accuracy value based on how many fields were matched. With an exact title match (rating overload) the accuracy will be a maximum of 200%, which gives me a quick and easy method of checking for an exact match in my ASP code.

Thanks again for an excellent article, its been a great help.

- Andrew
Go to Top of Page

Starting Member

4 Posts

Posted - 2005-08-10 : 02:42:48
I also ran this solution on my site and met the same problem as naubin. it seems to go on and on, then times out. i search for each term over one NVARCHAR(100) field and one NTEXT field. I have roughly 8000 records in my database and searching even a single word in this fashion times out.

I also switched the LIKE comparisons to PATINDEX, but also times out.

Another note about this code, it is suitable for an Any keywords search (OR) but would not work for an ALL keywords search (AND).
Go to Top of Page

Starting Member

4 Posts

Posted - 2005-08-10 : 03:21:20
i ran this code on query analyzer too. It ran for about 100 seconds and then gave a general network error. connection broken.

Go to Top of Page

- Advertisement -