This query takes 1-2 minutes to execute and I can't figure out what the problem is. I noticed that the Est Rows vary's from the Actual Rows by millions in some cases. I tried updating statistics on all of the involved tables but the row counts seem to be off because of the number of executions being much higher than the estimates. How can I get the estimates to be more accurate?? Is that even what is causing the slow down?
EDIT: I made some changes to the query from the comments below. Attached is the new plan with the query text run from inside of it. I think I was able to push the filter down a bit further and I removed the datetime2 to datetime conversion manually as a test (didn't seem to help much). Here it is: slow query - new
EDIT 2: Thanks for the comments, they're really helping me along. I think LINQ isn't that great at pushing filters down past a .Union statement. So I moved the .Union as far down in the queries and switched some things around to eventually eliminate the .Union (though SQL still did a union anyways). What took over a minute to run before now only takes < 10 seconds! The new plan is here. I'd like to have this run in < 5 seconds but 10 is a lot better.
Now it looks like the Distinct Sort is taking most of the time. I'm using that to get the last record by date. Instead of sorting descending and grabbing the first, I tried using Where Date = MAX(Date). That gave a very different plan with the same results, but is actually a second or two slower...so maybe that's not such a big issue that the plan makes it out to be?
By ijabit 33 asked Apr 01, 2015 at 05:54 PM
The problematic area of the execution plan is:
There are multiple issues here. From a runtime performance point of view, one of the main problems is the late application of the Filter:
The filtering expression refers to a column on the Provider Payment History. The optimizer is unable to push the Filter down past the Outer Join / Top / Semi Join combination, but you may be able to rework the original query specification to achieve this (while retaining correct results). If you are able to do that, it should improve performance markedly. The optimizer is limited/conservative about pushing Filters below an outer apply with top in particular.
Aside from the outer apply / top pattern not being particularly optimizer-friendly, There are other issues as well. Certainly, estimates can have a profound effect on the plan shape and operators chosen by the optimizer.
In particular, the Merge Interval + Seek pattern here is caused by a mismatch in parameter types. Specifically, the following parameters appear to be incorrectly typed, compared with the columns they are applied to:
All these are typed
There are other things as well, but relocating the filter and addressing the incorrect types are probably the main things. Almost certainly, the execution plan and performance will be very different after making those changes.
Don't be misled by the high estimated cost on the Hash Match Flow Distinct:
The 59.3% shown is the optimizer's internal estimate, used only at compilation time to make internal choices about plan operators and shape. These cost numbers never show runtime performance. The Flow Distinct in this plan simply passes on the first row it encounters, though it is executed 1,736 times overall. Even so, the actual impact of this operator on overall performance is negligible.
The poor performance of the query is now almost all due to the 5-million-odd seeks into the Provider Payments table, and to a slightly lesser extent, the Lazy Spool. Both shown below:
Check you have the right indexing on Provider Payments. It seems you need an index keyed on
The remaining issues in that area of the plan are:
Investigating these would require detailed analysis and full access to the database. I can say that the unfortunate choices are almost certainly due to the way the query is currently expressed in SQL, but there are no obvious quick fixes like a change in indexing. Sorry about that, but there are limits here :)
By SQLkiwi ♦ 6.6k answered Apr 03, 2015 at 10:14 AM