代码之家 › 专栏 › 技术社区 › Julio César

防止在SQL Server中重写查询

optimization sql-server

Julio César · 技术社区 · 15 年前

我有一个列类型代码varchar(20),它的值类似于“1”、“2”、“3”、“foo”、“bar”。我需要找到小于参数值的最大整数。像这样:

select max(TypeCode) TypeCode
    from table1 a
    left join table2 b on b.table1id = a.id
        and b.TypeCode not in ('FOO', 'BAR')
    where b.TypeCode < @MaxType

这在大多数情况下都有效,但在某些查询中,SQL Server决定将其转换为类似的类型(根据查询计划)。

select max(TypeCode) TypeCode
    from table1 a
    left join table2 b on b.table1id = a.id
        and b.TypeCode < @MaxType
        and b.TypeCode not in ('FOO', 'BAR')

该查询显然会产生以下错误:

Conversion failed when converting the varchar value 'FOO' to data type int.

我尝试创建一个不带“foo”和“bar”值的Table2视图,并连接视图,但查询计划仍然相同。

您知道防止优化器更改查询的方法吗?

PS:我知道表的设计不是最好的,但这是一个遗留数据库,我不能更改它。

2 回复 | 直到 15 年前

Aaronaught 15 年前

这里真正的问题是,您在同一个查询中组合了字符和整数语义。

我可以想到三种可能的解决方案:

改变 @MaxType 参数为 varchar(9) . 如果您只比较单个数字,字母顺序是可以的。否则,这就行不通了。
使用A CASE 或 ISNULL(NULLIF(...)) 在中构造 WHERE 声明。这将有效,但它不可解析,并将导致优化器忽略类型代码上的任何索引。不太好。
创建了一个持久的、计算的、可以为空的整数列(即 TypeCodeID )并单独索引。放 案例 作为列的表达式。这将占用一些额外的数据/索引空间,但如果您希望获得良好的性能,这是最好的方法。那就不要写了 NOT IN ('Foo', 'Bar') 你可以写第一个条件( TypeCode < @MaxType )因为排在 Foo 和 Bar 在 TypeCode 列将具有 NULL 在新的 类型分类 列。

我想还有第四个答案是改变你的设计,如果可能的话,这是最好的主意。如果列可以包含字符数据,那么您就不应该尝试执行数值比较。我强烈怀疑此列中存在字符数据,因为它来自用户,未正确验证/清理,因此它的垃圾值如下 N/A 或 Unknown 在那里。如果是这样的话,您的数据库确实应该加强数据完整性,这就是DBMS的作用。我知道你说你“不能改变它”,但我认为如果我不建议反对这种危险的做法,这个答案就不完整。

mwigdahl 15 年前

没有办法关闭优化器,而且您可能根本不想这样做。更好的解决方案可能是修改查询。

你可以用一个案例陈述来解决这个问题,比如:

SELECT MAX(TypeCode) TypeCode
FROM table1 a
    LEFT JOIN table2 b ON b.table1id = a.id
WHERE CASE WHEN b.TypeCode IN ('FOO', 'BAR') THEN 99999999999 ELSE CAST(b.TypeCode AS int) END < @MaxType

你需要检查一下它的性能,看看它是否可以接受,但是它应该可以工作。