asked
Is there any alternative for like operators - for searching data
Hi Everyone, I need small help here, i am fetching data depends upon the conditions Here i am using like operators,where client may use search with any word thus i am below logic but here retrieval time is too high (its taking 4mins) to get the data which is not affordable I did all indexing to table still can't able to optimize the query declare @title varchar(20) declare @word varchar(20) declare @words varchar(20) declare @wordcom varchar(20) declare @wordscom varchar(20) set @title = 'coll' set @title = LTRIM(rtrim(@title)) set @word = @title set @words = @title + 's' set @wordcom = @title + '.com' set @wordscom = @title + 's.com' select * from authors where address like @word or address like '% '+@word or address like @word+' %' or address like '%'+@word+'%' or address like '% '+@word+' %' or address like @words or address like '% '+@words or address like @words+' %' or address like '%'+@words+'%' or address like '% '+@words+' %' or address like @wordcom or address like '% '+@wordcom or address like @wordcom+' %' or address like '%'+@wordcom+'%' or address like '% '+@wordcom+' %' or address like @wordscom or address like '% '+@wordscom or address like @wordscom+' %' or address like '% '+@wordscom+' %' Thanks Sai
msairam02@live.com
Just curious, 1) How many rows in the table 2) Can you post an execution plan? 3) Can you post the DDL of the table? Also, I'm a little confused on why you would be searching for the combinations with ''s' & '.com' when the search value will be found regardless? In other words, search for '%john%' will also find the 'john's' & '
john.com' by sheer nature of the way that you have your query written.
You are doing a lot of unnecessary work in that query. ...like '%' + @word + '%'...already covers "starts with" and "ends with" criteria, so you' don't need a lot of those conditions.
You also don't need to add criteria for possible spaces...it will be covered by '%' + @word + '%'.
The type of search you're providing is one of the most difficult to accomplish. What you're seeing is that using the LIKE '%anything' is causing scans and can only ever cause scans. The first way to address this is to ask, do you really need to search on anything, or can you more realistically search for LIKE 'anything%' which won't cause scans. Assuming you have to search for anything your alternative is to set up a f[ull text index and use the full text search syntax][1]. See if that helps. If you're using SQL Server 2012, you might consider taking a look at the [columnstore index][2]. I'm not sure it's applicable in this case, but I'd at least consider it as a test. [1]:
http://msdn.microsoft.com/en-us/library/ms142571.aspx [2]:
http://msdn.microsoft.com/en-us/library/gg492153.aspx
I'd support the use of a Full Text Index to see how it improves searches for '%stuff%'.
I would support also the full text index. The **columnstore index** s great in data warehousing scenarios, where data do not change frequently as the columnstore index turns whole partition upon which it is built read only. Than if there are some needs for modifications/inserts/deletes, it is necessary to drop the index prior the modification and rebuild it after, which is very costly operation. Anyway in DW scenarios for historical not changing partitios it can give a good performance.
Grant and Martin both have great answers and I echo everything they said. To add on a bit, depending on how this is called you may consider not automatically adding the % wildcard and let the user signal what wildcards they need. That way if they truly need the wildcards, especially the one in front that makes the query non-SARGEable, they can have it, but they won't suffer the performance cost if they don't. If this is being called through SSMS and your users are SQL literate they can add the wildcards themselves. In a Gui it could be checkboxes. Also, you can consider some of the CLR options that allow RegEx. I have not personally done performance testing, but I have been told they can be effecient and they definitely provide much more flexibility to get exactly the results you want.
Again, depending on the size of the data, you could consider having a words table. When you store a row in your data table, you also split out the words from the address and record the usage in your words table, storing the author id against the words. I have used this technique very effectively for a similar problem.
15 People are following this question.
