提高 sql server 表的連接性能

我在 sql server 上有一個表，可以說是 TableA。和另一個表作為 TableB。這兩個表都負載很重。

表 A 載入了 35883788 條記錄。它在 program_id 列上有很多重複項。

表 B 載入了記錄 27343331。它還在 program_id 的列上保存了一個副本。由於某些業務原因，我們需要在兩個表上的 program_id 上保留重複記錄。

現在我在給定的表上執行左連接：

select 
a.*,b.date_of_birth,
datediff(year,b.date_of_birth,a.from_date)-
(case when dateadd(year,
          datediff(year,b.date_of_birth,a.from_date),b.date_of_birth)
          > a.from_date then 1 else 0
                  end) as age_final,
case when datediff(yyyy,b.date_of_birth,a.from_date) is null then ''
    when datediff(yyyy,b.date_of_birth,a.from_date) <=1 then 'less than 1 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=17 then 'then 1 - 17 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=29 then 'then 19 - 29 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=39 then 'then 30 - 39 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=49 then 'then 40 - 49 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=59 then 'then 50 - 59 year'
    when datediff(yyyy,b.date_of_birth,a.from_date) <=64 then 'then 60 - 64 year'
else 'More than 64 years' end as Age_band
from TableA a 
left join ( select program_id,date_of_birth,max(process_date) 
           from TableB 
           group by program_id,date_of_birth) b
   on a.program_id=b.program_id;

我的內部查詢給了我一組獨特的 program_id 使用 group by 語句。

這些表都沒有索引。如果我必須在兩個表上創建索引，我需要在兩個表的 program_id 上都有什麼樣的索引。

我可以處理哪些其他事情來提高加入性能。

需要建議來優化連接和索引。

底部的 DDL 和 DML

可以根據給定資訊創建的標準索引

CREATE NONCLUSTERED INDEX IX_program_id_date_of_birth_process_date
ON dbo.TableB (program_id,date_of_birth,process_date);
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id);

執行查詢

SET STATISTICS IO, TIME ON;
select 
a.*,b.date_of_birth
from dbo.TableA a 
left join ( select program_id,date_of_birth,max(process_date)  as maxprocess_date
           from dbo.TableB 
           group by program_id,date_of_birth) b
   on a.program_id=b.program_id;

使用 NC 索引，TableA因為ID它包含在 NC 索引中，TableA除了program_id列（該索引的關鍵列）之外沒有其他列。

---

但是，由於您正在使用，select a.*您可能必須將所有這些列添加到TableA包含列的 NC 索引中，這將根據表中列的數量增加您的索引大小。

如果我們添加一個varchar(3)列並用一個值填充它：

ALTER TABLE dbo.TableA ADD bla varchar(3) 
DEFAULT ('bla')
WITH VALUES;

不再使用索引：

由於添加了附加bla列：

要解決此問題bla，請重新創建包含列的索引。

DROP  INDEX IX_TableA ON dbo.TableA;
CREATE NONCLUSTERED INDEX IX_TableA
ON dbo.TableA(program_id)
INCLUDE(bla);

---

此外

這些表都沒有索引。

堆表並不理想，您應該考慮添加一個有意義的聚集索引並閱讀索引。

更多關於堆表和具有聚集索引的表之間的區別here

選擇一個。*

使用 Select *不是一個好習慣，請考慮分別命名您的 select 語句中使用的所有列。

要踢的壞習慣：使用 SELECT *

---

DDL + DML 用於測試

CREATE TABLE 
dbo.TableA(ID INT IDENTITY(1,1) PRIMARY KEY,
          program_id INT);
CREATE TABLE 
dbo.TableB(ID INT IDENTITY(1,1) PRIMARY KEY,
          program_id INT,
          date_of_birth date,
          process_date datetime2)
-- Dataset reduced by / 10 ~= 3 588 378

INSERT INTO 
dbo.TableA WITH(TABLOCK)  (program_id)
SELECT TOP(3588378) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 3588

-- Dataset reduced by / 10 ~= 2 734 333
INSERT INTO 
dbo.TableB WITH(TABLOCK) (program_id,date_of_birth,process_date) 
SELECT TOP(2734333) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000,
CAST('01/01/1990' AS date),
DATEADD(MINUTE,-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),GETDATE()) -- one minute less to get distinct process_dates
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;
-- values from 0 to 2734

引用自：https://dba.stackexchange.com/questions/242611