代码之家 › 专栏 › 技术社区 › Pravin Satav

静态与动态SQL

plsql oracle sql

Pravin Satav · 技术社区 · 14 年前

在我的数据库的几个地方,开发人员使用了动态SQL而不是静态SQL。他们说这是为了提高性能。有人能告诉我动态SQL是否真的能提高存储过程或PLSQL块的性能吗?

哪个会执行得更快,为什么?
1。

begin  
    execute immediate 'delete from X';  
end;

2。

begin  
    delete from X;  
end;

2 回复 | 直到 14 年前

APC 14 年前

您的示例代码非常简单,几乎没有什么不同,但是在这种情况下,静态版本的执行效果很可能更好。

使用动态SQL来提高性能的主要原因是,当SQL语句可能会发生重大变化时——即,您可能能够在运行时根据系统的状态向WHERE子句添加额外的代码(如果输入了地址,则通过对地址的子查询进行限制等)。

另一个原因是有时使用绑定变量作为参数可能会适得其反。

例如,如果您有类似于状态字段的内容,其中的数据不是均匀分布的(而是索引的)。

当95%的数据被“处理”时,考虑以下3条语句

   SELECT col FROM table 
   WHERE status = 'U'-- unprocessed
   AND company = :company

   SELECT col FROM table 
   WHERE status = 'P' -- processed
   AND company = :company

   SELECT col FROM table
   WHERE status = :status
   AND company = :company

在最终版本中,Oracle将选择通用的解释计划。在第一个版本中,它可能会决定最好的计划是从状态索引开始(知道“U”处理的条目只是总数的一小部分)。

您可以通过不同的静态语句实现这一点,但是如果您有更复杂的语句,这些语句只会更改几个字符,那么动态SQL可能是更好的选择。

下侧

同一个动态SQL语句的每次重复都会产生一个软解析,与静态语句相比,这是一个很小的开销,但仍然是一个开销。

每个新的SQL语句(动态或静态)也会在SGA(共享内存)上产生一个锁,并可能导致将“旧”语句推出。

一个糟糕但常见的系统设计是让某人使用动态SQL来生成只随键变化的简单选择,即。

SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7

单个语句将很快,但系统的整体性能将恶化,因为它正在杀死共享资源。

而且,在编译时用动态SQL捕获错误要困难得多。如果使用pl/sql,这将丢弃一个良好的编译时检查。即使在使用JDBC之类的东西时(将所有数据库代码移入字符串中——好主意!)您可以让预解析器验证JDBC内容。动态SQL=仅运行时测试。

开销

execute immediate的开销很小(以千分之一秒为单位),但是,如果它在一个循环中/在一个方法上(每个对象调用一次/etc),它就可以加起来。我曾经用生成的静态SQL替换动态SQL,从而提高了10倍的速度。但是,这使代码变得复杂,并且只因为我们需要速度而完成。

Colin Pickard 14 年前

不幸的是,这确实因个案而异。

对于您给出的示例,可能没有可测量的差异。但对于更复杂的示例,您可能希望测试自己的代码。

评论中给出的link@dumbcoder有一些很好的经验法则,在很大程度上也适用于Oracle。您可以使用类似这样的东西来帮助您做出决定,但是没有简单的规则像“动态比静态更快”。