Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Почему функция выполняется дольше чем тот же запрос.  [new]
dba08
Guest
Добрый день, ситуация такая. Есть запрос такого вида:
select count(n) from table1 where...
union all
select count(n) from table1 where...
union all
select count(n) from table1 where...
union all
select count(n) from table1 where...
union all ...

и так 100 раз

Далее я создал функцию f_cnt, которая содержит в себе один запрос:

FUNCTION F_CNT ( ... )
  RETURN  number IS
   ret number;
BEGIN 
      select count(n) into ret from table1 where...
     return ret;  
END;

И исходный запрос переписываем следующим образом:
select f_cnt from dual
union all
select f_cnt from dual
union all
select f_cnt from dual
union all
.....
и так 100 раз


в результате имеем для 1-го варианта без функции:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      1.15       1.12          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.67       0.65          0       8134          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.83       1.78          0       8134          0         100



2-й вариант запроса, но с функцией:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100    103.27     100.85          0    2079637          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      200    103.27     100.86          0    2079637          0         100



Почему так происходит, как быть ? С функцией мне гораздо удобней, так как код более удобочитаемым становится и данная функция используется много где. Но работает на МНОГО дольше.

Заранее благодарен за мнения.
18 мар 08, 09:47    [5422110]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
вот-вот
Guest
Может если в выражении where условия поддаются некой закономерности
переписать функцию так, чтобы она возвращала сразу нужный набор записей, чем
делать 100 запросов?
18 мар 08, 09:53    [5422136]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
вот-вот
Guest
Возможно тебе помогут аналитические функции, дабы не писать кучу юнионов, а сделать одним запросом, но опять же зависит от суловий, наверно :)
А так вообще странно :)) почему-то во втором варианте куча разборов, может надо почистить кэш перед выполнением каждого варианта? ф.з. ))) интересненько :)
18 мар 08, 09:58    [5422164]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
dba08
Guest
юнионов на самом деле у меня нет, это я для удобства привёл такой пример, но суть от этого не меняется. В реале запрос такого вида:
select id,

         (select count(t1.n) 
            from table1 t1, 
                   table2 t2,
                   table3 t3
                   table4 t4
          where t2.f2=tb1.f1...) cnt,
         
         ....
  from tab1 tb1, tab2 tb2...
where .... 

мне надо, чтобы ,было вот так:

select id,
         f_cnt(tb1.f1) cnt,       
         ....
  from tab1 tb1, tab2 tb2...
where .... 

но работает долго
18 мар 08, 10:08    [5422219]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
вот-вот
Guest
Не, ниасилил - ждем более бодрый народ. Аууу кто-нибуудь :))
Может поточнее бы запорсик озвучить? Хотя на старте итак вроде конкретный вопрос, почему через функцию медленее. Банально типа как писал кайт пл/скл всегда медленне скл? :))
18 мар 08, 10:13    [5422246]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
concepts
Member

Откуда: из засады
Сообщений: 279
dba08
Почему так происходит, как быть ? С функцией мне гораздо удобней, так как код более удобочитаемым становится и данная функция используется много где. Но работает на МНОГО дольше.

Заранее благодарен за мнения.

1 fetch в 100 строк в первом случае и 100 fetch'ей во втором на мысль не наталкивают?
18 мар 08, 10:23    [5422297]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
givanov
Member

Откуда:
Сообщений: 757
Что-то автор не договаривает. Просто на вызов функции такие накладные расходы получить не получится.
Думаю, стоит выложить полный пример.
18 мар 08, 10:33    [5422341]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
Задумайтесь над этим :)
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as CUB_D04
 
SQL> create table bigt(n) as select level from dual connect by level<=1e5;
 
Table created
 
SQL> create table smallt(n) as select level from dual connect by level<=1e2;
 
Table created
 
SQL> set timing on
SQL> 
SQL> SELECT SUM(s)
  2    FROM (SELECT (SELECT COUNT(*) FROM smallt s WHERE s.n<b.n) s
  3            FROM bigt b)
  4  /
 
    SUM(S)
----------
   9994950
 
Executed in 2,424 seconds
 
SQL> update bigt set n=least(n,1e2);
 
100000 rows updated
 
Executed in 3,144 seconds
 
SQL> commit;
 
Commit complete
 
Executed in 0,01 seconds
 
SQL> 
SQL> SELECT SUM(s)
  2    FROM (SELECT (SELECT COUNT(*) FROM smallt s WHERE s.n<b.n) s
  3            FROM bigt b)
  4  /
 
    SUM(S)
----------
   9895050
 
Executed in 0,051 seconds
 
SQL> 
SQL> create or replace function f(n number) return NUMBER IS r NUMBER;
  2  BEGIN select count(*) into r from smallt where smallt.n<f.n; return r;
  3  end;
  4  /
 
Function created
 
Executed in 0,03 seconds
 
SQL> 
SQL> SELECT SUM(s)
  2    FROM (SELECT f(n) s
  3            FROM bigt b)
  4  /
 
    SUM(S)
----------
   9895050
 
Executed in 6,76 seconds
 
SQL> 
SQL> create or replace function f(n number) return NUMBER
  2  DETERMINISTIC
  3  IS r NUMBER;
  4  BEGIN select count(*) into r from smallt where smallt.n<f.n; return r;
  5  end;
  6  /
 
Function created
 
Executed in 0,03 seconds
 
SQL> 
SQL> SELECT SUM(s)
  2    FROM (SELECT f(n) s
  3            FROM bigt b)
  4  /
 
    SUM(S)
----------
   9895050
 
Executed in 0,12 seconds
 
SQL> 
18 мар 08, 11:04    [5422540]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
givanov
Member

Откуда:
Сообщений: 757
RA\/EN
Задумайтесь над этим :)
Ничего странного. 100000 вызовов функции - это, действительно, 5 секунд накладных расходов. По словам автора, у него 100 таких вызовов приводят к 100 секундам разницы времени выполнения.
Пропорции не соблюдены, однако.
18 мар 08, 11:41    [5422817]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
dba08
Guest
2RAVEN
У меня Ваш пример почему-то не работает:


SQL*Plus: Release 9.2.0.1.0 - Production on Втн Мар 18 13:54:00 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Присоединен к:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table bigt(n) as select level from dual connect by level<=1e5;

Таблица создана.

SQL> create table smallt(n) as select level from dual connect by level<=1e2;

Таблица создана.

SQL> set timing on
SQL>
SQL> SELECT SUM(s)
  2    FROM (SELECT (SELECT COUNT(*) FROM smallt s WHERE s.n<b.n) s
  3            FROM bigt b)
  4  /

    SUM(S)
----------
   9994950

Затрач.время: 00:00:02.00
SQL> update bigt set n=least(n,1e2);

100000 строк обновлено.

Затрач.время: 00:00:10.07
SQL> commit;

Фиксация обновлений завершена.

Затрач.время: 00:00:00.00
SQL>
SQL> SELECT SUM(s)
  2    FROM (SELECT (SELECT COUNT(*) FROM smallt s WHERE s.n<b.n) s
  3            FROM bigt b)
  4  /

    SUM(S)
----------
   9895050

Затрач.время: 00:00:00.00
SQL>
SQL> create or replace function f(n number) return NUMBER IS r NUMBER;
  2  BEGIN select count(*) into r from smallt where smallt.n<f.n; return r;
  3  end;
  4  /

Функция создана.

Затрач.время: 00:00:00.00
SQL>
SQL> SELECT SUM(s)
  2    FROM (SELECT f(n) s
  3            FROM bigt b)
  4  /

    SUM(S)
----------
   9895050

Затрач.время: 00:00:06.04
SQL>
SQL> create or replace function f(n number) return NUMBER
  2  DETERMINISTIC
  3  IS r NUMBER;
  4  BEGIN select count(*) into r from smallt where smallt.n<f.n; return r;
  5  end;
  6  /

Функция создана.

Затрач.время: 00:00:00.00
SQL> SELECT SUM(s)
  2    FROM (SELECT f(n) s
  3            FROM bigt b)
  4  /

    SUM(S)
----------
   9895050

Затрач.время: 00:00:06.04
SQL>
SQL> alter session set QUERY_REWRITE_ENABLED=TRUE;

Сеанс изменен.

Затрач.время: 00:00:00.00
SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

Сеанс изменен.

Затрач.время: 00:00:00.00
SQL>
SQL> SELECT SUM(s)
  2    FROM (SELECT f(n) s
  3            FROM bigt b)
  4  /

    SUM(S)
----------
   9895050

Затрач.время: 00:00:06.05
SQL>
18 мар 08, 12:05    [5423003]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
Алымов Анатолий
Member

Откуда: Оренбург
Сообщений: 1309
Не забудьте про время на переключение контекстов sql и pl/sql
18 мар 08, 12:09    [5423034]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
stranno
Guest
dba08
мне надо, чтобы ,было вот так:
select id,
         f_cnt(tb1.f1) cnt,       
         ....
  from tab1 tb1, tab2 tb2...
where .... 

А можно поподробнее тот where ...?
18 мар 08, 12:24    [5423157]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
givanov
RA\/EN
Задумайтесь над этим :)
Ничего странного. 100000 вызовов функции - это, действительно, 5 секунд накладных расходов. По словам автора, у него 100 таких вызовов приводят к 100 секундам разницы времени выполнения.
Пропорции не соблюдены, однако.

Смотри внимательнее Особенно на разницу в выполненнии 2-х запросов с функцией и разницу в выполнении 2-х запросов без функции.
18 мар 08, 12:31    [5423209]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3659
dba08
2RAVEN
У меня Ваш пример почему-то не работает:

Ну тогда попробуйте так:
SELECT SUM(s)
      FROM (SELECT (SELECT f(n) FROM dual) s
              FROM bigt b)
На 9-ке помогло.
Видимо, только в 10.2 оракл поумнел и не стал требовать явного заворачивания функции в курсор.
18 мар 08, 12:41    [5423273]     Ответить | Цитировать Сообщить модератору
 Re: Почему функция выполняется дольше чем тот же запрос.  [new]
givanov
Member

Откуда:
Сообщений: 757
RA\/EN
Смотри внимательнее
Ну так бы и сказал, scalar subquery caching.
Это может быть.
18 мар 08, 13:04    [5423517]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить