主页 / dba / 问题 / 279210
Accepted
Newbie-DBA
Asked: 2020-11-05 14:41:11 +0800 CST

我如何通过更改查询或添加索引来调整以下 proc

  • 772

我遇到了一个运行速度非常慢的 proc 大约一个小时的问题：

请原谅，因为我必须从系统中写出整个代码才能粘贴到这里。当我复制该信息时，我将使用有关表架构和索引的更多详细信息来编辑问题：

SP 如下所示

CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable 大约有 800 M 行，并在 id 上聚集为 PK，在 userid（第一个键）上聚集为 NC，tabletypeid（第二个键）

anotherbigtable 大约是。300 M 行

@parameter2大多数时候接收为 NULL 而@parameter1 变化

缓存计划如下所示

在此处输入图像描述

sql-server query-performance

2 个回答

  1. Best Answer

    我要做的第一件事是避免在繁重的 SQL 操作中使用表变量。您应该改用临时表。最初或将表变量 @table1 选择到临时表中，然后在更新语句中使用该临时表。

    表变量在历史上被认为是瓶颈，因为它们没有维护统计数据，即使微软在 SQL 2014 中对它们进行了改进，它们仍然表现不佳。这是一篇关于他们的好 Brent Ozar 文章：https ://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

    您可能还受益于在“anotherbg table”上使用强制搜索提示。但首先从上述更改开始。

    供参考：http ://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1

    以及关于表提示的 MS Docs：https ://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

    通常，查询提示只能由有经验的人在非常特殊的情况下使用，但我发现对于较大的表，有时 SQL 引擎会在索引搜索确实是更好的选择时尝试进行索引扫描。（更新“anotherbg table”上的表统计信息也可能更自然地解决您的问题。）

    所以这是情境性的，您需要进行测试，但强制搜索索引提示可能会有所帮助。

    这是带有索引提示的更新查询的样子：

    UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2
    • 1

  2. 我同意@JD 关于临时表变量的说法。因为在 SQL Server 2019 版本之前，查询引擎认为临时表变量的估计只返回一行。

    否则，我认为您也应该离开Clustered Index Scanon anotherbigtable。因为它花费了98%

    我不知道你的索引，但我推荐下面的索引。

    CREATE INDEX paydate_payid_inc ON anotherbigtable (paydate,payid) INCLUDE (id);

    此外，没有太大必要，但我不喜欢它。

    SET @parameter2 = Getdate()
    
SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

    请试试这个。

    SET @parameter2 = CONVERT(VARCHAR, GETDATE(), 101)

    顺便说一句，您可以与pasttheplan共享您的执行计划，这样我们可以看到您查询的很多信息（估计、成本等），我们可以写出更强大的答案。

    • 0

相关问题