静态与动态SQL [英] Static vs dynamic sql

问题描述

在我的数据库的多个地方，开发人员使用了动态sql而不是静态sql.他们说这是为了提高性能.有人可以告诉我动态sql是否真的可以提高存储过程或plsql块的性能吗?

In my database at several places developers have used dynamic sql instead of static. And they are saying reason for this is to improve the performance. Can someone tell me can if dynamic sql can really increase the performance in stored procedure or plsql block?

执行速度更快，为什么?
1.

Which will execute faster and why ?
1.

begin  
    execute immediate 'delete from X';  
end;

2.

begin  
    delete from X;  
end;

推荐答案

您的示例代码非常简单，几乎没有什么区别，但是在那种情况下，静态版本执行起来可能会更好.

Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.

使用动态SQL来提高性能的主要原因是，当SQL语句发生显着变化时-即，您可以根据系统状态在运行时向WHERE子句添加额外的代码(受系统限制).如果输入了地址，则对地址进行子查询).

The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc).

另一个原因是，有时将Bind变量用作参数可能适得其反.

Another reason is that sometimes using Bind variables as parameters can be counter-productive.

例如，如果您有一个状态字段之类的东西，其中的数据分布不均匀(但已建立索引).

An example is if you have something like a status field, where data is not evenly distributed (but is indexed).

在处理了95％的数据时，请考虑以下3条语句

Consider the following 3 statements, when 95% of the data is 'P'rocessed

   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'处理过的条目只占总数的很小一部分).

In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).

您可以通过不同的静态语句来实现这一点，但是如果您有更复杂的语句(仅需更改几个字符)，则动态SQL可能是一个更好的选择.

You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.

缺点

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

Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.

每个新的sql语句(动态或静态)也会在SGA(共享内存)上产生锁定，并可能导致推出旧"语句.

Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.

一个糟糕但常见的系统设计是，有人使用动态SQL来生成仅随键而异的简单选择，即

A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.

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

单独的语句很快，但是由于它会杀死共享资源，因此整个系统的性能会降低.

The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.

此外-使用动态SQL在编译时捕获错误要困难得多.如果使用PL/SQL，则会浪费大量的编译时间检查.即使使用JDBC之类的东西(将所有数据库代码都移到字符串中-很好！)，您也可以使用预解析器来验证JDBC内容.动态SQL =仅用于运行时测试.

Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.

开销

立即执行的开销很小-大约是千分之一秒-但是，如果这是在循环内/在每个对象调用一次的方法上等，它可以累加起来.我曾经将速度提高了10倍通过用生成的静态SQL替换动态SQL.但是，这使代码复杂化，并且仅仅因为我们需要速度而完成了.

The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.

这篇关于静态与动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！