SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Searching on SQLTeam.com		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 12/31/2000 :  12:13:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts
Posted - 01/18/2001 :  16:22:46  Show Profile  Reply with Quote
Graz' Search Function for the SQLTEAM website

I reviewed your code for this website's search capability and wondered if you'd worked with the "full-text indexing" capability of SQL 7 yet? It will give you the weighted value search results you want and can really streamline (speed up?) the search process itself.

Full-text indexing is not installed by default so you may have to dig out your SQL 7 CD to get it going. Once you install it you simply point it to the table.column(s) you want indexed and set the rebuild frequency and that's basically it! It installs the Microsoft Search Service (NT) and builds the full-text index OUTSIDE of SQL which lives on the harddrive of your server... so it is not really a SQL index at all. This is great as far as SQL performance goes because it doesn't slow down your database at all. It means that you have to rebuild (or "re-populate" as Microsoft calls it) your full-text indexes asynchronously, usually as a scheduled task but also configurable as an "on-demand" function.

If the column(s) you are indexing are very large or are growing rapidly you might want to use the "incremental repopulation" option. This option requires that the table on which your index is based have a TIMESTAMP column in it. If it doesn't already it is usually worthwhile to add one since this option can save you a lot of processing time.

Then you can modify your existing search stored procedure to use the CONTAINS and FREETEXT predicates. These T-SQL extensions are the ones that do all the work in finding column values that match your user's search criteria.

Microsoft has a pretty concise white paper that goes into greater detail on exactly how to set all this up. It gets very granular with all the CONTAINS and FREETEXT syntax as well. You can download it from the Microsoft website. Sorry I don't have a link for you here but if you do a search (ha!) on the following title you should find it: Textual Searches on Database Data Using Microsoft SQL Server 7.0

Thanks for creating a
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4128 Posts
Posted - 01/18/2001 :  17:02:16  Show Profile  Visit graz's Homepage  Reply with Quote
Graz says . . .

My first choice for searching was full-text indexing. Unfortunately my host doesn't offer that on shared servers. Can't say as I blame them either. I updated the article to mention so it should be clear to future readers.

Thanks
Go to Top of Page

naubin
Starting Member

Canada
1 Posts
Posted - 10/11/2001 :  16:01:14  Show Profile  Reply with Quote
I've read your article "Searching on SQLTeam.com". I've tried to
implement this type of search on my web site. Unfortunately I've been
having a few problems. I've created the demonstrated stored procedure
that was outlined in your article. As I enter the search words in the
text box and click submit the stored procedure loops through the records
until it times out. When I look at the temp table it has a very large
number of IDs that goes on indefinitely. It's almost as if the stored
procedure doesn't pickup the words in the text box. And goes on an infinite loop. I'm using the same
asp code from the article to pass the variables onto the stored
procedure. I'm also using a view to select the records from.


Edited by - naubin on 10/11/2001 16:03:46
Go to Top of Page

robvolk
Most Valuable Yak

USA
15557 Posts
Posted - 10/11/2001 :  17:06:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
Have you seen this recent article?

http://www.sqlteam.com/item.asp?ItemID=5857

It lists an alternate method that might work better for you.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4128 Posts
Posted - 10/11/2001 :  17:16:50  Show Profile  Visit graz's Homepage  Reply with Quote
quote:

I've read your article "Searching on SQLTeam.com". I've tried to
implement this type of search on my web site. Unfortunately I've been
having a few problems. I've created the demonstrated stored procedure
that was outlined in your article. As I enter the search words in the
text box and click submit the stored procedure loops through the records
until it times out. When I look at the temp table it has a very large
number of IDs that goes on indefinitely. It's almost as if the stored
procedure doesn't pickup the words in the text box. And goes on an infinite loop. I'm using the same
asp code from the article to pass the variables onto the stored
procedure. I'm also using a view to select the records from.


Edited by - naubin on 10/11/2001 16:03:46

Can you run the stored procedure without the ASP page? Does that work?

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

RobWafle
Starting Member

38 Posts
Posted - 02/09/2002 :  03:43:26  Show Profile  Visit RobWafle's Homepage  Send RobWafle an AOL message  Send RobWafle an ICQ Message  Send RobWafle a Yahoo! Message  Reply with Quote
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

robvolk
Most Valuable Yak

USA
15557 Posts
Posted - 02/09/2002 :  09:35:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
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!
Go to Top of Page

AndrewGail
Starting Member

United Kingdom
3 Posts
Posted - 02/27/2004 :  06:20:52  Show Profile  Reply with Quote
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_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

graz
Chief SQLTeam Crack Dealer

USA
4128 Posts
Posted - 02/27/2004 :  18:45:32  Show Profile  Visit graz's Homepage  Reply with Quote
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

AndrewGail
Starting Member

United Kingdom
3 Posts
Posted - 03/01/2004 :  06:13:13  Show Profile  Reply with Quote
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

wengang
Starting Member

China
4 Posts
Posted - 08/10/2005 :  02:42:48  Show Profile  Reply with Quote
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

wengang
Starting Member

China
4 Posts
Posted - 08/10/2005 :  03:21:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000