I'm working with a vendor's system that has a very severe performance problem. I'm looking at it because despite the fact that I told the vendor there was a problem with a particular query back in OCTOBER and that the problem was going to degrade linearly with the data size they didn't (1) fix the query or (2) provide us with a data archive system, and now it's an emergency.
Sigh.
The reason the query is performing badly is that it's a query of a view. Not a stored procedure containing a query of a view even, but their application directly does a select statement.
Problem 1: All data access is to be done through stored procedures. All of it. If you are writing an application and don't follow this rule, please just kill yourself now and save me the trouble of having to track you down.
It's a query of two views, using the old join syntax...
Problem 2: It's an OLD join syntax. There is a NEW join syntax. It doesn't suck like the OLD one did. If you use a comman in your WHERE clause, please just go back to writing user interfaces.
One of the views is a 5 part UNION ALL, which contains in one part another view, which is itself a big pile of UNION ALL's.
Problem 3: Excessive use of UNION ALL is prominent in poorly constructed databases. UNION ALL is generally called for when you're generalizing data, making one thing look like something it's related to. If they're close enough to be related together in this way, USUALLY they're the same object and you're thinking about the problem wrong. "We need to use UNION ALL so we get a transaction total across our 5 order tables". Why do you have 5 order tables? Poor design. One order table. 5 order detail tables that differentiate the transaction types.
With all these problems combined, what should have taken about 5 minutes to figure out took more like 4 hours to diagnose, fix, verify, and implement. The problem turned out to be that one of the deepest views was not filtering properly, instead of filtering down at the view level, the SQL Server query optimizer decided to join the 250K rows to a few other tables and then filter it down to 0 rows.
When you write complicated queries, you give the optimizer a LOT of chances to screw up. And it will take every chance it gets to screw up and pick the least efficient strategy possible far more often than you'd like.
"Oh, but if you had up to date indexes and statistics that wouldn't happen"
Uh huh. Right. Pull the other one it's got bells on. Indexes rebuilt nightly, because I have the time to do it.
The optimizer is usually pretty good. "Usually" usually doesn't cut it.
Write small queries, store temporary results, combine the temporary results. "Oh but that's space inefficient". Uh huh. Look at your query plan. See that "Spooling" step? Guess what that does! It creates a temporary table for intermediate results. It's the same work either way. One way you control how the work gets done, and you understand your data and business rules. The other way SQL Server controls how the work gets done, and it doesn't understand your data or business rules. One way you can highlight part of your query, run it, and performance tune it one piece at a time. The other way, you get an atomic operation, maybe not done in the order you'd like, and the order is somewhat arbitrary. The optimizer does things in a specific order based on data size and index selectivity, but it's not always in the RIGHT order.