Can somebody help me to optimize this query

IMDT 2017-02-03 11:41:24

I am a newbie on SQL Server. I am using SQL Server 2012. In this query, my query takes about 7 seconds to return nearly 13K rows. I could see that there is a huge difference between Estimated Rows and Actual Rows. I executed the sp_updatestats but the difference still remained. Can somebody pl. guide me towards any scope of optimizing this query. Thanks.

Aaron Bertrand 2017-02-03 16:35:19
Takes 7 seconds to return the rows and render them in a Management Studio grid? That's pretty good, IMHO. Is that really where you will be running this query and what you want to tune for? The time is probably largely being eaten up in rendering that grid – did you observe wait statistics when running the query? Were they mostly ASYNC_NETWORK_IO? Also, estimates are way off across the board (look at all the orange on the Top Operations tab), do you have auto update stats turned off? Could you try generating a new plan for this query after updating statistics on the main tables involved?
IMDT 2017-02-04 05:47:34
Dear Aaron, thanks for the response. 7 seconds is the time taken to return the results in Management Studio. I wanted to use this query for an ASP.Net Dashboard and hence I wanted to explore any scope of optimization since the dashboard might be accessed concurrently by about 40-50 people. The server is managed by our IT and I am only trying to develop the MIS as a self-set goal and therefore, I have no authorization for changing the server parameters like auto update stats etc. However, my mainly two tables are updated on daily basis with only 30 to 40 records. These tables are for Invoice Headers and Invoice Items.