December 21, 2017 at 12:08 pm
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.
Thanks,
Sam
December 21, 2017 at 12:19 pm
vsamantha35 - Thursday, December 21, 2017 12:08 PMHi 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.
Thanks,
Sam
What is the problem? There are only seven rows of data returned. And what do you consider very fast with out the order by versus with the order by?
December 21, 2017 at 12:38 pm
The 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.
December 21, 2017 at 12:43 pm
vsamantha35 - Thursday, December 21, 2017 12:38 PMThe 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.
Two choices.
One, don't sort in SQL Server and have the application that receives the data sort it there if needed.
Two, create a filtered nonclustered index on the table like this:
CREATE NONCLUSTERED INDEX idx_Sample ON [LubeAnalyst].[Sample] (
[x].[DateDrawn] DESC
,[x].[EquipmentLife] DESC
,[x].[LubricantLife] DESC
,[x].[DateRegistered] DESC
,[x].[SampleID]
)
WHERE [SampleStatusID] = 1;
December 21, 2017 at 12:48 pm
Thanks Lynn. 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.
December 21, 2017 at 12:53 pm
vsamantha35 - Thursday, December 21, 2017 12:48 PMThanks Lynn. 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.
That I can't answer. All I can suggest is that you demonstrate the impact that a simple query like this one can have when called by multiple connections in a short period of time.
December 21, 2017 at 1:04 pm
vsamantha35 - Thursday, December 21, 2017 12:48 PMThanks Lynn. 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.
This is really the main question here and you probably should have led with this. The only suggestion I have is to remind them of distributed processing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 21, 2017 at 1:05 pm
Thanks for taking time. Thanks a lot.
December 21, 2017 at 1:06 pm
drew.allen - Thursday, December 21, 2017 1:04 PMvsamantha35 - Thursday, December 21, 2017 12:48 PMThanks Lynn. 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.This is really the main question here and you probably should have led with this. The only suggestion I have is to remind them of distributed processing.
Drew
Drew, can you please elaborate a little bit?
December 21, 2017 at 1:26 pm
vsamantha35 - Thursday, December 21, 2017 1:06 PMdrew.allen - Thursday, December 21, 2017 1:04 PMvsamantha35 - Thursday, December 21, 2017 12:48 PMThanks Lynn. 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.This is really the main question here and you probably should have led with this. The only suggestion I have is to remind them of distributed processing.
Drew
Drew, can you please elaborate a little bit?
Simple, is it faster to have one machine (the server) perform 60 sort operations or 60 machines (the clients) to each perform one sort operation?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 21, 2017 at 2:36 pm
@drew,
It depends on the server and clients.
@vsamantha35
Try creating this index:
CREATE INDEX IXLubeAnalyst__Sample__DateDrawn_EquipmentLife_LubricantLife_DateRegistered_SampleID
ON LubeAnalyst.Sample
( DateDrawn DESC
, EquipmentLife DESC
, LubricantLife DESC
, DateRegistered DESC
, SampleID ASC
) ;
December 21, 2017 at 2:40 pm
Joe Torre - Thursday, December 21, 2017 2:36 PM@drew,
It depends on the server and clients.
Try creating this index:
CREATE INDEX IXLubeAnalyst__Sample__DateDrawn_EquipmentLife_LubricantLife_DateRegistered_SampleID
ON LubeAnalyst.Sample
( DateDrawn DESC
, EquipmentLife DESC
, LubricantLife DESC
, DateRegistered DESC
, SampleID ASC
) ;
Same thing I mentioned except I made it a filtered index.
December 21, 2017 at 3:11 pm
The key lookups should cost more than the sort for only 7 rows (I know the lookup is in both queries). You should consider adding DateRegistered to the NC lindex.
Actually, you should really review the clustered index and consider using SampleStatusID as the first clustering key,, but you're probably much less likely to want to go that far, even though it could potentially help performance across many queries.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 22, 2017 at 6:48 am
vsamantha35 - Thursday, December 21, 2017 12:48 PMThanks Lynn. 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.
There's no real issue with sorting in the database if it's done correctly. The key is WHEN the sort appears in the execution plan. In most cases, ORDER BY will be among one of the last things done but not always... especially if the sorts are on calculated columns from a view, etc.
Issuing a draconian edict such as never sorting in the database will possibly lead to a great amount of stupid code because it's sometimes hugely advantageous to sort in the database. Don't issue such an edict.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2017 at 11:02 am
ScottPletcher - Thursday, December 21, 2017 3:11 PMThe key lookups should cost more than the sort for only 7 rows (I know the lookup is in both queries). You should consider adding DateRegistered to the NC lindex.Actually, you should really review the clustered index and consider using SampleStatusID as the first clustering key,, but you're probably much less likely to want to go that far, even though it could potentially help performance across many queries.
Actually, I think some major index analysis needs to be done on this table, considering there are 15 indexes on it, and some look like near duplicates:
- [IDX_NC_Sample_ComponentID_SampleNumber]
- [IDX_NC_Sample_ComponentID]
can be consolidated, same index key column, just different include columns
- [IDX_NC_Sample_ComponentID_IsActive_SampleNumber_SampleStatusID]
is similar to above 2, research needed
- [IDX_NC_Sample_EquipmentLife_EquipmentLifeUnitID_LubricantLife_LubricantLifeUnitID_TopUpVolume]
- [nci_wi_Sample_2603D396CB0E4AF80959A093AB4C9037]
only difference is [IsActive] column being 8th or 3rd index key column, research needed
- [IDX_NC_Sample_EquipmentLife_EquipmentLifeUnitID_LubricantLifeUnitID_TopUpVolumeUnitID_TopUpVolumePerUnitID_IsActive]
is similar to above 2 but skips [LubricantLife] column as part of index key, research needed
- [IDX_NC_Sample_SampleCondition]
- [nci_wi_Sample_AF4FD50D969088FBC95F7D420791ED50]
can be consolidated, same index key column, second has 1 include column
5 indexes that start with [SampleStatusID] as the index key, research needed.
Also, before changing your clustering index, I'd look at overall index usage for a while and see if there is one of these NC indexes that is causing the majority of the lookups.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply