Substring searching in SQL
-
In SQLServer 2000 I have a table with 25000 rows 7 columns. One of the columns is a nvarchar of 60. I need to do a substring search on that column and the performance is very poor, 3-10 seconds. The search is done by a stored procedure, for example a search for "John Doe" is implemented in SQL by a simple "LIKE %John% AND LIKE %Doe%". I need the functionality of this kind of query, but it's just too inefficient and slow. Any suggestions or references on improving text searching using SQL would be most appreciated. David
-
In SQLServer 2000 I have a table with 25000 rows 7 columns. One of the columns is a nvarchar of 60. I need to do a substring search on that column and the performance is very poor, 3-10 seconds. The search is done by a stored procedure, for example a search for "John Doe" is implemented in SQL by a simple "LIKE %John% AND LIKE %Doe%". I need the functionality of this kind of query, but it's just too inefficient and slow. Any suggestions or references on improving text searching using SQL would be most appreciated. David
You might want to try setting up Full Text Search. Other than that, if you're searching for "John Doe", wouldn't you want the like to look like this: LIKE '%John Doe%' Andy Gaskell, MCSD MCDBA
-
In SQLServer 2000 I have a table with 25000 rows 7 columns. One of the columns is a nvarchar of 60. I need to do a substring search on that column and the performance is very poor, 3-10 seconds. The search is done by a stored procedure, for example a search for "John Doe" is implemented in SQL by a simple "LIKE %John% AND LIKE %Doe%". I need the functionality of this kind of query, but it's just too inefficient and slow. Any suggestions or references on improving text searching using SQL would be most appreciated. David
Is there an index on the column? Also, what about just using LIKE %JOHN DOE% instead of LIKE %John% AND LIKE %Doe%?
-
You might want to try setting up Full Text Search. Other than that, if you're searching for "John Doe", wouldn't you want the like to look like this: LIKE '%John Doe%' Andy Gaskell, MCSD MCDBA