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
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]. See if that helps. If you're using SQL Server 2012, you might consider taking a look at the [columnstore index]. I'm not sure it's applicable in this case, but I'd at least consider it as a test. :
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.