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

[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

[dimitr]

1) создать необходимые индексы
2) перенести все INNER выше LEFT

[ZiBiT]

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

[dimitr]

таблицы соединяются сначала по left а по том к ним подкл. маленькие табл. по Inner

если "к ним" - это к левым табличкам джойнов, то ты зря так думаешь. А если это обозначает "к результатам джойна", то тогда положение INNER-а монопенисуально.

[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 а он почему то не ищет по этому индексу

[kdv]

блин. кроме того, A LEFT JOIN B = B RIGHT JOIN A
www.ibase.ru/devinfo/joins.htm

Создал индекс в табличке mails по полю firm а он почему то не ищет по этому индексу

а почему он должен искать ПО ФУНКЦИИ?

[dimitr]

все условия джойнов идут через PK/FK? Или по условиям связи вообще нет индексов?

[ZiBiT]

Да все связи идуд по индексам
Где все ID - PK
А остальн. FK

[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 '%ГОЛДЕН%') 

[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]

[ZiBiT]

Не подскажите что можно сделать с Order by

[kdv]

Не подскажите что можно сделать с Order by

сортировку в конфиге настроить.

[kdv]

where (UPPER(MAILS.FIRM) Like '%ГОЛДЕН%')

и еще. если вот такого рода условие отбора существенно уменьшает число записей в MAILS, то вариантов два

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

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

[ZiBiT]

сортировку в конфиге настроить.

а неподскажите где именно настраивать

[ZiBiT]

Upper убрал поиск все равно не идет по индексу
Если убрать Upper и Like Поиск идет по Индексу

[dimitr]

с таким шаблоном в LIKE - и не будет

[Andrew Sagulin]

Upper убрал поиск все равно не идет по индексу

Насколько я знаю, запросы типа like '%...' не используют индекс, потому что для поиска по такому условию надо просматривать весь индекс, что может быть дольше, чем прочитать всю таблицу целиком.
Для таких вещей нужно что-нибудь типа full text search. И не спрашивай, где это взять - не знаю.

[ZiBiT]

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

[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

[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