Производительность запроса при увеличении "больше, чем"производительность по сравнению с отдельным оператором "между"

ProgramBox

Производительность запроса при увеличении «больше, чем»производительность по сравнению с отдельным оператором «между»

Post author:admin
Запись опубликована:8 января, 2022
Post category:Вопросы по программированию

#sql #database #oracle #query-optimization

Вопрос:

У меня есть запрос, который выглядит следующим образом:

 select         
CASE
WHEN col BETWEEN 0 AND 20 THEN 0
WHEN col BETWEEN 20 AND 50 THEN 20
WHEN col BETWEEN 50 AND 100 THEN 40
WHEN col BETWEEN 100 AND 200 THEN 75
WHEN col BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_col
from TAB1;

Я хотел получить лучшую производительность и переписал ее на:

 select case  
WHEN col < 20 then 0
WHEN col < 50 then 20
WHEN col < 100 then 40
WHEN col < 200 then 75
WHEN col < 1000 then 86
END AS t_col
from TAB1;

Я подумал, что второй запрос может быть быстрее, потому что не будет создан интервал, а только значение столбца будет сравниваться с одним числом. План объяснения дает мне одинаковые результаты для обоих запросов. я хотел бы знать, кто из них лучше работает?

1. МЕЖДУ включает в себя обе границы, поэтому вы, вероятно, захотите использовать <= их во 2-м запросе. Что касается производительности: запустите их оба и убедитесь сами. В небольших наборах данных вы не заметите никакой разницы. Насколько велик этот стол?

2. Все это выполняется в оперативной памяти процессором, разница может быть в диапазоне наносекунд. Не тратьте свое время на эту «проблему».

3. План выполнения тот же, потому что он не связан со списком ВЫБОРА, если он не содержит скалярных подзапросов.

4. Я полагаю between , что версия позволяет вам сначала указывать наиболее часто встречающиеся значения, теоретически экономя несколько циклов процессора. Опять же, вторая версия требует только одного сравнения для каждого случая вместо двух. Но, как сказал @WernfriedDomscheit, это вряд ли окажет заметное влияние на любой запрос в реальном мире.

Ответ №1:

Они фактически одинаковы. Любая незначительная разница в циклах процессора будет омрачена вводом-выводом, сетевыми накладными расходами и т.д., Необходимыми для выполнения запроса.

Я полагаю between , что версия позволяет вам сначала указывать наиболее часто встречающиеся значения, теоретически повышая эффективность, если большая часть значений попадает в один или два диапазона. Опять же, < версия требует только одного сравнения для каждого случая вместо двух.

Я все еще присуждаю < версии больше очков за стиль 🙂

Я протестировал обе версии в цикле курсора PL/SQL, чтобы устранить проблемы с сетью, и провел тест несколько раз. Результаты были с интервалом в полсекунды друг от друга более чем в 10 миллионах итераций, что примерно равно изменению в течение нескольких запусков (потому что на любом сервере или ноутбуке всегда есть что-то еще, работающее).

 create table t1 (c1, c2, c3) pctfree 0 nologging
as
select round(dbms_random.value(1,100))
     , cast(dbms_random.string('X',40) as varchar2(40))
     , cast(dbms_random.string('X',40) as varchar2(40))
from   xmltable('1 to 1000000');

 declare
    type test_rectype is record(num number);

    totalTime1 simple_integer := 0;
    totalTime2 simple_integer := 0;

    time1Percentage simple_integer := 0;
    time2Percentage simple_integer := 0;

    function testCursor
        ( cursorNum in integer )
        return sys_refcursor
    is
        testCursor sys_refcursor;
    begin
        if cursorNum = 1 then
            open testCursor for
                select case
                           when c1 < 20 then 0
                           when c1 < 50 then 20
                           when c1 < 100 then 40
                           when c1 < 200 then 75
                           when c1 < 1000 then 86
                       end as t_c1
                from   t1;
        elsif cursorNum = 2 then
            open testCursor for
               select case
                           when c1 < 20 then 0
                           when c1 < 50 then 20
                           when c1 < 100 then 40
                           when c1 < 200 then 75
                           when c1 < 1000 then 86
                       end as t_c1
                from   t1;
        end if;

        return testCursor;
    end testCursor;
      
    -- Fetch all rows from a cursor and return time in hundredths of a second:
    procedure time_cursor
        ( inCursor in sys_refcursor
        , outTime  in out nocopy simple_integer )
    is
        startTime simple_integer := dbms_utility.get_time;
    begin
        -- 21c new iterator syntax
        for r test_rectype in values of inCursor loop
            null;  -- Could also compare rowcounts here
        end loop;

        outTime := dbms_utility.get_time - startTime;

        close inCursor;
    end time_cursor;

    -- Report timing difference:
    procedure print_comparison
        ( time1 simple_integer
        , time2 simple_integer )
    is
    begin
        time1Percentage := 100 * time1 / (time1   time2);
        time2Percentage := 100 * time2 / (time2   time2);

        dbms_output.put_line('Between:  '||to_char(time1/100,'900d00')|| rpad(' |',time1Percentage,'|'));
        dbms_output.put_line('LessThan: '||to_char(time2/100,'900d00')|| rpad(' |',time2Percentage,'|'));
    end print_comparison;

    procedure compare_cursors
        ( runningTime1 in out nocopy simple_integer
        , runningTime2 in out nocopy simple_integer )
    is
        testCursor1 sys_refcursor := testCursor(1);
        testCursor2 sys_refcursor := testCursor(2);

        time1 simple_integer := 0;
        time2 simple_integer := 0;

        time1Percentage simple_integer := 0;
        time2Percentage simple_integer := 0;
    begin
        time_cursor(testCursor1, time1);
        time_cursor(testCursor2, time2);

        print_comparison(time1, time2);

        -- Update running totals:
        runningTime1 := nvl(runningTime1,0)   nvl(time1,0);
        runningTime2 := nvl(runningTime2,0)   nvl(time2,0);

        dbms_output.new_line;
    end compare_cursors;
begin
    for i in 1..10 loop
        compare_cursors(totalTime1, totalTime2);
    end loop;

    dbms_output.put_line('Total:'||chr(10));

    print_comparison(totalTime1, totalTime2);
end;

 Between:    02.18 ||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.25 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.03 |||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.03 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.13 |||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.13 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.13 ||||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.07 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.16 ||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.27 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.28 |||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.28 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.17 ||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.27 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.24 ||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.30 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.24 |||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.27 |||||||||||||||||||||||||||||||||||||||||||||||||

Between:    02.25 ||||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   02.18 |||||||||||||||||||||||||||||||||||||||||||||||||

Total:

Between:    21.81 |||||||||||||||||||||||||||||||||||||||||||||||||
LessThan:   22.05 |||||||||||||||||||||||||||||||||||||||||||||||||

Ответ №2:

Что ж, позвольте мне сделать PoC и посмотреть, что произойдет

 SQL> create table t1 ( c1 number, c2 varchar2(40) , c3 varchar2(40) ) ;

Table created.

SQL> declare
  2  begin
  3  for i in 1 .. 1000000
  4  loop
  5   insert into t1 values ( round(dbms_random.value(1,100)) , dbms_random.string('X',40) , dbms_random.string('X',40) );
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('TESTUSER','T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 ;

  COUNT(*)
----------
   1000000

Сценарий

 SQL> set autotrace traceonly
SQL> select
CASE
WHEN c1 BETWEEN 0 AND 20 THEN 0
WHEN c1 BETWEEN 20 AND 50 THEN 20
WHEN c1 BETWEEN 50 AND 100 THEN 40
WHEN c1 BETWEEN 100 AND 200 THEN 75
WHEN c1 BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_c1
from t1;  2    3    4    5    6    7    8    9

1000000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|  2929K|  2322   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  1000K|  2929K|  2322   (1)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select case
WHEN c1 < 20 then 0
WHEN c1 < 50 then 20
WHEN c1 < 100 then 40
WHEN c1 < 200 then 75
WHEN c1 < 1000 then 86
END AS t_c1
from t1;  2    3    4    5    6    7    8

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|  2929K|  2322   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  1000K|  2929K|  2322   (1)| 00:00:01 |
--------------------------------------------------------------------------

Они ведут себя одинаково, верно? Не совсем, хотя план выглядит так же, давайте проверим статистику. Для этого я буду очищать буферный кэш и общий пул после каждого теста.

 SQL> set autotrace traceonly timing on
SQL> select
CASE
WHEN c1 BETWEEN 0 AND 20 THEN 0
WHEN c1 BETWEEN 20 AND 50 THEN 20
WHEN c1 BETWEEN 50 AND 100 THEN 40
WHEN c1 BETWEEN 100 AND 200 THEN 75
WHEN c1 BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_c1
from t1;  2    3    4    5    6    7    8    9

1000000 rows selected.

Elapsed: 00:00:02.92

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|  2929K|  2322   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  1000K|  2929K|  2322   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
      72870  consistent gets
       6180  physical reads
          0  redo size
   19435128  bytes sent via SQL*Net to client
     733901  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> alter system flush shared_pool ;

System altered.

Elapsed: 00:00:00.08
SQL> alter system flush buffer_cache ;

System altered.

Elapsed: 00:00:00.04
SQL> select t1.* , case
WHEN c1 < 20 then 0
WHEN c1 < 50 then 20
WHEN c1 < 100 then 40
WHEN c1 < 200 then 75
WHEN c1 < 1000 then 86
END AS t_c1
from t1;  2    3    4    5    6    7    8

1000000 rows selected.

Elapsed: 00:00:03.49

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|    81M|  2323   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |  1000K|    81M|  2323   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
      72878  consistent gets
       6180  physical reads
          0  redo size
  101747627  bytes sent via SQL*Net to client
     733834  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

Второй из них медленнее первого, вероятно, из-за того, что recursive calls сделано во втором ( 18 ) против первого ( 12 ). Если вы начнете добавлять больше полей, а данные будут огромными, я совершенно уверен, что вы получите лучшую производительность в запросе, between чем в другом.

Но очевидно, что это просто ощущение, вы должны проверить себя в своей собственной базе данных. Я совершенно уверен, что окончательный запрос содержит другие поля, где условия и т. Д…. Поэтому этот ответ охватывает только то, что вы указали в исходном вопросе, а не то, что произойдет в реальном сценарии со многими другими полями, где условия, индексы и т. Д…

1. Это был довольно точный ответ. У меня есть несколько таких полей сравнения, и представления огромны. Так что эти секоды могут длиться до нескольких минут.

2. спасибо @Amir, рад, что это помогло 🙂

3. Дополнительные рекурсивные вызовы могут быть связаны с синтаксическим анализом, так как при повторном запуске число становится равным 0, и в этом случае выбор синтаксиса не окажет заметного влияния на запросы реального мира. Когда я запускал тест, самым большим фактором на милю была настройка размера массива SQL*Plus, и при повторных запусках единственной статистикой автопробега, которая отличалась, были байты, отправленные/полученные через SQL*Net, в то время как время варьировалось до секунды, предположительно, из-за другой обработки, происходящей в системе.

4. Это ясно., как только разбор будет снят с уравнения. В моем сценарии ответа в тот момент в системе ничего не работало, кроме самого теста. Я сделал несколько, и первый всегда был немного быстрее второго. Однако я не использовал событие трассировки 10046, чтобы увидеть, что на самом деле происходит позади. В любом случае, как я уже сказал, это было просто ощущение, и, вероятно, не стоит вдаваться в подробности, не имея дела с реальным запросом. Я согласен с вами, Уильям, что в случае реального сценария вызовы становятся неуместными, так как об этом позаботится мягкий синтаксический анализ.

5. Я только что заметил , что вторая версия (с < ) включает t1.* в себя, в то время как та, с between которой этого нет. Это может объяснить дополнительные вызовы синтаксического анализа и более длительное время выполнения, так как требуется получить больше данных.

Метки: Производительность запроса при увеличении «больше, чем»производительность по сравнению с отдельным оператором «между»