where子句中的动态可选参数

xn1cxnb4  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(427)

考虑所有参数都是可选的sql查询

SELECT ...
FROM Table
WHERE
 (@Col1 IS NULL OR Col1 = @Col1)
AND
 (@Col2 IS NULL OR Col2 = @Col2)
...
 (@ColN IS NULL OR ColN = @ColN)

大约有8个参数,但将来可能会更多。
代码审阅者要求永远不要编写这样的where构造(检查null或相等),而是使用动态sql(没有给出原因)。
在我看来,它看起来可读性和明确。我还没有运行一个执行计划,但理论上dbms会发现任何空参数,并且不会考虑括号中的第二项。因此,我希望只比较指定的参数。
有人能支持或反驳为什么这是坏的吗?
索引。如果搜索参数可以以任何组合到达,我们甚至可以索引这样的查询(以及如何索引)?

mqxuamgl

mqxuamgl1#

这很复杂。使用非动态查询有一些好处:
这是很清楚的。
它更易于调试和维护。
它在创建存储过程时进行验证。
如果代码在存储过程中,SQLServer将为您维护依赖关系。
从编码的Angular 来看,我认为编译时验证是一个巨大的胜利。它可以防止意外的运行时失败。
在这种情况下,动态查询有一个优点:每次执行时可能都会重新编译它。
什么时候会有不同?如果在所使用的每一列上都有索引,那么重新编译总是有帮助的。
但是,您可以使用 option (recompile) . 这将基于当前参数值重新编译代码。这可能是你想要做的最好的选择。

qyzbxkaa

qyzbxkaa2#

我倾向于同意你的评论。原因如下:
您所面临的风险是,SQLServer将在第一次执行时编译此查询一次。它将根据当时提供的参数值进行基数估计,并以此为基础制定执行计划。此执行计划将在缓存中保留一段时间,而不管后续运行中实际发生了什么。这会导致某些参数值的执行计划非常糟糕。
正如林诺夫先生所建议的(他真的很了解自己的情况),你可以用 option(recompile) . 但是,这意味着SQLServer需要在每次执行时重新编译此查询。如果经常运行这些查询,可能会导致SQLServer花费大量额外的时间重新编译。
此外,我对这种类型的查询的经验是,用户往往会比其他人更频繁地提供某些列。如果动态生成查询,SQLServer可以缓存公共排列的计划,并保存额外的重新编译工作。从dba的Angular 来看,这些索引现在也是计划缓存中的独立条目,您可以通过检查这些条目来了解随着时间的推移,哪些索引对支持此搜索功能更有价值。
当然,知道这是否会是一场胜利意味着要了解您的数据、系统和用户。你必须做的工作是返回并检查用户是否真的在同一组列上进行搜索,或者它是否更随机分布。
我可能会做一些不同的事情,我倾向于在客户机代码中构建动态sql,这听起来有点像您希望将其作为存储过程的一部分来实现。
例如,使用类似c的伪代码:

sql = "SELECT ... FROM... WHERE 1=1";

if (txtCol1.Text != "")
{
   sql += " AND Col1 = @Col1"
   cmd.Parameters.AddWithValue("@Col1", txtCol1.Text);
}
if (txtCol1.Text != "")
{
    sql += " AND Col2 = @Col2"
    cmd.Parameters.AddWithValue("@Col2", txtCol2.Text);
}
//...
// Note this is just pseudocode. I'm not a fan of AddWithValue() in actual practice.

cmd.ExecuteReader();

我知道 1=1 这看起来很奇怪,但它并没有伤害SQLServer,而且这是一种确保语法仍然有效的简单方法,不管哪个条件(如果有的话)是第一个保存值的条件。

相关问题