July 14, 2015 at 8:38 am
Hi I'm hoping someone has a tip for me. I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.
The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.
Any tips on how to rewrite or speed this query up would be greatly appreciated.
select * from #TempLogins a where exists
(select 1 from #TempSearch t1 where
a.detail like '%' + t1.ResultIDSearch + '%'
or
a.detail like '%' + t1.ResultSetIDSearch + '%')
July 14, 2015 at 9:26 am
Improvements are unlikely to come from the query without a change in the inherent structure of what you're trying to do, unless perhaps you can implement FULL TEXT SEARCH. There's no way that a normal index is going to help because the values being searched for aren't at the begiinning of the string. Also, if the values being searched are all URLs, FULL TEXT SEARCH might have trouble breaking up the data into "words". I don't know enough about how that process works, or if it's configurable to allow for choosing how to break up data into "words". Hopefully, someone with more knowledge on FULL TEXT SEARCH can chime in on this. The problem with just throwing hardware at the problem is that it might not help all that much and could still cost a lot.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply