SQL Server Performance Forum – Threads Archive
Searching Large "text" fields
I would like some guidance on how to attack a problem I inherited please I have a table containing row information about items. As part of the items in the table, there is a field called "notes" that sometimes contains over 10,000 characters in it. The field is currently set as a "text" field in SQL Server 2000. I am assuming it was setup that way because of the 8000 char limitation on a row. At any rate, there are now 125,000 records in that table, soon to be more, and the user would like to search on the notes field. As you can imagine, the query takes a while. They want to do a query like "Select * from table where (notes like ‘%dog%’)". It takes a while to run, I assume because it has to search through all that text in the "notes" field. Does anyone have any suggestions as to how this can be changed to improve performance? I have to assume that they will continue to need notes that are longer than 8000 characters. Thanks very much. MikeHave you looked at full-text indexing? If not, you should check it out.
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Does anybody have a good reference site for use of TSQL with full text indexing? Just curious of any favorites people have out there. Thanks.
This will get you started. Pay attention to the links at the bottom. http://www.mssqlcity.com/Articles/Adm/SetupFTS/SetupFTS.htm MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
http://spaces.msn.com/members/jtkane/ —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
]]>