iBase.ru Forum

Форум по InterBase, Firebird и Yaffil
http://www.ibaseforum.ru/

Оптимизация запроса

http://www.ibaseforum.ru/viewtopic.php?f=4&t=2691

Страница 1 из 2

Оптимизация запроса

Добавлено: 22 сен 2006, 10:49
ZiBiT
Здраствуйте не подскажите можноли оптимизировать этот запрос чтоб он выполнялся быстрее

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

SELECT MAILS.ID as IDM, MAILS.ORDERS, CLIENTS.NAME as CLIENT,Clients.id as IDC, MAILS.NUMMAIL,MAILS.RING as PROSVON, MAILS.FIRM, MAILS.MAN, STREETS.NAME as STREET, MAILS.HOME, MAILS.LETTEROUT, 
MAILS.AREANUM, MAILS.backcl , MAILS.AREALET, MAILS.ADDADR, MAILS.PHONE, SERVICES.NAME as SERVICE1, MAILS.SLIPTIME, MAILS.SLIPMAN, MAILS.SLIPDOLG, MAILS.SLIPPHONE, EXECSOSTS.NAME AS EXECSOT1, MAILS.NUMRECIP, MAILS.EXECSOST, REASONS.NAME as REASON 
, MAILS.courier1 as courier, MAILS.SLIPDATE 
FROM MAILS
LEFT JOIN STREETS ON (MAILS.STREETOUT = STREETS.ID)
INNER JOIN EXECSOSTS ON (MAILS.EXECSOST = EXECSOSTS.ID)
LEFT JOIN TARIFFS ON (MAILS.TARIFF = TARIFFS.ID)
LEFT JOIN ORDERS ON (MAILS.ORDERS = ORDERS.ID)
INNER JOIN SERVICES ON (TARIFFS.SERVICE = SERVICES.ID)
INNER JOIN REASONS ON (MAILS.BACKCL = REASONS.ID)
INNER JOIN CLIENTS ON (ORDERS.CLIENT = CLIENTS.ID)
where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')
ORDER BY ORDERS desc, NUMMAIL


PLAN SORT (MERGE (SORT (CLIENTS NATURAL),SORT (MERGE (SORT (REASONS NATURAL),SORT (MERGE (SORT (SERVICES NATURAL),SORT (JOIN (JOIN (MERGE (SORT (EXECSOSTS NATURAL),SORT (JOIN (MAILS NATURAL,STREETS INDEX (RDB$PRIMARY1)))),TARIFFS INDEX (RDB$PRIMARY49)),ORDERS INDEX (RDB$PRIMARY10)))))))))

Adapted Plan
PLAN SORT (MERGE (SORT (CLIENTS NATURAL),SORT (MERGE (SORT (REASONS NATURAL),SORT (MERGE (SORT (SERVICES NATURAL),SORT (JOIN (JOIN (MERGE (SORT (EXECSOSTS NATURAL),SORT (JOIN (MAILS NATURAL,STREETS INDEX (INTEG_2)))),TARIFFS INDEX (INTEG_69)),ORDERS INDEX (INTEG_20)))))))))

------ Performance info ------
Prepare time = 16ms
Execute time = 14s 594ms
Avg fetch time = 810,78 ms
Current memory = -24 460 176
Max memory = 45 982 112
Memory buffers = 3 000
Reads from disk to cache = 64 299
Writes from cache to disk = 0
Fetches from cache = 1 856 665
InterBase 7.5

Добавлено: 22 сен 2006, 11:05
dimitr
1) создать необходимые индексы
2) перенести все INNER выше LEFT

Добавлено: 22 сен 2006, 11:31
ZiBiT
Создал индекс для поля firm
Скорость осталось прежней мин. 14 сек
Перенисти все Inner выше Left Не получается
т. к.
таблицы соединяются сначала по left а по том к ним подкл. маленькие табл. по Inner

Добавлено: 22 сен 2006, 11:38
dimitr
ZiBiT писал(а):таблицы соединяются сначала по left а по том к ним подкл. маленькие табл. по Inner
если "к ним" - это к левым табличкам джойнов, то ты зря так думаешь. А если это обозначает "к результатам джойна", то тогда положение INNER-а монопенисуально.

Добавлено: 22 сен 2006, 11:49
ZiBiT

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

Plan
------------------------------------------------
PLAN SORT (MERGE (SORT (CLIENTS NATURAL),SORT (MERGE (SORT (REASONS NATURAL),SORT (MERGE (SORT (SERVICES NATURAL),SORT (JOIN (JOIN (MERGE (SORT (EXECSOSTS NATURAL),SORT (JOIN (MAILS NATURAL,STREETS INDEX (RDB$PRIMARY1)))),TARIFFS INDEX (RDB$PRIMARY49)),ORDERS INDEX (RDB$PRIMARY10)))))))))

Adapted Plan
------------------------------------------------
PLAN SORT (MERGE (SORT (CLIENTS NATURAL),SORT (MERGE (SORT (REASONS NATURAL),SORT (MERGE (SORT (SERVICES NATURAL),SORT (JOIN (JOIN (MERGE (SORT (EXECSOSTS NATURAL),SORT (JOIN (MAILS NATURAL,STREETS INDEX (INTEG_2)))),TARIFFS INDEX (INTEG_69)),ORDERS INDEX (INTEG_20)))))))))

Query Time
------------------------------------------------
Prepare       : 0,00 ms
Execute       : 17 907,00 ms
Avg fetch time: 994,83 ms

Memory
------------------------------------------------
Current: -97 375 856
Max    : 45 982 112
Buffers: 3 000

Operations
------------------------------------------------
Read   : 64 316
Writes : 0
Fetches: 1 857 204


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts |
|                          |   Total   |   reads   |    reads    |         |         |         |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|                   CLIENTS|         0 |         0 |         967 |       0 |       0 |       0 |
|                 EXECSOSTS|         0 |         0 |           8 |       0 |       0 |       0 |
|                     MAILS|         0 |         0 |      831780 |       0 |       0 |       0 |
|                    ORDERS|         0 |       420 |           0 |       0 |       0 |       0 |
|                   REASONS|         0 |         0 |          14 |       0 |       0 |       0 |
|                  SERVICES|         0 |         0 |           4 |       0 |       0 |       0 |
|                   STREETS|         0 |       420 |           0 |       0 |       0 |       0 |
|                   TARIFFS|         0 |       420 |           0 |       0 |       0 |       0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
Создал индекс в табличке mails по полю firm а он почему то не ищет по этому индексу

Добавлено: 22 сен 2006, 12:18
kdv
блин. кроме того, A LEFT JOIN B = B RIGHT JOIN A
www.ibase.ru/devinfo/joins.htm
Создал индекс в табличке mails по полю firm а он почему то не ищет по этому индексу
а почему он должен искать ПО ФУНКЦИИ?

Добавлено: 22 сен 2006, 12:53
dimitr
все условия джойнов идут через PK/FK? Или по условиям связи вообще нет индексов?

Добавлено: 22 сен 2006, 12:57
ZiBiT
Да все связи идуд по индексам
Где все ID - PK
А остальн. FK

Добавлено: 22 сен 2006, 13:06
dimitr
тогда еще раз говорю - перепиши запрос:

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

FROM MAILS
INNER JOIN EXECSOSTS ON (MAILS.EXECSOST = EXECSOSTS.ID)
INNER JOIN REASONS ON (MAILS.BACKCL = REASONS.ID)
LEFT JOIN STREETS ON (MAILS.STREETOUT = STREETS.ID)
LEFT JOIN TARIFFS ON (MAILS.TARIFF = TARIFFS.ID)
LEFT JOIN ORDERS ON (MAILS.ORDERS = ORDERS.ID)
LEFT JOIN SERVICES ON (TARIFFS.SERVICE = SERVICES.ID)
LEFT JOIN CLIENTS ON (ORDERS.CLIENT = CLIENTS.ID)
where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')
или

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

FROM MAILS
INNER JOIN EXECSOSTS ON (MAILS.EXECSOST = EXECSOSTS.ID)
INNER JOIN REASONS ON (MAILS.BACKCL = REASONS.ID)
LEFT JOIN STREETS ON (MAILS.STREETOUT = STREETS.ID)
LEFT JOIN (TARIFFS 
  INNER JOIN SERVICES ON (TARIFFS.SERVICE = SERVICES.ID))
  ON (MAILS.TARIFF = TARIFFS.ID)
LEFT JOIN (ORDERS 
  INNER JOIN CLIENTS ON (ORDERS.CLIENT = CLIENTS.ID))
  ON (MAILS.ORDERS = ORDERS.ID)
where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')

Добавлено: 22 сен 2006, 13:28
ZiBiT
Код:
FROM MAILS
INNER JOIN EXECSOSTS ON (MAILS.EXECSOST = EXECSOSTS.ID)
INNER JOIN REASONS ON (MAILS.BACKCL = REASONS.ID)
LEFT JOIN STREETS ON (MAILS.STREETOUT = STREETS.ID)
LEFT JOIN (TARIFFS
INNER JOIN SERVICES ON (TARIFFS.SERVICE = SERVICES.ID))
ON (MAILS.TARIFF = TARIFFS.ID)
LEFT JOIN (ORDERS
INNER JOIN CLIENTS ON (ORDERS.CLIENT = CLIENTS.ID))
ON (MAILS.ORDERS = ORDERS.ID)
where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')
Выполнелся за 2 мин 36 сек


Убрал ORDER BY mails.ORDERS desc, mails.NUMMAIL

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

Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (MAILS NATURAL,REASONS INDEX (RDB$PRIMARY33),EXECSOSTS INDEX (RDB$PRIMARY25)),STREETS INDEX (RDB$PRIMARY1)),TARIFFS INDEX (RDB$PRIMARY49)),ORDERS INDEX (RDB$PRIMARY10)),SERVICES INDEX (RDB$PRIMARY52)),CLIENTS INDEX (RDB$PRIMARY6))

Adapted Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (MAILS NATURAL,REASONS INDEX (INTEG_49),EXECSOSTS INDEX (INTEG_39)),STREETS INDEX (INTEG_2)),TARIFFS INDEX (INTEG_69)),ORDERS INDEX (INTEG_20)),SERVICES INDEX (INTEG_73)),CLIENTS INDEX (INTEG_10))

------ Performance info ------
Prepare time = 16ms
Execute time = 78ms
Avg fetch time = 4,33 ms
Current memory = 12 644 540
Max memory = 36 458 128
Memory buffers = 3 000
Reads from disk to cache = 25 765
Writes from cache to disk = 0
Fetches from cache = 770 146
[/quote]

Добавлено: 22 сен 2006, 13:41
ZiBiT
Не подскажите что можно сделать с Order by

Добавлено: 22 сен 2006, 14:58
kdv
Не подскажите что можно сделать с Order by
сортировку в конфиге настроить.

Добавлено: 22 сен 2006, 15:00
kdv
where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')
и еще. если вот такого рода условие отбора существенно уменьшает число записей в MAILS, то вариантов два

1. или перевести все в MAILS.FIRM в UPPER, чтобы не использовать функцию при поиске. Тогда будет работать индекс (по идее).

2. использовать индекс по выражению в YA или FB 2.

Добавлено: 22 сен 2006, 15:05
ZiBiT
сортировку в конфиге настроить.
а неподскажите где именно настраивать :oops:

Добавлено: 22 сен 2006, 15:11
ZiBiT
Upper убрал поиск все равно не идет по индексу
Если убрать Upper и Like Поиск идет по Индексу

Добавлено: 22 сен 2006, 15:12
dimitr
с таким шаблоном в LIKE - и не будет

Добавлено: 22 сен 2006, 15:21
Andrew Sagulin
ZiBiT писал(а):Upper убрал поиск все равно не идет по индексу
Насколько я знаю, запросы типа like '%...' не используют индекс, потому что для поиска по такому условию надо просматривать весь индекс, что может быть дольше, чем прочитать всю таблицу целиком.
Для таких вещей нужно что-нибудь типа full text search. И не спрашивай, где это взять - не знаю.

Добавлено: 22 сен 2006, 15:44
ZiBiT
а с order by не подскажите че делать а то с ним запрос выполняется в 10 раз дольше
запрос без Order By выполняется <1 сек
а с ним как мин сек 12
kdv писал где то в конфиг. менять надо не подскажите где

Добавлено: 23 сен 2006, 12:04
ZiBiT
Переделал связи в запросе без order by

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

 SELECT MAILS.ID as IDM, MAILS.ORDERS, CLIENTS.NAME as CLIENT,Clients.id as IDC, MAILS.NUMMAIL,MAILS.RING as PROSVON, MAILS.FIRM, MAILS.MAN, STREETS.NAME as STREET, MAILS.HOME, MAILS.LETTEROUT, 
MAILS.AREANUM, MAILS.backcl , MAILS.AREALET, MAILS.ADDADR, MAILS.PHONE, SERVICES.NAME as SERVICE1, MAILS.SLIPTIME, MAILS.SLIPMAN, MAILS.SLIPDOLG, MAILS.SLIPPHONE, EXECSOSTS.NAME AS EXECSOT1, MAILS.NUMRECIP, MAILS.EXECSOST, REASONS.NAME as REASON 
, MAILS.courier1 as courier, MAILS.SLIPDATE 
FROM MAILS
inner JOIN STREETS ON (MAILS.STREETOUT = STREETS.ID)
INNER JOIN TARIFFS ON (MAILS.TARIFF = TARIFFS.ID)
INNER JOIN ORDERS ON (MAILS.ORDERS = ORDERS.ID)
INNER JOIN CLIENTS ON (ORDERS.CLIENT = CLIENTS.ID)
inner JOIN EXECSOSTS ON (MAILS.EXECSOST = EXECSOSTS.ID)
inner JOIN SERVICES ON (TARIFFS.SERVICE = SERVICES.ID)
inner JOIN REASONS ON (MAILS.BACKCL = REASONS.ID)
where (UPPER(MAILS.FIRM) Like '%ОПТИМА%')


Plan
PLAN JOIN (CLIENTS NATURAL,ORDERS INDEX (RDB$FOREIGN11),MAILS INDEX (RDB$FOREIGN14),REASONS INDEX (RDB$PRIMARY33),STREETS INDEX (RDB$PRIMARY1),TARIFFS INDEX (RDB$PRIMARY49),SERVICES INDEX (RDB$PRIMARY52),EXECSOSTS INDEX (RDB$PRIMARY25))

Adapted Plan
PLAN JOIN (CLIENTS NATURAL,ORDERS INDEX (INTEG_18),MAILS INDEX (INTEG_22),REASONS INDEX (INTEG_49),STREETS INDEX (INTEG_2),TARIFFS INDEX (INTEG_69),SERVICES INDEX (INTEG_73),EXECSOSTS INDEX (INTEG_39))

------ Performance info ------
Prepare time = 15ms
Execute time = 1s 532ms
Avg fetch time = 85,11 ms
Current memory = 129 185 296
Max memory = 137 605 716
Memory buffers = 30 000
Reads from disk to cache = 42 545
Writes from cache to disk = 0
Fetches from cache = 1 030 503
c order by

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

 Plan
PLAN SORT (JOIN (CLIENTS NATURAL,ORDERS INDEX (RDB$FOREIGN11),MAILS INDEX (RDB$FOREIGN14),REASONS INDEX (RDB$PRIMARY33),STREETS INDEX (RDB$PRIMARY1),TARIFFS INDEX (RDB$PRIMARY49),SERVICES INDEX (RDB$PRIMARY52),EXECSOSTS INDEX (RDB$PRIMARY25)))

Adapted Plan
PLAN SORT (JOIN (CLIENTS NATURAL,ORDERS INDEX (INTEG_18),MAILS INDEX (INTEG_22),REASONS INDEX (INTEG_49),STREETS INDEX (INTEG_2),TARIFFS INDEX (INTEG_69),SERVICES INDEX (INTEG_73),EXECSOSTS INDEX (INTEG_39)))

------ Performance info ------
Prepare time = 0ms
Execute time = 16s 469ms
Avg fetch time = 914,94 ms
Current memory = 129 196 652
Max memory = 137 605 716
Memory buffers = 30 000
Reads from disk to cache = 70 899
Writes from cache to disk = 0
Fetches from cache = 1 729 291

Добавлено: 23 сен 2006, 12:13
ZiBiT
Убрал связи оставил одно поле в запросе

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

SELECT MAILS.ID as IDM
FROM MAILS
where (UPPER(MAILS.FIRM) Like '%ОПТИМА%')
Order by orders, nummail

Plan
PLAN SORT ((MAILS NATURAL))


------ Performance info ------
Prepare time = 0ms
Execute time = 12s 969ms
Avg fetch time = 682,58 ms
Current memory = 130 099 376
Max memory = 137 605 716
Memory buffers = 30 000
Reads from disk to cache = 33 229
Writes from cache to disk = 0
Fetches from cache = 1 791 519
Без Order by

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

Plan
PLAN (MAILS NATURAL)


------ Performance info ------
Prepare time = 0ms
Execute time = 11s 546ms
Avg fetch time = 607,68 ms
Current memory = 129 049 296
Max memory = 137 605 716
Memory buffers = 30 000
Reads from disk to cache = 33 229
Writes from cache to disk = 0
Fetches from cache = 1 791 519
Часовой пояс: UTC+03:00
Страница 1 из 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/