SQL Server 2012 在 TempDb 中使用排序创建索引 - 获得 False？

Question

lifeisajourney

Asked: 2022-10-25 13:55:07 +0800 CST2022-10-25 13:55:07 +0800 CST 2022-10-25 13:55:07 +0800 CST

在包含某些列的集群键上创建非聚集索引

772

我有 3 个表：
test_productInfo：包含有关产品的信息
test_productCreator：包含我们系统中的所有用户
test_productOwner：包含有关谁是产品所有者的信息。test_productInfo 和 test_productCreator 之间的交叉引用表。

我的存储过程需要一个 productCreatorId 或多个 productCreatorIds 和一个排序键，并返回 test_productInfo（所有列）中按传入的排序键排序的前 1000 行。不幸的是，我必须选择所有内容。

我已经粘贴了我们在下面的当前查询，这是它的执行计划： https ://www.brentozar.com/pastetheplan/?id=SyKgEEVNj

我正在取回 20 个用户的数据。当前查询的排序溢出到 tempdb。

我在 productId 上添加了一个新的非聚集索引，它是聚集键并包含所有排序键。我将查询修改为仅在排序时选择 productId，以便可以使用这个新索引并将结果存储在临时表中。在随后的查询中，我添加了所有需要的列并再次进行排序。This query works well for users who have more than 1000 rows coz' I'm not sorting more than 1000 rows when all the columns are being selected. 对于少于 1000 行的用户，我两次访问 productInfo 表，但我没有观察到当前查询和修改后的查询之间的时间差异很大。这是修改后查询的执行计划：https ://www.brentozar.com/pastetheplan/?id=SyA_SVEVs

使用新的修改后的查询，我正在尝试减少排序溢出，并且我看到了一些改进，尤其是当有超过 1000 行时。

在包含某些列的集群键上创建非集群索引是否有意义？有没有更好的方法来重写我当前的查询？

--drop table test_productInfo
--drop table test_productOwner
--drop table test_productCreator

Create Table test_productInfo
(
productId uniqueIdentifier Primary Key,
productName varchar(100),
productRegion varchar(100),
productCreatedDt Datetime,
productUpdatedDt  Datetime,
productStatus varchar(100),
isProductsold bit,
productLineNumber Int,
productLineId uniqueIdentifier,
productAddress varchar(100),
productAddress2 varchar(100),
productCity varchar(100),
productState varchar(100),
productCountry varchar(100),
productZip  Varchar(100),
productLatitude varchar(100),
productLongitude varchar(100)
)

Create Table test_productOwner
(
productOwnerId Int Identity(1,1),
productId uniqueIdentifier,
productCreatorId uniqueIdentifier,
Createddate datetime,
modifieddate datetime
)

Create Table test_productCreator 
(
productCreatorId uniqueIdentifier primary key,
productCreatorName varchar(100),
createddate datetime,
modifieddate datetime
)

ALTER TABLE test_productOwner ADD CONSTRAINT PK_test_productOwner PRIMARY KEY NONCLUSTERED(productOwnerId)
create clustered index ix_test_productOwner_productId On dbo.test_productOwner(productId)
create nonclustered index ix_test_productOwner_test_productCreatorId On dbo.test_productOwner(productCreatorId)

/* Insert data */
Declare @i int = 1, @j int = 1
Declare @newIdi uniqueidentifier, @newIdj uniqueidentifier

While @i < 100
Begin
set @newIdi = newid()

Insert Into dbo.test_productCreator values (@newIdi, 'ABC'+convert(varchar(10),@i), getdate(),getdate())

    while @j <= 2300
    Begin
        set @newIdj = newId()
        Insert Into dbo.test_productInfo values (@newIdj, 'ProductName'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Region1',getdate(),getdate(),'completed', 1, @i,newId()
        ,'Address1'+convert(varchar(10),@i)+convert(varchar(10),@j), 'Address2'+convert(varchar(10),@i)+convert(varchar(10),@j), 'los angeles','ca','USA','22231','26.45','33.23')
        
        insert into dbo.test_productOwner values (@newIdj, @newIdi, getdate(), getdate())
        
        set @j = @j + 1
        
    End
set @i = @i + 1
set @j = 1
End

**CURRENT Query:**


set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize   int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)

insert into @prodCreator 
select top 10 productCreatorId
from dbo.test_productCreator

Select p_pi.*
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY

**MODIFIED Query:**
create nonclustered index idx_non_productInfo on dbo.productInfo(productId) include(productUpdatedDt, productCreatedDt, productName)

set statistics io on
Declare @sort varchar(100) = 'PRODMODIFIED'
Declare @PageNumber int = 1, @PageSize   int = 1000
Declare @prodCreator Table(productCreatorId Uniqueidentifier)
Create Table #tmpProduct (productId uniqueidentifier)

insert into @prodCreator 
select top 10 productCreatorId
from dbo.test_productCreator

Insert Into #tmpProduct
Select p_pi.productId
from test_productInfo p_pi
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end
      OFFSET @PageSize * (@PageNumber - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY


Select p_pi.*
from #tmpProduct tmp_pi
join test_productInfo p_pi
on tmp_pi.productId = p_pi.productId
join test_productowner p_po
on p_pi.productId = p_po.productId
join @prodcreator p_pc
on p_po.productCreatorId = p_pc.productCreatorId
order by case when @sort = 'PRODMODIFIED' then p_pi.productUpdatedDt
              when @sort = 'PRODCREATED' then p_pi.productCreatedDt
              when @sort = 'PRODNAME' then p_pi.productName
        end

If object_id('tempdb..#tmpProduct') is not null
begin
drop table #tmpProduct
end

1 个回答

Voted

Paul White · Answer 1 · 2022-10-26T02:58:01+08:00

排序溢出主要是因为表变量不支持统计信息并且默认为一行的基数估计。您可以使用重新编译提示或跟踪标志 2453解决 SQL Server 2012 上的基数问题，但您仍然不会获得统计信息，这对于任何依赖于数据分布的操作（如连接和分组）都很重要。

有很多方法可以改进您的查询，包括使用动态 SQL，但对于涉及的相对较小的表，我可能不会费心编写那么多代码。下面的解决方案将行从表变量复制到临时表，该表支持统计信息：

DECLARE @ProdCreator table (productCreatorId uniqueidentifier PRIMARY KEY);

INSERT @ProdCreator
    (productCreatorId)
SELECT TOP (10) 
    TPC.productCreatorId
FROM dbo.test_productCreator AS TPC;

CREATE TABLE #ProdCreator 
(
    productCreatorId uniqueidentifier PRIMARY KEY
);

INSERT #ProdCreator 
    (productCreatorId)
SELECT 
    PC.productCreatorId 
FROM @ProdCreator AS PC;

DECLARE 
    @Sort varchar(100) = 'PRODMODIFIED',
    @PageNumber integer = 1,
    @PageSize integer = 1000;

-- This query now benefits from automatic statistics
-- on the #ProdCreator table
SELECT TPI.*
FROM
(
    SELECT
        TPI.productId, 
        rn = ROW_NUMBER() OVER (
            ORDER BY
                CASE @Sort
                    WHEN 'PRODMODIFIED' 
                        THEN CONVERT(sql_variant, TPI.productUpdatedDt)
                    WHEN 'PRODCREATED' 
                        THEN CONVERT(sql_variant, TPI.productCreatedDt)
                    WHEN 'PRODNAME' 
                        THEN CONVERT(sql_variant, TPI.productName)
                END)
    FROM dbo.test_productInfo AS TPI
    WHERE EXISTS 
    (
        SELECT * 
        FROM #ProdCreator AS PC
        JOIN dbo.test_productOwner AS TPO 
            ON TPO.productCreatorId = PC.productCreatorId
        WHERE 
            TPO.productId = TPI.productId
    )
    ORDER BY rn
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH FIRST @PageSize ROWS ONLY
) AS ThePage
JOIN dbo.test_productInfo AS TPI
    ON TPI.productId = ThePage.productId
ORDER BY
    ThePage.rn
OPTION (RECOMPILE, MAXDOP 4);

示例计划：

它通过转换为sql_variant避免了阻止您的原件使用 PRODNAME 排序顺序的转换错误。重新编译提示启用参数嵌入优化，通过在执行开始前对其进行一次评估来最小化 case 表达式的影响。

MAXDOP 提示不是必需的，但如果您自然地获得并行计划并且没有设置最大并行度，则可能需要考虑这一点。并行性在此计划中有一个额外的好处，因为它允许通过哈希构建位图进行早期半连接减少。

对信息表的额外连接是仅获取单页行的所有列。它避免了对最终不需要的行的额外列进行排序。它可能会或可能不会增加巨大的价值；如果您愿意，请忽略它。问题中提出的索引将使初始连接更有效率，但对于这种大小的表可能不值得。这真的是你要做出的决定。

由于sql_variant转换，此查询确实具有“过多”内存授权。如果这在实际应用程序中对您很重要，那么您将不得不使用动态 SQL。在这个例子中，拨款并没有那么大，我特别担心。

动态 SQL 示例进行比较（仍然使用临时表）：

DECLARE 
    @Sort varchar(100) = 'PRODMODIFIED',
    @PageNumber integer = 1,
    @PageSize integer = 1000,
    @SQL nvarchar(max);

SET @SQL = 
    REPLACE
    (
        N'
        SELECT TPI.*
        FROM
        (
            SELECT
                TPI.productId, 
                rn = ROW_NUMBER() OVER (ORDER BY ##OrderBy##)
            FROM dbo.test_productInfo AS TPI
            WHERE EXISTS 
            (
                SELECT * 
                FROM #ProdCreator AS PC
                JOIN dbo.test_productOwner AS TPO 
                    ON TPO.productCreatorId = PC.productCreatorId
                WHERE 
                    TPO.productId = TPI.productId
            )
            ORDER BY rn
                OFFSET (@PageNumber - 1) * @PageSize ROWS
                FETCH FIRST @PageSize ROWS ONLY
        ) AS ThePage
        JOIN dbo.test_productInfo AS TPI
            ON TPI.productId = ThePage.productId
        ORDER BY
            ThePage.rn
        OPTION (MAXDOP 4);
        ',
        N'##OrderBy##',
        CASE @Sort
            WHEN 'PRODMODIFIED' THEN N'TPI.productUpdatedDt'
            WHEN 'PRODCREATED' THEN N'TPI.productCreatedDt'
            WHEN 'PRODNAME' THEN N'TPI.productName'
        END
    );

EXECUTE sys.sp_executesql
    @SQL,
    N'@PageNumber integer, @PageSize integer',
    @PageNumber = @PageNumber,
    @PageSize = @PageSize;

在我的机器上，动态版本大约在 80 毫秒内完成。非动态版本的运行时间约为 40 毫秒，但使用更多的内存和并行度。

在包含某些列的集群键上创建非聚集索引

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

在包含某些列的集群键上创建非聚集索引

1 个回答

相关问题