UNION Causing Sorting - Query Optimization
-
Hi all,
can this query be written to get rid of the SORT in the execution plan? First it does an Index Seek but then it does a SORT of the results which is quite expensive.
select'S' as CodeVal,*
from OffertoSell
with (nolock)
where
ticker = 'DKNY' and
local_ts between '20090322 00:00:00' and '20100323 00:00:00'
union
select
'B' as CodeVal,*
from OffertoBuy
with (nolock)
where
ticker = 'DKNY' and
local_ts between '20090322 00:00:00' and '20100323 00:00:00'
order by local_ts asc
I have tried a UNION all and its still sorting due to the order by statement at the end
Thanks in advance
-
Try UNION ALL instead of UNION
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Yep as stated I have tried that but the order by at the end still causes a SORT to carried out for each query either side of the UNION ALL
-
dibbydibby (1/18/2011)
Yep as stated I have tried that but the order by at the end still causes a SORT to carried out for each query either side of the UNION ALLApologies, didn't see your comment at the end.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
There is a good chance that Iām missing something here, but if you want to get rid of the sort, why did you add a sort clause to your query?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
The order by matches the Index definition. Its clustered on local_ts. So it shouldnt need to sort no?
-
It needs to sort because it's dealing with two sets of data, from the two queries, based on two different tables in the union. Also the query probably won't be using the clustered index unless there's no non-clustered index that satisfies the where clause.
Is the sort causing a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Not much option of overcoming the sort operation unitll order by is removed,,,
one way you can try creating indexed view,,,,
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/ -
GilaMonster (1/18/2011)
It needs to sort because it's dealing with two sets of data, from the two queries, based on two different tables in the union. Also the query probably won't be using the clustered index unless there's no non-clustered index that satisfies the where clause.Is the sort causing a problem?
Both tables have the identical schema and the clustered index consists of all the columns in the where clause (i.e ticker and local_ts).
The clustered index seek is being used for both queries around the UNION. but in the next step both streams do SORTS.
psingla (1/18/2011)
Not much option of overcoming the sort operation unitll order by is removed,,,Yep i feel you may be right there š
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply