sql - 提高 MS SQL 事务性能

Question

如果您缺少信息，我会根据要求附上它们。

工作区

我有一个在此类 MS SQL 2012 标准版上运行的数据库:

• 表格:

  • 用户(id、softId(不唯一)、出生日期)

    • 行数:1050 万行
    • 索引:所有三列，出生日期(聚集)

  • 文档(docId、userId、creationDate、deleteDate、姓氏、forename、classificationId)

    • 行数:2300 万

    • 索引:姓氏、名字、docId、creationDate、userID(聚集)

    • 注意:在这种特定情况下，名称与文档相关，而不是与 userId 相关

  • 分类(id、描述)

    • 行数:200

  • 三个表“数据”

    • 行数:10、13 和 30 万行
    • 索引:docId

• 关系:

  • 用户到文档:1 到 n

  • 文档分类:1 到 n

  • 文档到数据表:1 到 n

要选择完整的记录，我实际上按以下语句进行:

服务器执行时间 16 秒

SELECT * FROM (
    select * from docs 
    where userID in (
        select distinct userID from users where softId like '...'
    )
) as doc
LEFT JOIN users on users.userID = doc.userId
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

已更新 - 现在 15 秒

SELECT
docID, calssificationId, classificationDescription,
userId, softId, forename, lastname, birthdate,
data1.id, data1.date, data2.id, data2.date, data3.id, data3.date,
FROM docs
JOIN users on users.userID = doc.userId AND softId like '...'
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

执行计划

• execution plan

服务器执行时间 17 秒

DECLARE @userIDs table( id bigint );
DECLARE @docIDs table( id bigint );

insert into @userIDs select userID from users where softId like '...';
insert into @docIDs select docId from docs where userId in ( select id from @userIDs);
SELECT * FROM users where userID in ( select id from @userIDs);
SELECT * FROM docs where docID in (select id from @docIDs);
SELECT * FROM data1 where data1.docId in (select id from @docIDs);
SELECT * FROM data2 where data2.docId in (select id from @docIDs);
SELECT * FROM data3 where data3.docId in (select id from @docIDs);
GO

已更新 - 现在 14 秒

DECLARE @userIDs table( id bigint, softId varchar(12), birthdate varchar(8) );
DECLARE @docIDs table( id bigint, classification bigint, capture_date datetime, userId bigint, lastname varchar(50), forename varchar(50) );


INSERT INTO @userIDs select userID, softId, birthdate from users where softId like '...';
INSERT INTO @docIDs select docID, classification, capture_date, userID, lastname, forename from docs where userID in ( select id from @userIDs);

SELECT * FROM @userIDs;
SELECT * FROM @docIDs;

SELECT [only needed fields] FROM data1 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data2 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data3 where docID in (select id from @docIDs);

执行计划

• execution plan userIds
• execution plan docIds
• execution plan userIds output
• execution plan data1

常规更新@AntonínLejsek 建议将文档的 docId 定义为聚集索引，将 pkId 定义为非聚集索引。这改变了执行时间，如下所示:

• 连接语句:-1 秒
• 多选语句:-5 秒

我再次检查了索引并更改了包含的列，现在它们具有执行时间:

• 连接语句:4 秒
• 多选语句:6 秒

“简单”问题

有人有减少执行时间的建议吗？

Answer 1

我将逻辑表述为:

我将摆脱第一个子查询，只在users表上做必要的工作:

SELECT *
FROM docs JOIN
     users
     ON users.userID = doc.userId AND softId LIKE '...'  LEFT JOIN
     . . .

如果您无论如何都要执行 JOIN，则 IN 中的逻辑是不必要的。

注意:这可能没有多大帮助，因为您的查询似乎返回了大量数据，无论是在列还是行中。