Reputation: 417
I'm currently running this query:
SELECT Time_ID,
Site_Type_ID,
Abandoned_ID,
WorkType_ID,
SUM (staging.dbo.incoming_measure.ring_time) AS Ring_Time,
SUM (staging.dbo.incoming_measure.hold_time) As Hold_Time,
SUM (staging.dbo.incoming_measure.talk_time) AS Talk_Time,
SUM (staging.dbo.incoming_measure.acw_time) AS ACW_Time,
COUNT(*) CallCount
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim
ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and
Time_Dim.Time_End
INNER JOIN datamartend.dbo.Site_Type_Dim
ON incoming_measure.DBID = Site_Type_Dim.Site_Type_Code
INNER JOIN datamartend.dbo.Abandoned_Call_Dim
ON incoming_measure.Abandoned = Abandoned_Call_Dim.abandoned_value
INNER JOIN DataMartEnd.dbo.Work_Type_Dim
ON incoming_measure.DBID = work_type_dim.MIG_Site_ID AND
Work_Type_Dim.Work_Type_Code = incoming_measure.Queue AND
incoming_measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND
Work_Type_Dim.DimEffectiveEndDtm
group by time_id, Site_Type_ID, Abandoned_ID, WorkType_ID
It is returning the correct results but is taking around 8minutes to run and I just wondered if anyone had any advice on how i can speed up the query? The main issue if this is part of a project in which i have to demonstrate the end result to a client and I am only allowed 10 minutes to demonstrate (University Rules) and this query is about 30% of the project.
The bulk of the estimated execution is the SORT which is 57%.
Upvotes: 1
Views: 249
Reputation:
You may find your query performs better if it is rewritten like this:
SELECT
Time_ID,
Site_Type_ID,
Abandoned_ID,
WorkType_ID,
SUM (im.ring_time) AS Ring_Time,
SUM (im.hold_time) As Hold_Time,
SUM (im.talk_time) AS Talk_Time,
SUM (im.acw_time) AS ACW_Time,
COUNT(*) CallCount
FROM incoming_measure im
INNER JOIN DataMartEnd.dbo.Time_Dim td
ON dateadd(mi,
15*floor(datediff(mi,
dateadd(dd, datediff(dd,0,im.StartTimeDate), 0),
im.StartTimeDate ) / 15),
dateadd(dd, datediff(dd,0,im.StartTimeDate), 0)
) = td.Time_Start
INNER JOIN datamartend.dbo.Site_Type_Dim std
ON im.DBID = std.Site_Type_Code
INNER JOIN datamartend.dbo.Abandoned_Call_Dim acd
ON im.Abandoned = acd.abandoned_value
INNER JOIN DataMartEnd.dbo.Work_Type_Dim wtd
ON im.DBID = wtd.MIG_Site_ID AND
im.Queue = wtd.Work_Type_Code AND
im.StartTimeDate BETWEEN wtd.DimEffectiveStartDtm AND wtd.DimEffectiveEndDtm
group by time_id, Site_Type_ID, Abandoned_ID, WorkType_ID
- so that the time dimension is joined on an equals value, rather than on a value between a range of values.
If this does not significantly improve performance, then I suggest creating an indexed view on your existing query, and selecting from the indexed view as your new query - you can find more about creating indexed views here, while there is some information on their limitations here.
Upvotes: 2
Reputation: 10099
You need indexes on the following fields:
Time_Dim.Time_ID
incoming_measure.DBID
incoming_measure.Queue
incoming_measure.Abandoned
incoming_measure.StartTimeDate
Site_Type_Dim.Site_Type_ID
Site_Type_Dim.Site_Type_Code
Abandoned_Call_Dim.Abandoned_ID
Abandoned_Call_Dim.abandoned_value
Work_Type_Dim.WorkType_ID
Work_Type_Dim.Work_Type_Code
work_type_dim.MIG_Site_ID
I'm not completely certain about the order of the fields in the indexes, so you may have to experiment, but I suggest:
create index Time_Dim_Time_ID on Time_Dim (Time_ID)
create index incoming_measure_index on (DBID, Queue, Abandoned, StartTimeDate)
create index Site_Type_index on Site_Type_Dim (Site_Type_ID, Site_Type_Code)
create index Abandoned_Call_index on Abandoned_Call_Dim (Abandoned_ID, abandoned_value)
create index Work_Type_index on Work_Type_Dim (WorkType_ID, Work_Type_Code, MIG_Site_ID)
Upvotes: 0
Reputation: 1271241
I think the problem with performance is due to joins like the:
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim
ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and
Time_Dim.Time_End
What is the granulatiry of Time_Dim
? What is the granularity of StartTimeDate
? The names suggest that one is measured in days and the other in hours, minutes, or seconds. This could result in lots of additional records being matched.
If you have a time dimension, why are you storing a regular date? If you have database date times, why are you using a time dimension table?
Also, you should give every table a readable alias. Trying to figure out someting like:
SUM (staging.dbo.incoming_measure.ring_time) AS Ring_Time,
is much harder than:
SUM (im.ring_time) AS Ring_Time,
Where im
is a nice short alias for incoming_message
.
Upvotes: 1
Reputation: 411
Try this may be help you.
SELECT * FROM
(SELECT
ROW_NUMBER() OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID ORDER BY time_id) No,
Time_ID,
Site_Type_ID,
Abandoned_ID,
WorkType_ID,
SUM (staging.dbo.incoming_measure.ring_time) OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID) AS Ring_Time,
SUM (staging.dbo.incoming_measure.hold_time) OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID) As Hold_Time,
SUM (staging.dbo.incoming_measure.talk_time) OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID) AS Talk_Time,
SUM (staging.dbo.incoming_measure.acw_time) OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID) AS ACW_Time,
COUNT(1) OVER( PARTITION BY time_id, Site_Type_ID, Abandoned_ID, WorkType_ID) CallCount
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim
ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and
Time_Dim.Time_End
INNER JOIN datamartend.dbo.Site_Type_Dim
ON incoming_measure.DBID = Site_Type_Dim.Site_Type_Code
INNER JOIN datamartend.dbo.Abandoned_Call_Dim
ON incoming_measure.Abandoned = Abandoned_Call_Dim.abandoned_value
INNER JOIN DataMartEnd.dbo.Work_Type_Dim
ON incoming_measure.DBID = work_type_dim.MIG_Site_ID AND
Work_Type_Dim.Work_Type_Code = incoming_measure.Queue AND
incoming_measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND
Work_Type_Dim.DimEffectiveEndDtm
) AS T1 WHERE No = 1
or
SELECT Time_ID,
Site_Type_ID,
Abandoned_ID,
WorkType_ID,
SUM (staging.dbo.incoming_measure.ring_time) AS Ring_Time,
SUM (staging.dbo.incoming_measure.hold_time) As Hold_Time,
SUM (staging.dbo.incoming_measure.talk_time) AS Talk_Time,
SUM (staging.dbo.incoming_measure.acw_time) AS ACW_Time,
COUNT(1) CallCount
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim
ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and
Time_Dim.Time_End
INNER JOIN datamartend.dbo.Site_Type_Dim
ON incoming_measure.DBID = Site_Type_Dim.Site_Type_Code
INNER JOIN datamartend.dbo.Abandoned_Call_Dim
ON incoming_measure.Abandoned = Abandoned_Call_Dim.abandoned_value
INNER JOIN DataMartEnd.dbo.Work_Type_Dim
ON incoming_measure.DBID = work_type_dim.MIG_Site_ID AND
Work_Type_Dim.Work_Type_Code = incoming_measure.Queue AND
incoming_measure.StartTimeDate BETWEEN Work_Type_Dim.DimEffectiveStartDtm AND
Work_Type_Dim.DimEffectiveEndDtm
group by time_id, Site_Type_ID, Abandoned_ID, WorkType_ID
Upvotes: 0