帮助重构 SQL 查询

Question

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

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 子句中使用的列也定义了索引...

有什么方法可以快速执行以上查询

吗？帮助我...

谢谢

---

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

修改后的 SQL 查询如下：

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 行 通过内部联接，auditdata 中的每个 callto 列记录都与 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'

Answer 1

很难说是什么原因造成的，但以下可能会造成影响：

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

• 次优索引 - 除了由于所有转换而无法使用的索引之外，您要加入的其他列是否已正确建立索引？ 请查看索引调整向导，它可能会在这里为您提供帮助。

Answer 2

您可以做的是在执行此查询时使用 SQL Server Management Studio 内的执行计划视图。
它将显示 SQL 在处理查询时花费最多时间的步骤。
从那里您至少知道需要在哪里进行优化。

仅供参考，SQL Server Management Studio Express 中还提供了执行计划。

只需打开一个新的查询窗口，点击查询> 显示估计执行计划并运行您的查询。 查询完成后，将弹出执行计划。

Answer 3

另一件事 - 您可以尝试将一些条件从 WHERE 放入 JOIN：

from Auditdata AuditData 
inner join AuditMaster am 
    on am.ID=AuditData.AuditMaster_ID AND AuditData.TATCallType is null   
    and  AuditData.AuditMaster_ID=74 

但我认为查询优化器应该这样做。

无论如何，你需要先查看执行计划。

Answer 4

执行计划会准确地告诉您什么花费了最多的时间。

这可能是导致最多工作的最后一个连接。 当您比较计算值时，数据库无法使用索引进行查找。

我看到您对数值执行了 CONVERT(VARCHAR(10), ...)，然后将其与数字进行比较。 您应该能够删除该转换。

您在多个位置将字段 PrefixLen 转换为数字。 该字段真的是文本字段吗？如果是，您可以将其转换为数字字段吗？

您将 AuditData 的第一部分与 PrefixNo 进行比较，然后将该字段的更多部分与 PrefixNo + NoSeries 进行比较。 除非由于缺少分隔符而导致值“出血”（例如“01”+“23”=“0”+“123”）出现问题，否则您可以删除第一个比较。