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.
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.