我目前正在运行这个查询:
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它返回了正确的结果,但运行大约需要8分钟,我只是想知道是否有人有任何关于如何加快查询速度的建议?主要的问题是,如果这是一个项目的一部分,在这个项目中,我必须向客户演示最终结果,我只有10分钟的演示时间(大学规则),这个查询大约占项目的30%。
预计执行的大部分是排序,即57%。
发布于 2013-04-02 01:45:34
如果将查询重写为以下代码,您可能会发现查询执行得更好:
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如果这不能显著提高性能,那么我建议在现有查询上创建一个索引视图,并从索引视图中选择作为新查询-您可以找到有关创建索引视图here的更多信息,同时还有一些关于它们的限制的信息here。
发布于 2013-04-01 21:10:35
我认为性能问题是由于像这样的连接造成的:
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim
ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and
Time_Dim.Time_EndTime_Dim的粒度是什么?StartTimeDate的粒度是多少?这些名称表明,一个以天为单位,另一个以小时、分钟或秒为单位。这可能会导致大量额外的记录被匹配。
如果你有一个时间维度,为什么你要存储一个常规的日期?如果您有数据库日期时间,为什么要使用时间维度表?
此外,您应该为每个表提供一个可读的别名。试着弄清楚一些事情,比如:
SUM (staging.dbo.incoming_measure.ring_time) AS Ring_Time,比以下内容难得多:
SUM (im.ring_time) AS Ring_Time,其中im是incoming_message的一个很好的简短别名。
发布于 2013-04-01 21:01:27
试试这个可能会对你有帮助。
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 或
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_IDhttps://stackoverflow.com/questions/15743443
复制相似问题