Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ferox
Starting Member
18 Posts |
 Posted - 2005-04-12 : 02:53:51
|
| Hi Guys, I'm new here and was pointed over by a friend. I've got a few questions some of you guys might be able to help me with. 1. Im working on a ASP.Net project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are text fields that hold large paragraphs of text. The user interface has a general search option so that they can enter a number of key words in one textbox and the database will return a count of the records found containing the keywords.At the moment I split the input and then build a query based on their input. For instance if they enter "hello world" the input is split into two strings "hello" and "world". I then build the query in a loop and get a query like so:Select Count(ID) as myCount FROM myTable WHERE (colOne like '%hello%' AND colOne like '%world%') OR (colTwo like '%hello%' AND colTwo like '%world%') OR (colThree like '%hello%' AND colThree like '%world%')I also tried a Stored Proc where I passed in the whole string, split it in the proc and then pulled out the matched into a temp table. Unfortunately both these methods runs EXTREMELY slow - too slow for a web application and just seems bad for this many records (used to work ok when I was working on 1000 or so records, this is the biggest set I have ever had to deal with!) Is there a more efficient way I should be doing these types of searching?2. Is it also possible to search a text column and look for exact matches?For instance I have 2 records with their textfield containing:Rec 1: the news for today is blah blah.Rec 1: this is a new itemIf I currently search for 'new' (select colID from myTable where colOne like '%new%') I will get both these records, but I'd really only like to pull out the second record.A poster over at ASP.net helped me out and said I should try Full Text searching on SQL. Is this the way I should be going?Any help would be greatly appreciated! :) |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-04-12 : 03:40:43
|
Yes, i'd go for Full-Text searchYour query would then look likeSELECT colID FROM myTable WHERE CONTAINS(colOne,'new') To search for verbs, nouns & plurals it would look likeWHERE CONTAINS(colOne, ' FORMSOF (INFLECTIONAL, new) ' )AndyBeauty is in the eyes of the beerholder  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-12 : 03:42:45
|
| Can you store all the words in mYtable colOne, colTwo and colThree in a "keywords" table, and just join to that? Should be pretty much instant results. Plus you could allow substitution from a second "KeywordAlternatives" table - e.g. plurals and root words etc. as a later enhancement.Alternatively you could install Full Text Search (basically Index Server) and integrate that into your querries using the "Contains" SQL syntax.Kristen |
|
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-04-12 : 09:19:51
|
| Thanks guys! I tried out the Full-Text Indexing and now use the 'CONTAINS(colOne,'new')' syntax. This is working amazingly faster than my previous version. Thank you both for you input and assistance! |
|
|
|
|
|
|
|
|
Large Text Search