sql - 带/不带参数的性能问题

Question

我遇到一些性能问题。

我有一个大约有 200 万行的表。

CREATE TABLE [dbo].[M8](
    [M8_ID] [int] IDENTITY(1,1) NOT NULL,
    [APPLIC] [char](8) NOT NULL,
    [NIVALERTE] [numeric](1, 0) NOT NULL,
    [LOGDH] [datetime2](7) NULL,
    [USERX] [char](20) NOT NULL,
    [TACHE] [char](3) NOT NULL,
    [PRG] [char](32) NOT NULL,
    [DOS] [numeric](3, 0) NOT NULL,
    [ERRNUM] [numeric](5, 0) NOT NULL,
    [LOGTXT] [char](200) NOT NULL)

我用 C# 和 ADO.NET 阅读它们

在管理工作室 (SQL Server 2008 R2) 中，使用该查询:

SELECT 
    M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, 
    M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT
FROM 
    M8 AS M8 WITH(NOLOCK) 
WHERE 
    ((M8.APPLIC LIKE 'DAV' ) )
ORDER BY 
    M8.LOGDH DESC, M8.M8_ID ASC
OPTION (FAST 1)

大约需要 1 分钟才能获得第一行。

但是，随着

DECLARE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 as char(8) = 'DAV'

SELECT 
   M8.M8_ID, M8.APPLIC, M8.NIVALERTE, M8.LOGDH, M8.USERX, M8.TACHE, 
   M8.PRG, M8.DOS, M8.ERRNUM, M8.LOGTXT
FROM 
   M8 AS M8 WITH(NOLOCK) 
WHERE 
   ((M8.APPLIC LIKE @APPLIC_ZOOMAPRESCLE_ZOOM_LIKE_APPLIC_WHERE_0 ) )
ORDER BY 
   M8.LOGDH DESC, M8.M8_ID ASC
OPTION(FAST 1)

4 秒后我得到了第一行。

PS:我知道，我没有%这样的。

编辑:这是执行计划 https://www.dropbox.com/sh/jgai5f9txbs84x6/EP5_hj8DNv

Answer 1

您的表有 1,517,820 行。其中近三分之一 (476,672) 包含值 DAV(或更准确地说，值 DAV     ，因为它是 CHAR(8) 数据类型，因此用尾随空格填充。

在 LIKE 比较中，match_expression 中的尾随空格并不重要(尽管它们在模式 本身中很重要)。

因此表达式 WHERE APPLIC LIKE 'DAV' 实际上匹配 476,672 行。然而，这两个执行计划的估计都与此相差甚远。尽管更快的计划(带有变量)要接近三个数量级。

+-----------------------+-----------+-----------+
|                       | Slow Plan | Fast Plan |
+-----------------------+-----------+-----------+
| Estimated # Rows      | 32        | 47,343    |
| Memory Grant          | 1 MB      | 333 MB    |
| Degree of Parallelism | 1         | 4         |
+-----------------------+-----------+-----------+

对于带有变量的计划，因为 SQL Server 不执行变量嗅探(没有例如 OPTION (RECOMPILE) 提示)，它会依靠猜测有多少行将与谓词匹配并得出据估计，该表中大约有 3.1% 的人符合资格。

快速计划

具有字面值的计划应该有更好的估计。您提供的 DBCC SHOW_STATISTICS 输出屏幕截图(添加另外一百万行后)显示 DAV 肯定在那里

不幸的是，尽管列值中的尾随空格对于查询结果并不重要，但它们的存在确实会扰乱基数估计(Reported as a bug here，目前声明将在下一版本中修复)。由于这个问题，它估计只会返回少数行，并提出以下计划。

慢计划

由于基数估计较差，除了执行 50 万个键查找外，内存授予可能远远不足以满足要排序的数据大小，从而导致溢出到 tempdb。

如果您可以更改查询或表架构，您可以考虑多种解决方法。

• 使用 = 代替 LIKE
• 将 WHERE 子句更改为 LIKE CAST('DAV' AS CHAR(8))
• 将列数据类型更改为 VARCHAR(8)(并确保修剪所有存储的值)。
• 删除当前正在查找的索引 (Index_A)。您还没有提供它的定义，但如果它是一个列上的单列索引，并且几乎没有不同的值，那么它的存在可能更多的是一种障碍而不是帮助(取决于您的查询工作负载))
• 添加包含键列 APPLIC 的覆盖索引(可能还有 LOGDH DESC、M8_ID ASC 以避免排序)以及其他引用列作为 INCLUDED.