首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何提高SQL查询速度

问如何提高SQL查询速度
EN

Stack Overflow用户
提问于 2013-04-01 20:25:58
回答 4查看 204关注 0票数 1

我目前正在运行这个查询:

代码语言:javascript
复制
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%。

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2013-04-02 01:45:34

如果将查询重写为以下代码,您可能会发现查询执行得更好:

代码语言:javascript
复制
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。

票数 2
EN

Stack Overflow用户

发布于 2013-04-01 21:10:35

我认为性能问题是由于像这样的连接造成的:

代码语言:javascript
复制
FROM incoming_measure
INNER JOIN DataMartEnd.dbo.Time_Dim 
        ON incoming_measure.StartTimeDate BETWEEN Time_Dim.Time_Start and 
                                                  Time_Dim.Time_End

Time_Dim的粒度是什么?StartTimeDate的粒度是多少?这些名称表明,一个以天为单位,另一个以小时、分钟或秒为单位。这可能会导致大量额外的记录被匹配。

如果你有一个时间维度,为什么你要存储一个常规的日期?如果您有数据库日期时间,为什么要使用时间维度表?

此外,您应该为每个表提供一个可读的别名。试着弄清楚一些事情,比如:

代码语言:javascript
复制
SUM (staging.dbo.incoming_measure.ring_time) AS Ring_Time,

比以下内容难得多:

代码语言:javascript
复制
SUM (im.ring_time) AS Ring_Time,

其中im是incoming_message的一个很好的简短别名。

票数 1
EN

Stack Overflow用户

发布于 2013-04-01 21:01:27

试试这个可能会对你有帮助。

代码语言:javascript
复制
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 

或

代码语言:javascript
复制
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
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/15743443

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档