The actual execution plan of a query shows a total of 2.040s time(taking the sum of time taken at every step) but takes 52 secs(time shown at the bottom of SQL Server Management Studio) to complete.

Why there is so much difference in both the times? And how can I reduce this 52 secs time?

CodePudding user response：

The elapsed time in SSMS includes network round-trip time, client render time, etc.

The execution plan indicates how long it took the server to process the query, not how long it took to stream the results to you.

If you're outputting data to a messages pane or, worse, a grid, that isn't free. As SSMS draws the data in the grid, the server is sending you rows over the network, but the query engine isn't doing anything anymore. Its job is done.

The execution plan itself only knows about the time the query took on the server. It has no idea about network latency or slow client processing. SSMS will tell you how much time it spent doing that, and the execution plan doesn't have any visibility into it at all because it's generated before SSMS has done its thing.

The execution plan runs on the server. It doesn't even know what SSMS is, never mind what it's doing with your 236,833 rows. Let's think about it another way:

You buy some groceries, and the cash register receipt says it took you 4 minutes to check out. Then you take the long way home, stop for coffee, and you dropped the groceries on the way into the house, and then it took you 20 minutes to remember where everything goes. Finally, you sit down on the couch. The cash register receipt doesn't then update and add your travel time and organization time, which is equivalent to what SSMS is doing when it is struggling trying to show you 236,833 rows.

And this is why we don't try to time the performance of a query by adding in unrealistic things that won't happen in the real world, because no real world user can process 200,000 rows of anything. Really don't draw any conclusions about real world performance from your testing in a client GUI. Is your application going to do pagination, or aggregation, or something else so an end user doesn't have to wait for 200,000 rows to render? If so, test that. If not, reconsider.

To make this faster in the meantime, try with the "Discard results after execution" option in SSMS.