| Author |
Topic |
|
AskSQLTeam
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. |
|
|
RobWafle
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. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-09 : 09:35:00
|
quote:
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! |
|
|
|
AndrewGail
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 SELECT UIN FROM view_Search WHERE Keywords LIKE @like_textwould 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) |
|
|
|
graz
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 ItemIDOrder by 2 DESC===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
AndrewGail
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 |
|
|
|
wengang
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). |
|
|
|
wengang
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. |
|
|
|
|
Article: Searching on SQLTeam.com