SQL Server 查询需要 3 分 40 秒。如何改善执行时间？

Question

我有一个查询需要 3 分 40 秒。

这里除了AirESet和ELT_B表外，其他表都是查找表，这些表中的数据少很多。有趣的是，如果没有这些查找表，执行时间约为 30 秒。我的理解是查找表包含的数据少得多，性能应该不会降低。

SELECT DISTINCT 
    RPGM.GroupID as RegionPerilGroupID,
    RPGM.GroupName as RPGroupName,
    LLGM.GroupID as LossLevelGroupID,
    LLG.GroupName As LLGroupName
FROM
    Acc.dbo.AIRESet ES 
JOIN 
    Acc.dbo.vw_RegionPerilGroup RPGM ON ES.RegionperilID = RPGM.RegionperilID 
                                     AND ModelLossFileID = 65 -- Line of Business
JOIN 
    AR.LA.ELT_B ELT WITH (NOLOCK) ON ES.EventNum = ELT.EventNum 
                                  AND ELT.Versionid = 215 
JOIN 
    AR.LA.LossLevelGroupMappings LLGM ON ELT.LossLevelID = LLGM. LossLevelID 
JOIN 
    AR.LA.LossLevelGroup LLG ON LLGM.GroupID = LLG.GroupID 
                             AND LLG.LosstypeId = 3 
JOIN 
    AR.LA.ReportMap RM ON RPGM.GroupId = RM.RegionPerilGroupID 
                       AND LLGM.GroupID = RM.LossLevelGroupID
JOIN 
    AR.LA.ReportMapFilterMappings RMFM ON RM.ReportMapID = RMFM.ReportMapID
                                       AND RMFM.FilterID = 15
ORDER BY 
    LossLevelGroupID, RegionPerilGroupID 

Answer 1

让我猜猜。
请将 option (HASH JOIN) 添加到您的查询中。

---

附言
SELECT distinct ... 通常表示查询逻辑问题。
请找出重复的原因。

---

...和-

1. 没有。
   相等连接操作不需要索引。
   这是 SQL Server，不是 MySQL，它支持 HASH JOIN。

   此外-
   使用索引检索大量数据会导致性能显着下降。

---

1. 没有。
   GROUP BY/DISTINCT 并不一定意味着 SORT 操作。
   它也可以使用复杂度为 O(1) 而不是 O(n*log n) 的 HASH 来实现。

---

统计的局限性

select @@version

---

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) 
  Jun  9 2015 12:06:16 
  Copyright (c) Microsoft Corporation
  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

---

create table t (n int primary key)

;with   t (n) as (select 1 union all select n+1 from t where n < 1000)
insert into dbo.t select n from t option  (maxrecursion 0)

create statistics t_n on t (n)
update statistics t (t_n)

---

;with x as (select * from t where sqrt(n) = n)
select * from x x0,x x1,x x2,x x3,x x4,x x5

---

+---+---+---+---+---+---+
| n | n | n | n | n | n |
+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 1 | 1 |
+---+---+---+---+---+---+

---

哈希连接与。循环连接

create table t (i int,j1 int,j2 int,j3 int,j4 int,j5 int)

;with   t (n) as (select 0 union all select n+1 from t where n < 999)
insert into dbo.t (i,j1,j2,j3,j4,j5) 
select      1+t0.n+1000*t1.n,1+t0.n,1+t0.n,1+t0.n,1+t0.n,1+t0.n
from        t t0,t t1 
option      (maxrecursion 0)

create table d1 (j int primary key,k int)
create table d2 (j int primary key,k int)
create table d3 (j int primary key,k int)
create table d4 (j int primary key,k int)
create table d5 (j int primary key,k int)

;with   t (n) as (select 0 union all select n+1 from t where n < 999)
insert into dbo.d1 (j,k) 
select      1+t0.n,1
from        t t0 
option      (maxrecursion 0)

insert into d2 select * from d1
insert into d3 select * from d1
insert into d4 select * from d1
insert into d5 select * from d1

---

select  sum (d1.k+d2.k+d3.k+d4.k+d5.k)

from            t 
        join    d1 on t.j1 = d1.j 
        join    d2 on t.j2 = d2.j 
        join    d3 on t.j3 = d3.j 
        join    d4 on t.j4 = d4.j 
        join    d5 on t.j5 = d5.j 

option (hash join)

---

select  sum (d1.k+d2.k+d3.k+d4.k+d5.k)

from            t 
        join    d1 on t.j1 = d1.j 
        join    d2 on t.j2 = d2.j 
        join    d3 on t.j3 = d3.j 
        join    d4 on t.j4 = d4.j 
        join    d5 on t.j5 = d5.j 

option (loop join)

哈希组对比。订单组

create table t (i int,j int)

;with   t (n) as (select 0 union all select n+1 from t where n < 9)
insert into dbo.t (i,j) 
select      t0.n,t0.n
from        t t0,t t1,t t2,t t3,t t4,t t5,t t6

---

select      distinct
            i,j
from        t
option      (HASH GROUP)
;

select      i,j
from        t
group by    i,j
option      (HASH GROUP)
;

---

select      distinct
            i,j
from        t
option      (ORDER GROUP)
;

select      i,j
from        t
group by    i,j
option      (ORDER GROUP)
;