Oracle PL / SQL - NO_DATA_FOUND异常对存储过程性能有坏处理吗？ [英] Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

问题描述

我正在编写一个需要很多调理的存储过程。通过C＃.NET编码的一般知识，异常可能会影响性能，我一直都在避免在PL / SQL中使用它们。我在这个存储过程中的调理主要围绕是否存在一条记录，我可以通过两种方法之一进行操作：

  SELECT COUNT（*）INTO var WHERE condition; 
 IF var> 0 THEN 
 SELECT NEEDED_FIELD INTO otherVar WHERE condition; 
 .... 
  

-or -

  SELECT NEEDED_FIELD INTO var WHERE condition; 
 EXCEPTION 
 WHEN NO_DATA_FOUND 
 .... 
  

第二个情况对我来说似乎更加优雅，因为那时我可以使用NEEDED_FIELD，在第一种情况下，我不得不在第一个语句中选择条件。较少的代码但是如果存储过程使用COUNT（*）运行得更快，那么我不介意再打多一些来弥补处理速度。

任何提示？我有没有其他的可能性？

编辑
我应该提到这一切已经嵌套在FOR LOOP中。不知道这是否与使用游标有所不同，因为我不认为我可以在FOR LOOP中将光标DECLARE作为选择。

解决方案

我不会使用显式光标来做到这一点。当可以使用隐式游标时，Steve F.不再建议人们使用显式游标。

具有 count（*）的方法是不安全的。如果另一个会话删除符合 count（*）的行之后的条件的行，并且在使用选择...之前的行，代码将抛出一个不能被处理的异常。

原始帖子的第二个版本没有这个问题，通常是首选。

那就是说，使用异常有一个小的开销，如果你100％确定数据不会改变，你可以使用 count（*），但我建议反对。

我在Oracle 10.2.0.1上运行了这些基准测试/ em> 32位Windows 。我只是看着经过的时间。还有其他测试工具可以提供更多的细节（例如锁存计数和使用的内存）。

  SQL> create table t NEEDED_FIELD号，COND号）; 
  

创建表

  SQL> insert into t（NEEDED_FIELD，cond）值（1，0）; 
  

创建1行。

< blockquote>

  declare 
 otherVar number; 
 cnt number; 
 begin 
 for i in 1 .. 50000循环
从t中选择count（*）到cnt，其中cond = 1; 
 
 if（cnt = 1）then 
从t选择NEEDED_FIELD INTO otherVar，其中cond = 1; 
 else 
 otherVar：= 0; 
 end if; 
结束循环; 
结束
 / 
  

PL / SQL过程成功完成。 p>

已过期： 00：00：02.70

  declare 
 otherVar number; 
 begin 
 for i in 1 .. 50000循环
 begin 
从t选择NEEDED_FIELD INTO otherVar，其中cond = 1; 
 exception 
当no_data_found然后
 otherVar：= 0; 
结束
结束循环; 
结束
 / 
  

PL / SQL过程成功完成。 p>

已过期： 00：00：03.06

I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:

SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
   SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....

-or-

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....

The second case seems a bit more elegant to me, because then I can use NEEDED_FIELD, which I would have had to select in the first statement after the condition in the first case. Less code. But if the stored procedure will run faster using the COUNT(*), then I don't mind typing a little more to make up processing speed.

Any hints? Am I missing another possibility?

EDIT I should have mentioned that this is all already nested in a FOR LOOP. Not sure if this makes a difference with using a cursor, since I don't think I can DECLARE the cursor as a select in the FOR LOOP.

解决方案

I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.

The method with count(*) is unsafe. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.

The second version from the original post does not have this problem, and it is generally preferred.

That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.

I ran these benchmarks on Oracle 10.2.0.1 on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).

SQL>create table t (NEEDED_FIELD number, COND number);

Table created.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

1 row created.

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 50000 loop
     select count(*) into cnt from t where cond = 1;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.70

declare
  otherVar  number;
begin
  for i in 1 .. 50000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.06

这篇关于Oracle PL / SQL - NO_DATA_FOUND异常对存储过程性能有坏处理吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！