Please let me know how to optimize this query .

Vinu 2017-05-25 10:37:32

Rob Farley 2017-05-26 06:37:42
You could just NOT use the OVER clause. The two biggest costs you have are the sorts (which are needed because you want to order by the count – the left-most Sort – of events by EventLabel – the right-most Sort) and the spooling. The spooling is needed because you want to return the overall count in the same query. But you could do it in a different query. Your index seems very well optimised (although I'm not a fan of the fact that you're using a hint), so you should be able to re-query it pretty easily. If you need it in a second column, you could always do a CROSS JOIN onto the single-column (SELECT COUNT(*) AS TotalCount FROM … WHERE…) as tc.