asked
how to improve performance of a LIKE clause?
I have a query that's taking a reasonably long time - analysis of the execution plan points the finger at a LIKE clause. Due to the nature of how I'm dealing with hierarchies and parent/child relationships in a table, there's no other easy way to achieve this. CREATE TABLE #options ( [Optionid] INT primary KEY clustered, [Path] VARCHAR(100) ) Select (t4.OptionId), (select Count(distinct t1.VacancyId) from #results1 t1 inner join vacancy_Option t2 on t1.VacancyId = t2.vacancyid inner join #options t3 on t2.optionid = t3.optionid **where t5.Path like '%/'+ cast(t3.OptioniD as nvarchar(50))+'/%'** and t3.optionid <> t4.OptionId ) as NoVacanciesParents, (select Count(distinct t1.VacancyId) from #results1 t1 inner join vacancy_Option t2 on t1.VacancyId = t2.vacancyid inner join #options t3 on t2.optionid = t3.optionid where t3.[Path] like '%/'+ cast(t4.OptioniD as nvarchar(50))+'/%' ) as NoVacanciesIncChildren from OptionDisplay t4 inner join [Option] t5 on t4.OptionId = t5.OptionId Have worked at getting it this far for a very long time - and reduced the time taken down by a huge amount. Main thing left is to see whether there's a way to get the LIKE clause working better - perhaps creating an index on the #options temp table? Note - #options does not really get larger than 400 entries however, whilst #results can get up to a couple of thousand records.
Hi,
Is DISTINCT required for your query? If you you avoid DISTINCT you will get performance.
by
Pratheeskumar
Is DISTINCT required for your query? If you you avoid DISTINCT you will get performance.
by
Pratheeskumar
@prathees - good advice, if it can be avoided then DISTINCT is a good thing to leave out of SQL you are trying to get to perform better, however I suspect that the %work for the DISTINCT is a lot less than the %work for the LIKE ...
Because you are using LIKE '%... - i.e. wild-carding at the start of the like clause, there is no chance that indexing will help, because that construct isn't SARGable (Search ARGument-able) - i.e. cannot be used to seek an index. Note that in this case, there is no chance of indexing helping because even a non-clustered index would contain the same data as the clustered index. If there were more columns in the main table, then a non-clustered index may help because there would be a narrower data set to scan, and more rows could be scanned for every single page read. You haven't specified what server version you are on - but you might get some mileage out of using the `hierarchyid` data type that ships with SQL Server 2008.
Just to add to the options, if it was more data in a permanent table then a Full Text index might be an option too ... :)
An index will still help, won't it? Scanning a Non Clustered index seems faster than scanning a table, right?
@Magnus Ahlkvist - I don't think so, in this case - because the NC index would include all columns of the clustered index anyway (since there are only two columns)...
@Matt Whitfield - You're right. I wasn't paying attention to the details of the question.
@Magnus Ahlkvist - I think, more to the point, I wasn't specific enough in my answer. I'll edit it now.
As Matt mentioned, the LIKE '%somevalue%' forces an index- or clustered index scan, but there are solutions to that... read more at [
http://www.sommarskog.se/yourownindex.html][1]. You will not find the answer there but i will give you a hint on how to find it. [1]:
http://www.sommarskog.se/yourownindex.html
