Как оптимизировать запрос???

Модераторы: kdv, CyberMax

Ответить
Саша
Сообщения: 5
Зарегистрирован: 29 ноя 2007, 12:32

Как оптимизировать запрос???

Сообщение Саша » 29 ноя 2007, 13:41

Приветствую всех!
Есть следующий запрос:

Код: Выделить всё

   
    Select SUM(tdetails.stl_amount) Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
           SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
           SUM(tdetails.stl_fee)          SUM_STL_FEE   
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and

           tdetails.tran_type <> '70'   and

           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id
Он выполняется быстро....но стоит добавить еще одно условие отбора:
tdetails.tran_type <> '21' запрос выполняется более 10 сек:

Код: Выделить всё

    Select SUM(tdetails.stl_amount) Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
           SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
           SUM(tdetails.stl_fee)          SUM_STL_FEE   
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and

           tdetails.tran_type <> '70'   and
           tdetails.tran_type <> '21'  and

           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id
Кто то может объяснить что происходит??И если возможность это как то оптимизировать??
Последний раз редактировалось Саша 29 ноя 2007, 18:23, всего редактировалось 1 раз.
Вернуться к началу
stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Re: Как оптимизировать запрос???

Сообщение stix-s » 29 ноя 2007, 14:31

Саша писал(а):Приветствую всех!
Есть следующий запрос:


Кто то может объяснить что происходит??И если возможность это как то оптимизировать??
1 используй тег Code, ведь нечитабельно ни черта :(
2 смотри планы
Вернуться к началу
Саша
Сообщения: 5
Зарегистрирован: 29 ноя 2007, 12:32

Re: Как оптимизировать запрос???

Сообщение Саша » 29 ноя 2007, 15:33

Попробую переформулировать вопрос:)

Эти два запроса делают одно и тоже (и выдают один и тот же результат)-разница в последних условиях отбора по полю tran_type:

1)

Код: Выделить всё

  SELECT  SUM(tdetails.stl_amount) Sum_STL_Amount,
                  COUNT(tstl_operation.operation)  COUNT_OPER,
                  SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
                  SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
                  SUM(tdetails.stl_fee)          SUM_STL_FEE 
  
    FROM tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    WHERE    tpackage.name='settlement' AND
                  tdocument.date_doc=:date_doc AND
                  tdetails.stl_ccy=980 AND
                  tpackage.id_pack=tdocument.pack_id AND
                  tdocument.id_doc=tsettlement.doc_id AND   
                  tsettlement.id_set=tstl_operation.set_id AND   
                  tstl_operation.id_stl=tdetails.stl_id  and              

                 ( (tdetails.tran_type = '04')   or
                    (tdetails.tran_type = '05')  or
                    (tdetails.tran_type = '25')  or
                    (tdetails.tran_type = '29')   or
                    (tdetails.tran_type = '61')  or
                    (tdetails.tran_type = '96'))
2)

Код: Выделить всё


    SELECT SUM(tdetails.stl_amount) Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
           SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
           SUM(tdetails.stl_fee)          SUM_STL_FEE  

    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and
           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id and

           tdetails.tran_type <> '70'   and
           tdetails.tran_type <> '21'
Вопрос почему первый запрос работает в 10 раз быстрее второго??И как можно оптимизировать второй запрос(с неравенствами) чтобы он работал быстрее??
Последний раз редактировалось Саша 30 ноя 2007, 09:49, всего редактировалось 1 раз.
Вернуться к началу
Attid
Спец
Сообщения: 377
Зарегистрирован: 14 ноя 2006, 09:58

Сообщение Attid » 29 ноя 2007, 21:37

а планы так и не показал . . .
Вернуться к началу
Саша
Сообщения: 5
Зарегистрирован: 29 ноя 2007, 12:32

Сообщение Саша » 30 ноя 2007, 09:45

Вот вам и планы:

Код: Выделить всё

План
PLAN JOIN (TDETAILS INDEX (TDETAILS_STL_CCY),TSTL_OPERATION INDEX (PK_TSTL_OPERATION),TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE))

Адаптированный план
PLAN JOIN (TDETAILS INDEX (TDETAILS_STL_CCY),TSTL_OPERATION INDEX (PK_TSTL_OPERATION),TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE))

------ Performance info ------
Prepare time = 0ms
Execute time = 9s 937ms
Avg fetch time = 9 937,00 ms
Current memory = 2 081 056
Max memory = 7 516 064
Memory buffers = 2 048
Reads from disk to cache = 4 855
Writes from cache to disk = 0
Fetches from cache = 4 034 017
Вернуться к началу
mdfv
Сообщения: 119
Зарегистрирован: 23 май 2006, 15:53

Сообщение mdfv » 30 ноя 2007, 09:58

Кроме того не озвучена версия сервера.
А вообще лучше делать соединение явно через join.

И планы обоих запросов надо.
Вернуться к началу
Slavik
Сообщения: 115
Зарегистрирован: 17 янв 2007, 11:52

Сообщение Slavik » 30 ноя 2007, 10:31

И ещё самого первого запроса
Вернуться к началу
Саша
Сообщения: 5
Зарегистрирован: 29 ноя 2007, 12:32

Сообщение Саша » 30 ноя 2007, 17:44

Я уже разобрался если кому интересно:

Код: Выделить всё

   Select  SUM(tdetails.stl_amount)         Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)           SUM_Tran_FEE,
           SUM(tdetails.tran_amount)        SUM_Tran_Amount,
           SUM(tdetails.stl_fee)            SUM_STL_FEE
             
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails
    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and
           tdetails.tran_type not in ('70', '21') and
           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id
Просто вместо неравенств поставил not in и запрос выполняется в 40 раз быстрее..незнаю почему:)
Вернуться к началу
Саша
Сообщения: 5
Зарегистрирован: 29 ноя 2007, 12:32

Сообщение Саша » 30 ноя 2007, 17:56

Версия Firebird 1.5.3
Вот все планы:

Код: Выделить всё

    Select SUM(tdetails.stl_amount) Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
           SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
           SUM(tdetails.stl_fee)          SUM_STL_FEE   
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and

           tdetails.tran_type <> '70'  and

           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id 

План
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))

Адаптированный план
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))

------ Performance info ------
Prepare time = 0ms
Execute time = 250ms
Avg fetch time = 250,00 ms
Current memory = 11 190 160
Max memory = 12 734 640
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 121 397


Код: Выделить всё

    Select SUM(tdetails.stl_amount) Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)         SUM_Tran_FEE,       
           SUM(tdetails.tran_amount)      SUM_Tran_Amount,   
           SUM(tdetails.stl_fee)          SUM_STL_FEE   
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails

    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and

           tdetails.tran_type <> '70'   and
           tdetails.tran_type <> '21'  and

           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id 

План
PLAN JOIN (TDETAILS INDEX (TDETAILS_STL_CCY),TSTL_OPERATION INDEX (PK_TSTL_OPERATION),TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE))

Адаптированный план
PLAN JOIN (TDETAILS INDEX (TDETAILS_STL_CCY),TSTL_OPERATION INDEX (PK_TSTL_OPERATION),TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE))

------ Performance info ------
Prepare time = 0ms
Execute time = 9s 922ms
Avg fetch time = 9 922,00 ms
Current memory = 11 899 072
Max memory = 12 734 640
Memory buffers = 2 048
Reads from disk to cache = 4 818
Writes from cache to disk = 0
Fetches from cache = 4 034 017
А вот последний запрос с использованием not in

Код: Выделить всё

   Select  SUM(tdetails.stl_amount)         Sum_STL_Amount,
           COUNT(tstl_operation.operation)  COUNT_OPER,
           SUM(tdetails.tran_fee)           SUM_Tran_FEE,
           SUM(tdetails.tran_amount)        SUM_Tran_Amount,
           SUM(tdetails.stl_fee)            SUM_STL_FEE
             
    From tpackage,  tdocument, tsettlement, tstl_operation, tdetails
    Where  tpackage.name='settlement' and
           tdocument.date_doc=:date_doc and
           tdetails.stl_ccy=980 and
          tdetails.tran_type not in ('70', '21') and
           tpackage.id_pack=tdocument.pack_id and
           tdocument.id_doc=tsettlement.doc_id and   
           tsettlement.id_set=tstl_operation.set_id and   
           tstl_operation.id_stl=tdetails.stl_id


План
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))

Адаптированный план
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))

------ Performance info ------
Prepare time = 0ms
Execute time = 250ms
Avg fetch time = 250,00 ms
Current memory = 11 190 624
Max memory = 12 734 640
Memory buffers = 2 048
Reads from disk to cache = 50
Writes from cache to disk = 0
Fetches from cache = 121 397
Вернуться к началу
stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 03 дек 2007, 08:12

Саша писал(а):Версия Firebird 1.5.3
Вот все планы:

Код: Выделить всё

Адаптированный план
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))

Адаптированный план
PLAN JOIN (TDETAILS INDEX (TDETAILS_STL_CCY),TSTL_OPERATION INDEX (PK_TSTL_OPERATION),TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE))

Адаптированный план
PLAN JOIN (TSTL_OPERATION NATURAL,TSETTLEMENT INDEX (PK_TSETTLEMENT),TDOCUMENT INDEX (PK_TDOCUMENT),TPACKAGE INDEX (PK_TPACKAGE),TDETAILS INDEX (TDETAILS_STL_ID))
Разницу в планах видишь?
http://ibase.ru/devinfo/dataaccesspaths.htm
Вернуться к началу
Ответить

Вернуться в «Проектирование БД и запросов»