c# - 如果十进制参数的精度错误，查询速度会变慢

Question

我正在使用 SqlParameter 将值传递给我的查询

假设我的表中有一个字段 DOS numeric(3,0)。

我的查询类似于 SELECT * FROM ENT WHERE ENT.DOS = @DOS。

问题是我在运行时不知道 ENT.DOS 字段的类型(我的引擎很复杂...)，所以我在创建 SqlParameter 时没有指定任何精度或小数位数。

例如，如果我想要 DOS = 1，则 Precision 将设置为 1，Scale 将设置为 0。

它有效，这不是重点。问题是:它很慢。

如果我指定真实的 Precision 和 Scale 值，查询运行得很快。

我可以在 Management Studio 中重现问题:

在数据库中:DOS = 数字(3,0)TICOD = 字符 (1)PICOD = 数字 (8,0)皮诺 = 数字 (8,0)ENT_ID = 整数

慢速版本:

DECLARE @DOS as numeric(1,0) = 1
DECLARE @TICOD as varchar(1) = 'C'
DECLARE @PICOD as numeric(1,0) = 3
DECLARE @PINO as numeric(8,0) = 99999999
DECLARE @ENT_ID as numeric(8,0) = 99999999


SELECT TOP 1 * 
FROM ENT AS ENT WITH(NOLOCK) 
WHERE
(CE4 = '1') and (
( DOS = @DOS and TICOD = @TICOD and PICOD = @PICOD and PINO < @PINO ) or
( DOS = @DOS and TICOD = @TICOD and PICOD < @PICOD ) or
( DOS = @DOS and TICOD < @TICOD ) or
( DOS< @DOS )
)
order by DOS desc,TICOD desc,PICOD desc,PINO desc,ENT_ID desc
OPTION (FAST 1)

快速版本(具有正确的参数类型):

declare @DOS as numeric(3,0) = 1
declare @TICOD as char(1) = 'C'
declare @PICOD as numeric(5,0) = 3
declare @PINO as numeric(8,0) = 99999999
declare @ENT_ID as int = 99999999


SELECT TOP 1 * 
FROM ENT AS ENT WITH(NOLOCK) 
WHERE
(CE4 = '1') and (
(DOS = @DOS and TICOD = @TICOD and PICOD = @PICOD and PINO = @PINO and ENT_ID < @ENT_ID ) or
( DOS = @DOS and TICOD = @TICOD and PICOD = @PICOD and PINO < @PINO ) or
( DOS = @DOS and TICOD = @TICOD and PICOD < @PICOD ) or
( DOS = @DOS and TICOD < @TICOD ) or
( DOS< @DOS )
)
order by DOS desc,TICOD desc,PICOD desc,PINO desc,ENT_ID desc
OPTION (FAST 1)

所以我的问题是:有没有办法在不指定 Precision 和 Scale 的情况下为 decimal 类型声明 SqlParameter 并且有一个快速运行的查询？

更新 1:

执行计划:

正确的数据类型: https://dl.dropboxusercontent.com/u/14168890/SqlParameter/exec_plan_fast.sqlplan

计划显示寻求谓词

1 Seek Keys1: End: DOS < @DOS, 
2 Seek Keys1: Prefix: DOS = @DOS, End: TICOD < @TICOD, 
3 Seek Keys1: Prefix: DOS, TICOD = @DOS, @TICOD, End: PICOD < @PICOD, 
4 Seek Keys1: Prefix: DOS, TICOD, PICOD = @DOS, @TICOD, @PICOD, End: PINO < @PINO, 
5 Seek Keys1: Prefix: DOS, TICOD, PICOD, PINO = @DOS, @TICOD, @PICOD, @PINO, End: ENT_ID < @ENT_ID

不匹配的数据类型: https://dl.dropboxusercontent.com/u/14168890/SqlParameter/exec_plan_slow.sqlplan

该计划显示了谓词移动到过滤器中的扫描。

不匹配的数据类型(FORCESEEK): https://dl.dropboxusercontent.com/u/14168890/SqlParameter/exec_plan_forceseek.sqlplan

索引 GTFENT_G 是:DOS,TICOD,PICOD,PINO,ENT_ID

此外，我还测试了在整数类型中声明实际上是整数的数值，慢的变成快的。

Answer 1

在更快的版本中，五个寻道操作在计划中全部合并为一个寻道运算符。

索引顺序为DOS asc,TICOD asc,PICOD asc,PINO asc,ENT_ID asc。

因为您想要 TOP 1 ... ORDER BY DOS desc,TICOD desc,PICOD desc,PINO desc,ENT_ID desc 它只需要按降序键顺序查找 5 个不同的索引范围，并且一旦找到第一个匹配行就停止。

在数据类型不匹配的较慢版本中，虽然所有谓词仍然可以单独查找，但似乎无法将它们组合到这个单一的查找运算符中。带有 FORCESEEK 提示的计划显示了由单独的查找运算符执行的所有单个范围查找。

此外，一旦找到 TOP 1，它不再利用索引顺序来避免排序或短路。查找运算符都在阻塞排序运算符下，这意味着它们都是整体评估的。

根据列定义简单地使用正确的数据类型显然更好。如果这真的是不可能的，那么一种解决方法似乎是将它们声明为numeric(38,S)。

S 是什么似乎无关紧要。因此，这可以仅基于数据中的小数位数。关键似乎是确保变量的数据类型至少与列的数据类型具有相同的精度。

将 @DOS 和 @PICOD 声明为 numeric(38,37) 或 numeric(38,0) 两者都致力于针对以下表格结构提供更快的计划形状。

CREATE TABLE [dbo].[ENT](
    [FOO] [int] NOT NULL PRIMARY KEY,
    [CE4] INT,
    [DOS] [numeric](3,0) NOT NULL,
    [TICOD] [char](1) NOT NULL,
    [PICOD] [numeric](8, 0) NOT NULL,
    [PINO] [numeric](8, 0) NOT NULL,
    [ENT_ID] [int] NOT NULL,
 UNIQUE NONCLUSTERED ([DOS] ASC, [TICOD] ASC, [PICOD] ASC, [PINO] ASC, [ENT_ID] ASC)
)

查询

DECLARE @DOS as numeric(38,37) = 1
DECLARE @TICOD as varchar(1) = 'C'
DECLARE @PICOD as numeric(38,37) = 3
DECLARE @PINO as numeric(8,0) = 99999999
DECLARE @ENT_ID as numeric(8,0) = 99999999


SELECT TOP 1 * 
FROM ENT AS ENT WITH(NOLOCK, FORCESEEK) 
WHERE
 (
DOS = @DOS and TICOD = @TICOD and PICOD = @PICOD and PINO = @PINO and ENT_ID < @ENT_ID ) or
( DOS = @DOS and TICOD = @TICOD and PICOD = @PICOD and PINO < @PINO ) or
( DOS = @DOS and TICOD = @TICOD and PICOD < @PICOD ) or
( DOS = @DOS and TICOD < @TICOD ) or
( DOS< @DOS )

order by DOS desc,TICOD desc,PICOD desc,PINO desc,ENT_ID desc