How can i improve performance on count(*)

nick79 2016-10-20 19:30:01

query currently takes 1 second to execute but with the load it goes upto 5 seconds how can i tune the query for better performance

Aaron Bertrand 2016-10-20 19:33:04
Have you considered a matching index on #bTree? Heaps are generally not good at join/filter operations. Or, perhaps, using a TVP (also with the right index) instead of the temp tables.
nick79 2016-10-20 21:41:35
Yes tried on #btree still not results. Query is taking 54% in index seek.
Aaron Bertrand 2016-10-21 00:38:01
Please keep in mind that an estimated cost is just that, an estimated cost, and they always add up to 100% (well, maybe not always in SSMS), and something is always going to be the most expensive operator. I'd be interested in seeing the session file if you take the query and generate an actual plan within Plan Explorer (and use the With Live Query Profile option), instead of opening the plan you pulled from SSMS. This will give a much better idea of both where the time is being spent and also what the actual costs were.
Vlady Oselsky 2016-10-24 19:04:44
If you are working with a small number of rows, I would try to replace temp tables with table variables as they can perform faster when it comes to small row count. Also like @Aaron suggested is to run query in Plan Explorer to see Actual durations and other statistics that it is able to collect. This will help in further troubleshooting performance and suggesting a fix.