我怎样才能加快我的SQL查询速度

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.

它正在返回正确的结果,但是大约需要8分钟才能运行,我只是想知道是否有人对如何加快查询有任何建议?主要问题是,如果这是我必须向客户展示最终结果的项目的一部分,我只允许10分钟演示(大学规则),这个查询大约是项目的30%。

The bulk of the estimated execution is the SORT which is 57%.

估计执行的大部分是SORT,即57%。

4 个解决方案

#1

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.

如果这不会显着提高性能,那么我建议您在现有查询上创建一个索引视图,并从索引视图中选择新查询 - 您可以在此处找到有关创建索引视图的更多信息,同时这里有一些有关其限制的信息。

#2

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.

Time_Dim的颗粒度是多少? StartTimeDate的粒度是多少?这些名称表明一个是以天为单位测量的,另一个是以小时,分钟或秒为单位。这可能会导致许多其他记录匹配。

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.

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

#3

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

#4

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)