在SELECT INTO之前使用SELECT COUNT(*)是否比使用Exception慢? [英] Is the use of SELECT COUNT(*) before SELECT INTO slower than using Exceptions?

问题描述

我的最后一个问题让我开始思考.

1)

SELECT COUNT(*) INTO count FROM foo WHERE bar = 123;
IF count > 0 THEN
    SELECT a INTO var FROM foo WHERE bar = 123;
    -- do stuff
ELSE
    -- do other stuff
END IF;

2)

BEGIN
    SELECT a INTO var FROM foo where bar = 123;
    -- do stuff
EXCEPTION
    WHEN no_data_found THEN
        --do other stuff
END ;

我认为2号更快，因为它需要少一趟数据库访问.

I assume number 2 is faster because it requires one less trip to the database.

在任何情况下我都没有考虑过1会更好吗?

Is there any situation where 1 would be superior, that I am not considering?

我要让这个问题再悬几天，在回答之前再收集更多答案.

I'm going to let this question hang for a few more days, to gather some more votes on the answers, before answering it.

推荐答案

如果您使用问题的确切查询，那么第一种变体当然会变慢，因为它必须对满足条件的表中的所有记录进行计数.

If you use exact queries from the question then 1st variant of course slower because it must count all records in table which satisfies criteria.

它必须写为

SELECT COUNT(*) INTO row_count FROM foo WHERE bar = 123 and rownum = 1;

或

select 1 into row_count from dual where exists (select 1 from foo where bar = 123);

因为检查记录的存在足以满足您的目的.

because checking for record existence is enough for your purpose.

当然，这两种变体都不能保证其他人不会在两个语句之间更改foo中的某些内容，但是如果此检查是更复杂的情况的一部分，则不是问题.试想一下当有人执行var值引用操作时，将foo.a的值选择为var后将其值更改了的情况.因此，在复杂的情况下，最好在应用程序逻辑级别上处理此类并发问题.
要执行原子操作，最好使用单个SQL语句.

Of course, both variants don't guarantee that someone else don't change something in foo between two statements, but it's not an issue if this check is a part of more complex scenario. Just think about situation when someone changed value of foo.a after selecting it's value into var while performing some actions which refers selected var value. So in complex scenarios better to handle such concurrency issues on application logic level.
To perform atomic operations is better to use single SQL statement.

上述任何变体都需要在SQL和PL/SQL之间进行2个上下文切换，并且需要2个查询，因此在表中找到行的情况下，其执行速度将比下面描述的任何变体都要慢.

Any of variants above requires 2 context switches between SQL and PL/SQL and 2 queries so performs slower then any variant described below in cases when row found in a table.

还有另一种检查行是否存在的变体:

There are another variants to check existence of row without exception:

select max(a), count(1) into var, row_count 
from foo 
where bar = 123 and rownum < 3;

如果row_count = 1，则只有一行满足条件.

If row_count = 1 then only one row satisfies criteria.

由于对foo的唯一约束，有时仅检查是否存在就足够了，这保证了foo中没有重复的bar值.例如. bar是主键.
在这种情况下，可以简化查询:

Sometime it's enough to check only for existence because of unique constraint on the foo which guarantees that there are no duplicated bar values in foo. E.g. bar is primary key.
In such cases it's possible to simplify query:

select max(a) into var from foo where bar = 123;
if(var is not null) then 
  ...
end if;

或使用光标处理值:

for cValueA in ( 
  select a from foo where bar = 123
) loop
  ...  
end loop;

下一个变体来自链接 ，由@ user272735在他的回答中提供:

Next variant is from link, provided by @user272735 in his answer:

select 
  (select a from foo where bar = 123)
  into var 
from dual;

根据我的经验，在大多数情况下，没有异常块的任何变体都比带有异常的变体快，但是，如果此类块的执行次数很少，那么最好使用带有no_data_found和too_many_rows异常处理的异常块来改进代码可读性.

From my experience any variant without exception blocks in most cases faster then a variant with exceptions, but if number of executions of such block is low then better to use exception block with handling of no_data_found and too_many_rows exceptions to improve code readability.

选择使用异常还是不使用异常的正确点，是在问一个问题:这种情况适用于申请吗?".如果找不到行，并且可以预期的情况可以处理(例如添加新行或从另一个地方获取数据等等)，则最好避免发生异常.如果出乎意料，并且无法解决问题，则捕获异常以自定义错误消息，将其写入事件日志并重新抛出，或者根本不捕获它.

Right point to choose to use exception or don't use it, is to ask a question "Is this situation are normal for application?". If row not found and it's a expected situation which can be handled (e.g. add new row or take data from another place and so on) is better to avoid exception. If it's unexpected and there are no way to fix a situation, then catch exception to customize error message, write it to event log and re-throw, or just don't catch it at all.

要比较性能，只需在您的系统上做一个简单的测试用例，然后将这两个变体调用多次并进行比较.
多说一点，在90％的应用程序中，这个问题比理论上的实际问题多，因为还有很多其他性能问题来源必须首先考虑.

To compare performance just make a simple test case on you system whith both variants called many times and compare.
Say more, in 90 percent of applications this question is more theoretical than practical because there are a lot of another sources of performance issues which must be taken into account first.

更新

我复制了此页面上的示例在SQLFiddle网站上进行了一些更正(链接).
结果证明，从dual中进行选择的变体效果最佳:大多数查询成功时开销很小，而缺失行数增加时性能降低最低.
令人惊讶的是，如果所有查询均失败，则带有count()和两个查询的变体显示出最佳结果.

I reproduced example from this page at SQLFiddle site with a little corrections (link).
Results prove that variant with selecting from dual performs best: a little overhead when most of queries succeed and lowest performance degradation when number of missing rows raises.
Surprisingly variant with count() and two queries showed best result in case if all queries failed.

| FNAME | LOOP_COUNT | ALL_FAILED | ALL_SUCCEED | variant name |
----------------------------------------------------------------
|    f1 |       2000 |       2.09 |        0.28 |  exception   |
|    f2 |       2000 |       0.31 |        0.38 |  cursor      |
|    f3 |       2000 |       0.26 |        0.27 |  max()       |
|    f4 |       2000 |       0.23 |        0.28 |  dual        |
|    f5 |       2000 |       0.22 |        0.58 |  count()     |

-- FNAME        - tested function name 
-- LOOP_COUNT   - number of loops in one test run
-- ALL_FAILED   - time in seconds if all tested rows missed from table
-- ALL_SUCCEED  - time in seconds if all tested rows found in table
-- variant name - short name of tested variant

下面是用于测试环境和测试脚本的设置代码.

Below is a setup code for test environment and test script.

create table t_test(a, b)
as
select level,level from dual connect by level<=1e5
/
insert into t_test(a, b) select null, level from dual connect by level < 100
/

create unique index x_text on t_test(a)
/

create table timings(
  fname varchar2(10), 
  loop_count number, 
  exec_time number
)
/

create table params(pstart number, pend number)
/
-- loop bounds
insert into params(pstart, pend) values(1, 2000)
/

- f1 -异常处理

create or replace function f1(p in number) return number
as
  res number;
begin
  select b into res
  from t_test t
  where t.a=p and rownum = 1;
  return res;
exception when no_data_found then
  return null;
end;
/

- f2 -光标循环

create or replace function f2(p in number) return number
as
  res number;
begin
  for rec in (select b from t_test t where t.a=p and rownum = 1) loop
    res:=rec.b;
  end loop;
  return res;
end;
/

- f3 -max()

create or replace function f3(p in number) return number
as
  res number;
begin
  select max(b) into res
  from t_test t
  where t.a=p and rownum = 1;
  return res;
end;
/

- f4 -在双选中选择作为字段

-- f4 - select as field in select from dual

create or replace function f4(p in number) return number
as
  res number;
begin
  select
    (select b from t_test t where t.a=p and rownum = 1)
    into res
  from dual;
  return res;
end;
/

- f5 -检查count()然后获取值

-- f5 - check count() then get value

create or replace function f5(p in number) return number
as
  res number;
  cnt number;
begin
  select count(*) into cnt
  from t_test t where t.a=p and rownum = 1;

  if(cnt = 1) then
    select b into res from t_test t where t.a=p;
  end if;

  return res;
end;
/

测试脚本:

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f1(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f1', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f2(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f2', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f3(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f3', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f4(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f4', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;
  --v_end := v_start + trunc((v_end-v_start)*2/3);

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f5(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f5', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

select * from timings order by fname
/

这篇关于在SELECT INTO之前使用SELECT COUNT(*)是否比使用Exception慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！