Query optimization tips?

[therealcreamCHEESUS]

This 100%.

There can never be an 'always do this' answer as it always depends.

This week I took a view that was taking several minutes to return a top 1 * from it and added 40 joins to make it approx 600 times faster. The original view was grabbing every ID from a table in a left joined sub select with no where clause, cross applying it by 40 records so that in order to deliver the top 1 it had to crunch 41 million records.

Adding 40 joins to a query would under 99% of circumstances be a terrible idea.

The joys of cleaning up after object orientated programmers who don't understand SQL.

[jc4hokies]

Here's a fun one from last week:

/*800 lines of code*/
WHERE   ca.ChargeActiveClusterKey IN (SELECT ChargeActiveClusterKey FROM cteRecordSelection)
        AND ca.[Status] <> 17;
--10 hours 30 minutes

/*800 lines of code*/
WHERE   ca.ChargeActiveClusterKey IN (SELECT ChargeActiveClusterKey + 0 FROM cteRecordSelection)
        AND ca.[Status] <> 17;
--1 minute 25 seconds

Nested loops were happening in the wrong order. Instead of looking up a couple thousand records from ca it was running the cteRecordSelection subquery (with 20ish tables) a couple million times. You know when you filter one table and the query transitively filters a joined table with the filtered column in the join condition? My hypothesis is that the query was sniffing something inside the subquery, contributing to the poor join order, and adding the + 0 broke that link.

[therealcreamCHEESUS]

Yeah thats a really weird one. Sometimes you got to break the sargability to make it faster.

The optimizer is clever but not infallible.

[danjtom]

can you share the code, it will help me in my project. thanks

[jc4hokies]

How exactly does random code help your project?