代码之家 › 专栏 › 技术社区 › KuldipMCA

Sqlserver 2005中动态查询的缺点?

query-optimization stored-procedures tsql sql

KuldipMCA · 技术社区 · 16 年前

我在数据库中对过程使用了许多动态查询,因为我的过滤器没有修复,所以我将@filter作为参数并传入过程。

Declare @query as varchar(8000)
Declare @Filter as varchar(1000)

set @query = 'Select * from Person.Address where 1=1 and ' + @Filter

exec(@query)

类似地,我的过滤器包含表中的任何字段以进行比较。

会不会影响我的表现? 有没有别的方法可以达到这种目的

3 回复 | 直到 16 年前

adrianm 16 年前

对于性能而言,问题仅在于数据库是否可以重用现有的计划。

简单地说,您可以将其视为数据库以sql语句作为键缓存查询计划。一旦您更改了sql语句,它将不在缓存中,并且必须生成新的计划。

所以生成动态语句

"SELECT * FROM table WHERE param = @paramvalue"

在缓存中的机会比

"SELECT * FROM table WHERE param = '" + variable + "'"

您还应该将模式名添加到查询中的tablenames(例如dbo.table)。否则,如果计划由不同的登录名执行,则它将不会被重用。

Alex 16 年前

只要不需要动态地进行其他连接来检查可能重要的参数的值,并且唯一的动态部分是WHERE子句,那么这也可以是一个包含所有可能参数的静态查询。所以你有以下情况:

如果要检查所有可能的值(负/零/零/正/空字符串/等),则需要使用辅助参数,如@ SeimiFiFiTangPyPARAM1,以及@ PARAM1的原始值。

[...]
WHERE 
    (@signifficant_param1=0 or (@param1 is null and field1 is null) or @param1=field1) 
    AND (@signifficant_param2=0 or (@param2 is null and field2 is null) or @param2=field2)
    //etc
[...]

这是我能想象到的最普遍的条款。基本上它会验证 @signifficant_param 价值。如果应该考虑这个参数,它将是1,条件的第一部分将为false,第二部分(参数的验证)将进行。在第二阶段,如果 @param 为空,则查找 field ,并且不能将空与空进行比较,因为它们不相等。然后进行常规非空值匹配的验证。

另一方面,如果 领域 不能为空,也不能为负,您不需要 @重要参数 ,因为您可以制定规则,例如,如果 @参数 为空,则此值不重要(在前一种情况下,您必须搜索所有空值),可以使用以下命令:

[...]
WHERE
    field1=case when @param1 is null then field1 else @param1 end --first way with case statement
    and (@param2 is null or field2=@param2) --second way with boolean logic
[...]

Dave Markle 16 年前

动态查询本身并没有什么问题。但你打算这样做是很可怕的。这意味着您的参数将是@Filter的一部分,而@Filter只是请求SQL注入攻击。这也意味着您的查询计划不太可能被重用,这可能会由于过度的查询重新编译而导致高CPU和低吞吐量。

您需要确保生成的动态SQL是正确参数化的。您还需要确保在使用ADO.NET代码(或可能使用的任何数据访问技术)访问它时,使用SqlParameter(或等效的)对象。