Jeff Moden (1/18/2016)

---

ChrisM@Work (1/18/2016)

---

Jeff Moden (1/18/2016)

---

ChrisM@Work (1/18/2016)

---

Jeff Moden (1/18/2016)[hrCan we agree on "SARGable in one direction only" as it's often sufficient to permit tweaking a decent plan?

With the battles I'm currently facing at work on this identical subject on some rather large tables and heavily used similar code, my most politically correct answer would have to be "Oh hell no!". 😀

Now, how the heck did that happen? it looks like you posted that reply when it was me! Things have really been getting wonky on this site lately.

Ignoring the ssc weirdness for the moment, some of the best tuning gains I've had at this site recently have been from knowing that LIKE can be SARGable and tweaking the query to fit the data. How about 5 hours down to 20 seconds (with the help of a loop hint)? It's similar to an implicit conversion screwing up a join. If you can put an explicit conversion on the key column from the smaller table instead of relying on an implicit conversion of the matching column in the bigger table then you've won the battle.

Change the column types and you've won the war 🙂

I'd love to see the code you did for that incredible gain because it's driving me nuts at work. I've already instituted the "narrow index" trick and have gotten times down from nearly a minute per lookup to sub-second times so I'm not sure that anything else obvious can be done but I wouldn't mind seeing your code to try to get a fresh idea.

Ah... and you might ask why I'm moaning about sub-second times. One part is that, even with the narrow index, it's doing a scan on nearly 3 million rows 3 times per request and there are 3 different lookups per request and there are 40,000 requests per 8 hour period. It turns out to be about 8.3 hours of CPU time per 8 hours and total memory IO measured in the tens of terabytes. The 3 lookups per run are my top 3 worst statements on the entire system for CPU and logical reads outstripping even some of the very long winded and inefficient night jobs that I'm also working on fixing (except for these 3 lookups, I've finally fixed all of the GUI related code that was causing timeouts and screen delays not to mention beating the hell out of the system).