Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything.

Anyone got any ideas how to get this SORT percentage down or remove it all together? Here is the query I'm running.

SELECT Time_ID,
       Site_Type_ID,
       Abandoned_ID,
       WorkType_ID,
       SUM (staging.dbo.measure.ring_time) AS Ring_Time,
       SUM (staging.dbo.measure.hold_time) AS Hold_Time,
       SUM (staging.dbo.measure.talk_time) AS Talk_Time,
       SUM (staging.dbo.measure.acw_time)  AS ACW_Time,
       COUNT(*)                            CallCount
FROM   measure
       INNER JOIN DataMartEnd.dbo.Time_Dim
         ON measure.StartTimeDate BETWEEN Time_Dim.Time_Start AND Time_Dim.Time_End
       INNER JOIN datamartend.dbo.Site_Type_Dim
         ON measure.DBID = Site_Type_Dim.Site_Type_Code
       INNER JOIN datamartend.dbo.Abandoned_Call_Dim
         ON measure.Abandoned = Abandoned_Call_Dim.abandoned_value
       INNER JOIN DataMartEnd.dbo.Work_Type_Dim
         ON measure.DBID = work_type_dim.MIG_Site_ID
            AND Work_Type_Dim.Work_Type_Code = measure.Queue
            AND measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND Work_Type_Dim.DimEffectiveEndDtm
GROUP  BY Abandoned_ID,
          WorkType_ID,
          Site_Type_ID,
          time_id 

Thanks for the help.

share|improve this question

migrated from stackoverflow.com Apr 2 '13 at 8:12

This question came from our site for professional and enthusiast programmers.

1  
Can you let us know the result of the following query? pastebin.com/1RWAHFZK Interested to see how many rows are going into the final join. That is a triangular join on a 70,000 row table with a residual predicate. If thousands of rows are going into the join this could easily be very expensive. –  Martin Smith Apr 1 '13 at 11:53

2 Answers 2

up vote 4 down vote accepted
Group by Abandoned_ID, WorkType_ID , Site_Type_ID, time_id

One way to implement a group by is to sort the input. Stream Aggregate Showplan Operator:

The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan.

QED.

Further reading: Craig Freedman blog entry on Stream Aggregate and the alternative Hash Aggregate.

share|improve this answer
    
Hi, thanks for that. I'm unsure how i would be able to do this within the query? –  Richard C Apr 1 '13 at 10:22
    
Looking at my execution plan the stream aggregate operator is used after the sort? –  Richard C Apr 1 '13 at 10:30
    
The streaming aggregate is for your SUM and COUNT. –  Remus Rusanu Apr 1 '13 at 10:31
    
If you want more details, post the actual (not estimate) plan XML (the .sqlplan file). –  Remus Rusanu Apr 1 '13 at 10:32
    
Hi sorry about this, i've saved the xml file (executionplan.sqlplan) but how do i upload it for you to see? –  Richard C Apr 1 '13 at 10:36

Is this database a star schema? If so, what are the indexes on the dimension tables?

If you do a group by on columns that have sorted indexes, the optinizer will replace some or all of the sorting effort with index scans.

If some of the columns in your group by do have indexes, and others don't, try putting the one with the indexes first in the group by.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.