帮助重构 SQL 查询 [英] Help refactoring an SQL query

问题描述

下面是我的 SQL 查询，它需要 10 多分钟并且仍在运行......

Below is my SQL Query which takes more than 10 minutes and still running....

select DISTINCT Auditdata.ID,ns.ProviderMaster_ID as CDRComment 
from Auditdata AuditData 
inner join AuditMaster am 
    on am.ID=AuditData.AuditMaster_ID 
inner join HomeCircleMaster hcm 
    on hcm.Ori_CircleMaster_ID=am.CircleMaster_ID 
    and hcm.Ori_ServiceTypeMaster_ID=1 
    and hcm.Dest_ServiceTypeMaster_ID=1 
inner join NoSeriesMaster ns 
    on (ns.CircleMaster_ID=am.CircleMaster_ID 
    or ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID) 
    and ns.ProviderMaster_ID<>am.ProviderMaster_ID  
    and ns.ServiceTypeMaster_ID=1 
INNER JOIN    NoSeriesMaster_Prefix PD 
    ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo       
    AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen))  
    AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT(INT, PD.PrefixLen))  
 where  AuditData.TATCallType is null   
    and  AuditData.AuditMaster_ID=74 
    and PrefixType='CALL'

内连接中使用的每一列都定义了索引，where子句中使用的列也定义了索引...

evey column used in the inner join defied a index and columns used in where clause is also defined index...

有什么办法可以快速查询上面的内容

is there any way to fast above query

请帮帮我...

谢谢

亲爱的朋友，我修改了我的 SQL 查询如下，它仍然需要很多时间来执行 15000000

Dear Friends i m modified my SQL Query is as follow its still take lot of time to execute Against 15000000

修改后的SQL Query如下:

modified SQL Query are as follows:

select DISTINCT Auditdata.ID,ns.ProviderMaster_ID as CDRComment from Auditdata AuditData inner join AuditMaster am on am.ID=AuditData.AuditMaster_ID inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster_ID=am.CircleMaster_ID and hcm.Ori_ServiceTypeMaster_ID=1 and hcm.Dest_ServiceTypeMaster_ID=1 inner join NoSeriesMaster ns on (ns.CircleMaster_ID=am.CircleMaster_ID or ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID) and ns.ProviderMaster_ID<>am.ProviderMaster_ID and ns.ServiceTypeMaster_ID=1 INNER JOIN NoSeriesMaster_Prefix PD ON Auditdata.callto like PD.PrefixNo + '%' AND AuditData.CallTolen = PD.PrefixLen + PD.AfterPrefixLen AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, NoSeriesLen + PD.PrefixLen)
where AuditData.TATCallType is null and AuditData.AuditMaster_ID=74 and PrefixType='CALL' 

现在我该怎么办??

亲爱的朋友

我的查询需要很多时间，因为下面部分代码NoSeriesMaster 包含 4000 行和 Auditdata 15000000 行使用内连接与 Noseriesmaster 匹配的审计数据中的每个调用列记录

my Query takes lot times because below part of code NoSeriesMaster Contain 4000 rows and Auditdata 15000000 rows with inner join every callto columns record in auditdata matched with the Noseriesmaster

内连接 NoSeriesMaster_Prefix PD
ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo
AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen))
AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT (INT, PD.PrefixLen))
其中 AuditData.TATCallType 为 null，AuditData.AuditMaster_ID=74 和 PrefixType='CALL'

INNER JOIN NoSeriesMaster_Prefix PD
ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo
AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen))
AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT (INT, PD.PrefixLen))
where AuditData.TATCallType is null and AuditData.AuditMaster_ID=74 and PrefixType='CALL'

推荐答案

很难说是什么导致了它，但以下可能有帮助:

It's difficult to say what is causing it, but the following may contribute:

• 对连接中的值执行诸如 SUBSTRING、CONVERT、LEFT 等转换会破坏性能，因为这意味着 SQL Server 无法有效地使用其索引.您可能需要考虑将需要进行此类转换的列提取到单独的列中并为其编制索引.

• Performing transformations such as SUBSTRING, CONVERT, LEFT etc on values in joins will ruin performance, because it means SQL Server can't effectively use its indexes. You may want to look at extracting the columns you need to do this type of conversion on into separate columns and index them.

次优索引 - 除了由于所有转换而无法使用的索引之外，您加入的其他列是否已正确索引?看看索引调整向导，它或许能帮到你.

Sub-optimal indexes - apart from the ones you can't use due to all the conversions, are the other columns you're joining on correctly indexed? Take a look at the index tuning wizard which may be able to help you out here.

这篇关于帮助重构 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！