Can somebody help me to optimize this query

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.

avatar image By IMDT 1 asked Feb 03, 2017 at 11:41 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Feb 03, 2017 at 04:35 PM

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?

avatar image IMDT Feb 04, 2017 at 05:47 AM

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.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Feb 03, 2017 at 11:41 AM

Seen: 102 times

Last Updated: Feb 04, 2017 at 05:47 AM