时间戳

首页 > 解决方案 > 升级到 COMPATIBILITY_LEVEL 120 时 SQL 查询慢

sql-server - 升级到 COMPATIBILITY_LEVEL 120 时 SQL 查询慢

问题描述

我们已经在 COMPATIBILITY_LEVEL < 120 中运行数据库有一段时间了，因为我们无法确定某些查询运行速度非常慢的原因。不是我觉得这是解决 covid-19 无聊问题的完美方法，因此我尝试解决新的（嗯……不再那么新）CE 的问题。

所以，我有一个相当简单的查询，涉及 3 个表和 2 个表值参数

declare @spIDs as table (id int not null primary key);
INSERT INTO @spIDs values(1),(2); -- 169 in my sql script

DECLARE @subscriptionProductGroupMapping AS table
( 
    subscriptionProductID int not null,
    groupID int not null,
    primary key(subscriptionProductID, groupID)
);
INSERT INTO @subscriptionProductGroupMapping (subscriptionProductID, groupID) 
VALUES(101,101); -- 168 in my script

SELECT
    [dbo].[User].[userID]
FROM
    [dbo].[User]
    LEFT JOIN
    (
        SELECT DISTINCT [UserValidThrough].userID 
        FROM 
            [dbo].[UserValidThrough] 
            INNER JOIN @spIDs spIDs on(spIDs.id = [dbo].[UserValidThrough].subscriptionProductID)
    ) AS [uvt] ON [uvt].[userID] = [User].userID
    INNER JOIN 
    (
        SELECT DISTINCT userID
        FROM 
        GroupMembership
        INNER JOIN
        @subscriptionProductGroupMapping as SPIAndGroup ON(GroupMembership.groupID = SPIAndGroup.groupID)
    ) gms ON(gms.userID = [User].userID) 
WHERE
    [User].permissionType NOT IN(8, 16, 32, 64, 128) AND
    [User].deleteDate IS NULL AND
    [User].userTypeID IN(@userTypeID_0) AND
    [uvt].[userID] IS NULL

以兼容级别 110 运行此查询时，查询速度快如闪电，不到 0.3 秒。当更改为兼容级别 120 时，查询大约需要 8-9 秒才能执行！:(

在调查实际执行计划时，我发现有一个聚集索引查找分配了大约 7 秒，因此我专注于那部分

实际执行计划 慢聚集索引查找的解释

我试图将此查询分解为另一个表值参数，然后查询又很快了。然而，这意味着我必须在很多地方重写我的应用程序，我真的很想知道为什么这很慢以及如何处理它。

任何人都可以阐明这个问题吗？

编辑 2020-04-15 12:57 CET 这是一个重现简化场景的调试脚本： 1. 创建调试表和调试数据

create table temp_User 
(
    userID int not null,
    -- Additional columns ommited for readability
    primary key (useriD)
);

create table temp_UserValidThrough 
(
    userID int not null,
    subscriptionProductID int not null,
    -- Additional columns ommited for readability
    primary key(userID, subscriptionProductID)
);

create table temp_GroupMembership
(
    userID int not null,
    groupID int not null,
    primary key(userID, groupID)
);


CREATE NONCLUSTERED INDEX temp_GroupMembership_GroupIDWithUserID 
ON [dbo].[temp_GroupMembership] ([groupID])
INCLUDE ([userID])

-- populate User
-- populate UserValidThrough
-- populate GroupMembership
declare @noUsers as int = 120000;
declare @noGroups as int = 400;

SET NOCOUNT ON;

declare @n as int = 0;
while @n < @noUsers
begin
    insert into temp_User values(@n);

    declare @rand as int = rand() * @noGroups;
    insert into temp_UserValidThrough VALUES(@n, @rand);
    insert into temp_GroupMembership VALUES(@n, @rand); 

    SET @n = @n + 1;
end;
  1. 可以使用不同的 COMPATIBILITY_LEVEL 设置执行此查询，并且性能会有很大差异。
DECLARE @userTypeID_0 AS Int;
SET @userTypeID_0 = '1';

declare @spIDs as table (id int not null primary key);
insert into @spIDs
select distinct groupID from temp_GroupMembership;

DECLARE @subscriptionProductGroupMapping AS table
( 
    subscriptionProductID int not null,
    groupID int not null,
    primary key(subscriptionProductID, groupID)
);
insert into @subscriptionProductGroupMapping
SELECT
    T.groupID as subscriptionProductID,
    T.groupID
FROM
    (select distinct groupID from temp_GroupMembership) AS T;


SELECT
    [User].[userID]
FROM
    [dbo].[temp_User] AS [User]
    --LEFT JOIN
    --(
    --  SELECT DISTINCT [UserValidThrough].userID 
    --  FROM 
    --      [dbo].[UserValidThrough] 
    --      INNER JOIN @spIDs spIDs on(spIDs.id = [dbo].[UserValidThrough].subscriptionProductID)
    --) AS [uvt] ON [uvt].[userID] = [User].userID
    INNER JOIN 
    (
        SELECT DISTINCT userID
        FROM 
        temp_GroupMembership as GroupMembership
        INNER JOIN
        @subscriptionProductGroupMapping as SPIAndGroup ON(GroupMembership.groupID = SPIAndGroup.groupID)
    ) gms ON(gms.userID = [User].userID) 
WHERE
    --[User].permissionType NOT IN(8, 16, 32, 64, 128) AND
    --[User].deleteDate IS NULL AND
    --[User].userTypeID IN(@userTypeID_0) AND
    NOT EXISTS 
    (
        SELECT DISTINCT [UserValidThrough].userID 
        FROM 
            [dbo].[UserValidThrough] 
            --INNER JOIN @spIDs spIDs on(spIDs.id = [dbo].[UserValidThrough].subscriptionProductID)
        WHERE
            [UserValidThrough].userID = [User].userID
            AND
            [UserValidThrough].subscriptionProductID IN(SELECT id from @spIDs)
    )
    --[uvt].[userID] IS NULL 

declare @lvl as varchar (10) = (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME());

print 'COMPATIBILITY_LEVEL: ' + @lvl
-- COMPATIBILITY_LEVEL 110 time: average 242 ms
-- COMPATIBILITY_LEVEL 120 time: average 40 s! (200 times slower!)

标签： sql-serverquery-performancecompatibility-level

解决方案

尝试这个：

CREATE TABLE #spIDs 
(
    id int not null primary key
);

CREATE TABLE #subscriptionProductGroupMapping
( 
    subscriptionProductID int not null,
    groupID int not null,
    primary key(subscriptionProductID, groupID)
);

CREATE TABLE #Users
(
    UserID int not null primary key
);

INSERT INTO #subscriptionProductGroupMapping (subscriptionProductID, groupID) 
VALUES(101,101); -- 168 in my script

INSERT INTO #spIDs values(1),(2); -- 169 in my sql script

INSERT INTO #Users (UserID)
SELECT DISTINCT userID
FROM GroupMembership
INNER JOIN #subscriptionProductGroupMapping as SPIAndGroup
    ON(GroupMembership.groupID = SPIAndGroup.groupID)

SELECT
    [dbo].[User].[userID]
FROM
    [dbo].[User]
    INNER JOIN #Users gms 
        ON gms.userID = [User].userID
    LEFT JOIN
    (
        SELECT DISTINCT [UserValidThrough].userID 
        FROM [dbo].[UserValidThrough] 
        INNER JOIN #spIDs spIDs 
            on spIDs.id = [dbo].[UserValidThrough].subscriptionProductID
    ) AS [uvt] ON [uvt].[userID] = [User].userID
WHERE
    [User].permissionType NOT IN(8, 16, 32, 64, 128) AND
    [User].deleteDate IS NULL AND
    [User].userTypeID IN(@userTypeID_0) AND
    [uvt].[userID] IS NULL

推荐阅读