sort performance in query plan

Hi All,

This is related query tuning. I have a query which is doing expensive sorting. Without ORDER BY clause the query is returning the result set very fast when compared with having an ORDER BY DESC clause. Is there a way to avoid sorting from db side and suppose if the developer is so adamant and not listening for remove the ORDER BY clause. Is there an alternative way so that we can eliminate the sort operator in the execution plan.

Attaching the plan. Please share your thoughts.link text

Thanks

M

query-plans.zip (19.7 kB)
avatar image By Maahi 1 asked Dec 21, 2017 at 07:15 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Dec 21, 2017 at 07:27 PM

You can have the developer sort in the app (can be quite effective when using a caching layer, for example), or you can add/change an index so that sorting is supported without an additional sort operator. There is no way to get SQL Server to sort without, well, sorting.

avatar image Maahi Dec 21, 2017 at 07:51 PM

Thanks Aaron. One more thing, I want to ask is that, how can we make the DEV team understand why not to do the sorting at DB layer and can push it to the application layer. Most people are very reluctant to do so and they want everything to me done(all kinds of massaging within stored proc iteself) and they just add the data set to the grid control or whatever ... they use it in the front end.. How to make them understand in a nice polite way.

10|10000 characters needed characters left

2 answers: sort voted first

So I've looked at the execution plans. They are estimated plans, not actual plans, so they don't show the work actually done.

However, the plans show the exact same plan with the addition of a sort operator. (Might sound obvious to you but it's not - adding an ORDER BY can result in the optimizer picking a completely different execution plan). And they show an estimated row count of 7. There is no way that sorting 7 rows will ever take more than just a few milliseconds.

If you see much bigger performance issues than that, then please post the ACTUAL execution plans and I'll see what they show.

avatar image By Hugo Kornelis 211 answered Dec 21, 2017 at 07:27 PM
more ▼
(comments are locked)
avatar image Maahi Dec 21, 2017 at 08:26 PM

Thanks Hugo. The actual problem is, running the query individually for 1 user it is performing well. however, when the same sp/sql stmt is execute with 60 concurrent connections then the average response time is more than 3 secs and there SLA for the query to return the results back should be 1 or less than 1 secs. That's the concern.

avatar image Hugo Kornelis Dec 21, 2017 at 08:41 PM

Even for 60 concurrent connections, fetching and sorting just 7 rows should not take that much time.

Please post an actual query plan instead of the estimated plan, there must be something else going on.

avatar image Maahi Dec 21, 2017 at 08:51 PM +

Its the actual plan only. I have executed the sql stmts in ssms and sending the plan again. If you can provide email and provide the test data as well.

10|10000 characters needed characters left
avatar image By Maahi 1 answered Dec 21, 2017 at 08:51 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

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

Follow this question

Topics:

x664
x28
x20
x2
x2

asked: Dec 21, 2017 at 07:15 PM

Seen: 21 times

Last Updated: Dec 21, 2017 at 09:08 PM