主页 / dba / 问题 / 242611
Accepted
vikrant rana
Asked: 2019-07-11 23:29:21 +0800 CST

提高 sql server 表的连接性能

  • 772

我在 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 上都有什么样的索引。

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

需要建议来优化连接和索引。

sql-server ssis-2016

1 个回答

  1. Best Answer

    底部的 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
    • 1

相关问题