静态与动态 SQL

发布于 2024-09-15 09:08:51 字数 274 浏览 4 评论 0原文

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

哪个执行速度更快，为什么？
1.

begin  
    execute immediate 'delete from X';  
end;

begin  
    delete from X;  
end;

原文

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?

Which will execute faster and why ?
1.

begin  
    execute immediate 'delete from X';  
end;

begin  
    delete from X;  
end;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

丘比特射中我 2024-09-22 09:08:51

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

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

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

一个例子是，如果您有类似状态字段的内容，其中数据分布不均匀（但已建立索引）。

考虑以下3个语句，当95%的数据

   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将选择通用解释计划。在第一个版本中，它可能会决定最好的计划是从状态索引开始（知道“未处理的条目只占总数的很小一部分”）。

您可以通过不同的静态语句来实现这一点，但如果您有更复杂的语句（仅更改几个字符），动态 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 = 仅运行时测试。

开销

立即执行的开销很小 - 它是千分之一秒 - 但是，如果这是在循环内/在每个对象调用一次的方法上/等等，它可能会增加。我曾经通过用生成的静态 SQL 替换动态 SQL，速度提高了 10 倍。然而，这使代码变得复杂，并且只是因为我们需要速度而这样做。

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

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).

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).

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

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).

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.

Downsides

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.

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

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.

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.

Overheads

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.

回复收藏 0 原文