Suggestions for an expensive seek
Code comes from a front end web gui. It recently started taking 2-3 minutes for clients to load this 'view' in their browsers.
Table is 1.6 billion rows and the query only has one filter so clients can view all actions taken on a particular document.
Any ideas are appreciated.
Dan and Ed,
- No help with OPTION RECOMPILE or OPTIMIZE FOR UNKNOWN, but I can't change the code outside of experimenting anyway.
- I'll try testing the short circuit out. Is that a case of predicate push down? Even if I can't make that change it would be an interesting find.
- No partitioning
- Yeah, these Audit tables are ridiculous.
Tried using the where filter as a join predicate instead and no change.
I'm sure there will be some clever answers to this one!
Is the table horizontally partitioned in any way?
Have you tried to short circuit the inner first inner join by using
"ON AuditObject.ArtifactID = @ArtifactID" to disconnect it, and perhaps reduce the nested loop iterations?
I am definitely interested in how other people approach this one!
Hi Paul,
This was mostly remedied with an index on artifact id and on the date column descending. There's a new missing index request, however three of the columns are large text, and this is a very active table. Inserts slowed down by 3-4x when I either modified the new index to accommodate all of the includes, or when I added the newly requested index.
Anyway, attached is the actual, run in SQL Sentry, with the trace flag as you requested. If you see anything else that might be improved, please let me know.
Thanks