|
v7: 1sqlite Долго выполняется запрос | ☑ | ||
---|---|---|---|---|
0
wsxedc83
24.11.12
✎
17:24
|
Имеется сильно допиленная ТиС. Есть документ, задача которого - распределять поступающий товар по многочисленным заказам поставщику. Есть запрос, выбирающий эти заказы для каждого товара. Ситуация осложняется тем, что в одном заказе может присутствовать несколько поступающих товаров, и поступающий товар тоже может присутствовать в нескольких заказах. Кроме того, запрос учитывает и сроки годности заказанного и поступающего товара. Уверен, что запрос далеко не оптимален - т.к. при выполнении запроса для каждой строки 450-строчного документа время выполнения составляет около 2 минут.
Прошу посмотреть на наличие грубых ошибок при составлении запроса. Заранее спасибо! //============================== Функция ПолучитьЗаказы(Номенк,ТабЗаказов, ТекущаяГодность,сч) Если флSQLЗагружен=0 Тогда Возврат ""; КонецЕсли; ДатаЗапроса=Мин(ДатаДок,ПолучитьДатуТА()); база = СоздатьОбъект("SQLiteBase"); база.Открыть(":memory:"); запрос = база.НовыйЗапрос(); запрос.Подставлять("ДатаИтогвНаКонецПрошлогоПериода", НачМесяца(НачМесяца(ДатаЗапроса) - 1)); запрос.Подставлять("ДатаНачалаПериодаДвижений", НачМесяца(ДатаЗапроса)); запрос.Подставлять("ДатаОстатков", ДатаЗапроса); запрос.Подставлять("ТекГодность", ТекущаяГодность); запрос.Подставлять("Номенк", Номенклатура); база.УложитьТЗ(ТабЗаказов,"ВыбЗаказы"); Возврат запрос.ВыполнитьЗапрос(" |select | Заказы.ЗаказПоставщику [Заказ :Документ.ЗаказПоставщику], | Заказы.Номенклатура [Номенклатура :Справочник.Номенклатура], | Движ.ЗаявкаПокупателя [Заявка :Документ.ЗаявкаПокупателя], | Заявка.ГодностьС ГодностьС, | Заявка.ГодностьПО ГодностьПО, | ifnull(ЗаказыДокумента.КоличествоВЗаказах,0), | Заказы.КоличествоПриход, | (Заказы.КоличествоПриход - ifnull(ЗаказыДокумента.КоличествоВЗаказах,0)) Количество |FROM |( | SELECT | ЗаказПоставщику, | Номенклатура, | КоличествоПриход | FROM | РегистрИтоги_Заказы WHERE period = :ДатаИтогвНаКонецПрошлогоПериода | union all | select | ЗаказПоставщику, | Номенклатура, | case when debkred = 0 then КоличествоПриход else -КоличествоПриход end | FROM | Регистр_Заказы Движения | | inner join | Журнал on Журнал.iddoc = Движения.iddoc | WHERE Журнал.date >= :ДатаНачалаПериодаДвижений | and Журнал.date <= :ДатаОстатков | and Журнал.ЗаказыФр = 1 | ) Заказы | LEFT JOIN ( | SELECT | ЗаказПоставщику Док, | ЗаявкаПокупателя ЗаявкаПокупателя | FROM | Регистр_ЗаказыЗаявки) Движ on Движ.Док = Заказы.ЗаказПоставщику | LEFT JOIN ( | SELECT | iddoc, | ГодностьС, | ГодностьПО | FROM | ДокументСтроки_ЗаявкаПокупателя WHERE Номенклатура = :Номенк) Заявка on Заявка.iddoc = Движ.ЗаявкаПокупателя | LEFT JOIN ( | SELECT | Заказ, | КоличествоЗаказ КоличествоВЗаказах | FROM | ВыбЗаказы WHERE Номенклатура = :Номенк ) ЗаказыДокумента on ЗаказыДокумента.Заказ = Заказы.ЗаказПоставщику |WHERE Номенклатура = :Номенк |AND ГодностьС <= :ТекГодность |AND ГодностьПО >= :ТекГодность |group by Заказы.ЗаказПоставщику, Номенклатура, Движ.ЗаявкаПокупателя, ГодностьС, ГодностьПО, ЗаказыДокумента.КоличествоВЗаказах,Заказы.КоличествоПриход //|HAVING SUM(Заказы.КоличествоПриход - ifnull(ЗаказыДокумента.КоличествоВЗаказах,0)) > 0 |"); КонецФункции // ПолучитьЗаказы |
|||
1
kiruha
24.11.12
✎
17:28
|
А с какого перепоя он должен выполняться быстро, если отбор
WHERE Номенклатура = :Номенк и т.п. только во внешнем ? |
|||
2
wsxedc83
24.11.12
✎
17:34
|
Ок. Воткну во все. Что-нибудь ещё?
|
|||
3
wsxedc83
24.11.12
✎
17:36
|
Даже не так - не что-нибудь, а ЧТО ещё? Ибо подозреваю, что много там всего... ((
|
|||
4
kiruha
24.11.12
✎
17:37
|
(2)
Во все не надо Вот это например LEFT JOIN ( | SELECT | iddoc, | ГодностьС, | ГодностьПО | FROM | ДокументСтроки_ЗаявкаПокупателя WHERE Номенклатура = :Номенк) Заявка on Заявка.iddoc = Движ.ЗаявкаПокупателя означает что будут выбраны все тч документов и только потом отбор по iddoc ///////// В регистр добавить быструю обработку движений по номенклатуре и отбор (в 8 на виртуальные табл остатков тоже отбор вешают) |
|||
5
Эмбеддер
24.11.12
✎
17:39
|
по скорости как это будет не скажу но запрос можно записать проще, например вместо
LEFT JOIN ( | SELECT | Заказ, | КоличествоЗаказ КоличествоВЗаказах | FROM | ВыбЗаказы WHERE Номенклатура = :Номенк ) ЗаказыДокумента on ЗаказыДокумента.Заказ = Заказы.ЗаказПоставщику можно просто написать LEFT JOIN ВыбЗаказы ЗаказыДокумента ON Номенклатура = :Номенк and ЗаказыДокумента.Заказ = Заказы.ЗаказПоставщику |
|||
6
Эмбеддер
24.11.12
✎
17:40
|
т.е. есть смысл во вложенный запрос помещать только если в нем есть группировка
|
|||
7
kiruha
24.11.12
✎
17:40
|
(5)
+1 по скорости эта часть запроса намного быстрее |
|||
8
Эмбеддер
24.11.12
✎
17:45
|
особенно учитывая, что это не MSSQL (там разницы не будет), а всего лишь SQLite
|
|||
9
wsxedc83
24.11.12
✎
18:09
|
(5) (6) - спасибо! Переделал!
(4) отбор движений поставил в обоих регистрах - и в Заказы и в ЗаказыЗаявки Пробовал переделать так: LEFT JOIN ( | SELECT | iddoc, | ГодностьС, | ГодностьПО | FROM | ДокументСтроки_ЗаявкаПокупателя WHERE Номенклатура = :Номенк AND AND Заказать > 0) Заявка on Заявка.iddoc = Движ.ЗаявкаПокупателя - отрабатывает те же 2 минуты. (( Про виртуальные таблицы остатков и отбор на них в восьмерке - это я не понял... (( |
|||
10
wsxedc83
24.11.12
✎
18:10
|
Ой. AND два раза скопировал. У меня в запросе, конечно, один раз
|
|||
11
Эмбеддер
24.11.12
✎
18:13
|
вот такое условие
ДокументСтроки_ЗаявкаПокупателя WHERE Номенклатура = :Номенк AND Заказать > 0 будет перебирать все документы заявки надо хотя бы журнал к нему прицепить join'ом и отобрать по периоду можно ли в документе отбор для поля номенклатура включить, я не помню. документ можно заменить регистром там точно отбор включается |
|||
12
Эмбеддер
24.11.12
✎
18:17
|
11+ но если убрать вложенный запрос из (9) там как раз все нормально будет - условие iddoc=ЗаявкаПокупателя исключит лишние проверки номенклатуры...
|
|||
13
wsxedc83
24.11.12
✎
18:24
|
(11) Спасибо! Понял!
|
|||
14
wsxedc83
24.11.12
✎
18:34
|
(12) Вариант такой:
|LEFT JOIN ДокументСтроки_ЗаявкаПокупателя Заявка | ON iddoc = Движ.ЗаявкаПокупателя | AND Номенклатура = :Номенк | AND Заказать > 0 отрабатывает уже меньше минуты! Спасибо! |
|||
15
wsxedc83
24.11.12
✎
18:50
|
Итоговый запрос, с учетом большинства ошибок и включенным отбором по номенклатуре в регистрах отрабатывает около 45 секунд. Это по 10 строк в секунду. Ну там правда кое-какая обвязка ещё выполняется, так что, мне кажется это, наверное, предел скорости.
Сам запрос: //======================================= Функция ПолучитьЗаказы(Номенк,ТабЗаказов, ТекущаяГодность,сч) Если флSQLЗагружен=0 Тогда Возврат ""; КонецЕсли; Если ТабЗаказов.КоличествоСтрок()=0 Тогда ТабЗаказов.НоваяСтрока(); КонецЕсли; ДатаЗапроса=Мин(ДатаДок,ПолучитьДатуТА()); база = СоздатьОбъект("SQLiteBase"); база.Открыть(":memory:"); запрос = база.НовыйЗапрос(); запрос.Подставлять("ДатаИтогвНаКонецПрошлогоПериода", НачМесяца(НачМесяца(ДатаЗапроса) - 1)); запрос.Подставлять("ДатаНачалаПериодаДвижений", НачМесяца(ДатаЗапроса)); запрос.Подставлять("ДатаОстатков", ДатаЗапроса); запрос.Подставлять("ТекГодность", ТекущаяГодность); запрос.Подставлять("Номенк", Номенклатура); база.УложитьТЗ(ТабЗаказов,"ВыбЗаказы"); Возврат запрос.ВыполнитьЗапрос(" |select | Заказы.ЗаказПоставщику [Заказ :Документ.ЗаказПоставщику], | Заказы.Номенк [Номенклатура :Справочник.Номенклатура], | Движ.ЗаявкаПокупателя [Заявка :Документ.ЗаявкаПокупателя], | Заявка.ГодностьС ГодностьС, | Заявка.ГодностьПО ГодностьПО, | ifnull(ЗаказыДокумента.КоличествоЗаказ,0), | Заказы.КоличествоПриход, | (Заказы.КоличествоПриход - ifnull(ЗаказыДокумента.КоличествоЗаказ,0)) Количество //| (sum(Заказы.КоличествоПриход) - ЗаказыДокумента.КоличествоВЗаказах) Количество |FROM |( | SELECT | ЗаказПоставщику, | Номенклатура Номенк, | КоличествоПриход | FROM | РегистрИтоги_Заказы WHERE period = :ДатаИтогвНаКонецПрошлогоПериода | AND Номенклатура = :Номенк | union all | select | ЗаказПоставщику, | Номенклатура Номенк, | case when debkred = 0 then КоличествоПриход else -КоличествоПриход end | FROM | Регистр_Заказы Движения | inner join | Журнал on Журнал.iddoc = Движения.iddoc | WHERE Журнал.date >= :ДатаНачалаПериодаДвижений | and Журнал.date <= :ДатаОстатков | and Журнал.ЗаказыФр = 1 | ) Заказы | LEFT JOIN ( | SELECT | ЗаказПоставщику Док, | ЗаявкаПокупателя ЗаявкаПокупателя | FROM | Регистр_ЗаказыЗаявки WHERE Номенклатура = :Номенк) Движ | on Движ.Док = Заказы.ЗаказПоставщику | LEFT JOIN ДокументСтроки_ЗаявкаПокупателя Заявка | ON iddoc = Движ.ЗаявкаПокупателя | AND Номенклатура = :Номенк | LEFT JOIN | ВыбЗаказы ЗаказыДокумента | ON ЗаказыДокумента.Заказ = Заказы.ЗаказПоставщику | and НоменклатураЗаказов = :Номенк |WHERE Заказы.Номенк = :Номенк |AND ГодностьС <= :ТекГодность |AND ГодностьПО >= :ТекГодность |group by Заказы.ЗаказПоставщику, Номенклатура, |Движ.ЗаявкаПокупателя, ГодностьС, ГодностьПО, |ЗаказыДокумента.КоличествоЗаказ,Заказы.КоличествоПриход //|HAVING SUM(Заказы.КоличествоПриход - ifnull(ЗаказыДокумента.КоличествоВЗаказах,0)) > 0 |"); КонецФункции // ПолучитьЗаказы Большое спасибо всем кто помог! |
|||
16
wsxedc83
24.11.12
✎
18:51
|
Вот блин... Странно, что код весь под кат не ушел...
|
|||
17
Эмбеддер
24.11.12
✎
18:56
|
условие WHERE Заказы.Номенк = :Номенк можно внутрь перенести "inner join Журнал ..."
|
|||
18
wsxedc83
24.11.12
✎
19:10
|
(17) Да, действительно ж! Это я должен был и сам увидеть, каюсь...
450 строк 35 секунд. Спасибо! |
|||
19
Ёпрст
26.11.12
✎
09:12
|
case when debkred = 0 then КоличествоПриход else -КоличествоПриход end
вот так никогда не пиши |
|||
20
Ёпрст
26.11.12
✎
09:13
|
inner join
| Журнал on Журнал.iddoc = Движения.iddoc вот так тоже - ты же воткнул галку быстрая обработка движений - соединение с журналом не нужно, в табличке движений регистра теперь всё есть |
|||
21
Ёпрст
26.11.12
✎
09:14
|
group by ....Заказы.КоличествоПриход ?!!!
уверен, что именно это тебе нужно ? Может сумму лучше ? |
|||
22
Эмбеддер
26.11.12
✎
16:41
|
(19) а в этой строке что не так?
|
|||
23
Ёпрст
26.11.12
✎
16:42
|
(22) использование case, заместо арифметической операции, что гораздо быстрее
|
|||
24
Эмбеддер
27.11.12
✎
12:08
|
(23) напиши плиз оба варианта (хорошо и плохо), я вижу только один с case в обоих случаях одинаковый
|
|||
25
Ёпрст
27.11.12
✎
12:17
|
(24)
1. case when debkred = 0 then КоличествоПриход else -КоличествоПриход end 2. (1-2*debkred)*КоличествоПриход |
|||
26
Эмбеддер
29.11.12
✎
22:28
|
(25) второй вариант быстрее? я думал что первый. тоже использовал оба этих варианта в разное время
|
|||
27
Ёпрст
30.11.12
✎
09:25
|
(26) быстрее
|
|||
28
Ёпрст
30.11.12
✎
09:25
|
можешь замерить
|
|||
29
Ёпрст
30.11.12
✎
09:25
|
case всегда медленнее будет
|
|||
30
Эмбеддер
30.11.12
✎
14:16
|
(29) раньше замечал что вариант
case xxx when 0 then быстрее чем case when xxx = 0 then только прогресс не стоит на месте, могли и поправить))) |
|||
31
Ёпрст
30.11.12
✎
14:26
|
(30) дык в п.2 вообще case нет
|
Форум | Правила | Описание | Объявления | Секции | Поиск | Книга знаний | Вики-миста |