Переключение на Главную Страницу Страницы: [1]  ОтправитьПечать
Очень популярная тема (более 25 ответов) оптимизация запроса для формы списка справочника с остатком (число прочтений - 6269 )
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
оптимизация запроса для формы списка справочника с остатком
05. Октября 2016 :: 13:46
Печать  
Форма списка справочника "Товары" переписана на прямой запрос на табличном поле с поставщиком данных ODBC. В форме присутствует колонка с остатком по выбранному складу. Часть запроса выглядит так:

FROM $Справочник.Товары as Спр (nolock)
left join $РегистрОстатки.Остатки(
                                   ,
                                   ,
                             Склад = ?ТекущийСклад(14,9)
                                   ,
                             (Товар),(Остаток)
                                                           ) остаток on остаток.Товар = Спр.ID

После открытия периода в начале месяца форма списка "подвисает" при открытии и листании. Вместо моментального отображения информации, примерно минуту выполняется запрос для каждой порции данных. Ночью выполняется обновление статистики и форма снова летает. Всё бы ничего, но объёмы данных выросли и теперь такие зависания могут возникать и в течение дня. Начал разбираться в чём причина. Сделал копию базы, открыл период, статистику не обновлял. Смотрю план выполнения запросов.  С правильной статистикой поиск по кластерному индексу регистра остатков с таким seek predicates: Prefix: [book1_SQL].[dbo].[RG1501].PERIOD; [book1_SQL].[dbo].[RG1501].SP1511 = Scalar Operator('2016-10-01 00:00:00.000'); Scalar Operator([book1_SQL].[dbo].[SC10].[ID] as [Спр].[ID])
А с тухлой статистикой - так: Prefix: [book2].[dbo].[RG1501].PERIOD = Scalar Operator('2016-11-01 00:00:00.000')

Можно ли как-то повлиять на это кроме запуска обновления статистики по расписанию? Сейчас настроил обновление раз в два часа, но она выполняется минут 10 и нагрузку даёт нехилую - не выход это.
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #1 - 05. Октября 2016 :: 14:22
Печать  
каким по порядку идет измерение Товар?
если не первым, то стоит ли галка отбор итогов?
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #2 - 05. Октября 2016 :: 16:24
Печать  
В том то и дело, что первым. У нас база самописная, могу ручками добавить индекс конечно, но может другой выход есть?
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #3 - 05. Октября 2016 :: 17:50
Печать  
Лучше бы добавить... селективность по товару значительно выше, должно быть не просто быстрее, а очень очень быстрее.
С тухлой статистикой у тебя оптимизатор считает что ему без разницы есть склад или нет (потом типа отфильтрует) т.к. складов мало и он путается, а если будет индекс по товару, предполагаю, оптимизатор так не будет делать.
Как вариант использовать указание индекса WITH (INDEX (незнаюкаконутебятамзавется)) https://technet.microsoft.com/ru-ru/library/bb677261(v=sql.100).aspx
А зачем FROM $Справочник.Товары as Спр (nolock) left join $РегистрОстатки.Остатки( ?
В товарах обычно значительно быть больше позиций, чем есть на остатках.
Может стоит наоборот? Товары брать из регистра, и left join к ним товары, если требуется по их реквизитам дополнительный фильтр (это в том случае, если индекса в регистре по товару нет).
  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #4 - 06. Октября 2016 :: 03:45
Печать  
С тухлой статистикой и без индекс используется один и тот же, про хинт WITH знаю, но тут он, видимо, не поможет.
По порядку таблиц. Это запрос для формы списка справочника. Показывать нужно все элементы. А склад, теоретически, вообще может быть не выбран.
Попробую добавить доп.индекс.
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #5 - 06. Октября 2016 :: 06:14
Печать  
доп индекс не поможет, т.к. уже есть необходимый для этой задачи
  

1&&2&&3
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #6 - 06. Октября 2016 :: 06:25
Печать  
чему равна опция "max degree of parallelism" ?
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #7 - 06. Октября 2016 :: 07:05
Печать  
Да, доп. индекс не помог.
Max degree of parallelism сейчас стоит 0. На сервере 2 Xeon E5645 2,4 Ghz. Я ставил эксперименты, пробовал разные значения, с 0 в целом работает лучше. При попытке распараллеливать запросы какие-то тяжёлые отчёты работали шустрее, но многие жаловались что обычная работа по ощущениям стала менее комфортной и неспешной.
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #8 - 06. Октября 2016 :: 07:12
Печать  
я использую 1
1с рекомендует 1
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #9 - 06. Октября 2016 :: 07:12
Печать  
Хорошо, попробую
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #10 - 06. Октября 2016 :: 07:13
Печать  
интересно увидеть весь план "с тухлой статистикой"
  

1&&2&&3
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #11 - 06. Октября 2016 :: 07:16
Печать  
и до кучи, статистику можно обновлять исключительно для одной таблицы или даже для одного индекса
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #12 - 06. Октября 2016 :: 07:56
Печать  
10 минут обновляется статистика по одной таблице остатков, я это и использую. Ночью статистика по всей базе обновляется за 3,5 часа.
rg1501.sqlplan
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #13 - 06. Октября 2016 :: 09:27
Печать  
max degree of parallelism = 1
тоже скорее всего тут не поможет, так как, судя по плану, запрос и без этого в 1 поток выполняется
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #14 - 06. Октября 2016 :: 09:57
Печать  
Да, я это заметил. В копии базы, над которой ставил эксперименты, сейчас запрос снова выполняется быстро, хотя я ничего не делал. И план правильный. Видимо стоимость этой пары планов очень близка
  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #15 - 06. Октября 2016 :: 10:25
Печать  
я у себя потестил это запрос
у меня тоже seek только по полю period, если есть условие по складу, но выполняется моментально
без условия по складу - по period+Товар
  

1&&2&&3
Наверх
 
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #16 - 06. Октября 2016 :: 10:33
Печать  
Прикрепляю скрины планов, разница только в seek predicates, как я и говорил. Ну и итог: при правильном плане actual number of rows - 0 (в начале списка товаров остатков нет), при неправильном - больше 2 миллионов (я так понимаю, все остатки на ТА в разрезе складов). Получается, никак повлиять на такое поведение нельзя?
https://yadi.sk/i/Sw2tLTrrwM639
https://yadi.sk/i/1pKWfVsvwM68Y
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #17 - 06. Октября 2016 :: 11:54
Печать  
trad писал(а) 06. Октября 2016 :: 10:25:
я у себя потестил это запрос
у меня тоже seek только по полю period, если есть условие по складу, но выполняется моментально
без условия по складу - по period+Товар

Это так и должно быть, поэтому с чего оно тормозит не очень понятно.

pavel_tr А попробуй использовать не jeft join, а просто join. Что нибудь поменяется?
  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #18 - 06. Октября 2016 :: 12:34
Печать  
С join работает быстрее. Не моментально, но значительно быстрее (в правильной статистикой - 0 сек., с тухлой - 19 сек., с join - 2 сек.). Вместо 2 миллионов строк в соединении участвует 41 тысяча
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #19 - 06. Октября 2016 :: 13:39
Печать  
pavel_tr писал(а) 06. Октября 2016 :: 12:34:
С join работает быстрее. Не моментально, но значительно быстрее (в правильной статистикой - 0 сек., с тухлой - 19 сек., с join - 2 сек.). Вместо 2 миллионов строк в соединении участвует 41 тысяча

19c это кошмар. Да и 2 тоже. Сколько у тебя строк в остатках?  Ужас
left везде убрал или только там? Остатки нужны на определенную дату? Если нет то можно отказаться от виртуальных таблиц, запрос совсем простой будет.
  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #20 - 06. Октября 2016 :: 14:37
Печать  
Остатки хранятся в разрезе партий, на ТА для обозначенного в запросе склада 40985 записей.
Для теста оригинальный запрос сильно облегчил, оставил минимум полей и таблиц. 19 сек - это запрос по таблице товаров и регистру остатков.
Остатки нужны всегда на ТА, думал отказаться от виртуальной таблицы, но сейчас-то она разворачивается в правильный запрос, вроде ничего лишнего - смысл?

В остатках на ТА по всем складам 1 209 050 записей
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #21 - 06. Октября 2016 :: 15:53
Печать  
А ОбратныйРасчетОтТА включен? Я так понимаю что виртуальные таблицы при запросе на дату ТА должны выкинуть 2 лишних джойна.
  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #22 - 06. Октября 2016 :: 16:30
Печать  
Нет, не включал. У меня не ODBCRecordset, а ПоставщикДанныхODBC.MSSQL - данные выводятся в табличном поле
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #23 - 06. Октября 2016 :: 18:34
Печать  
pavel_tr писал(а) 06. Октября 2016 :: 16:30:
Нет, не включал. У меня не ODBCRecordset, а ПоставщикДанныхODBC.MSSQL - данные выводятся в табличном поле

Забыл что это свойство там не доступно ;(
Хотя там все равно внутри recordset...
Попробуй без виртуальных таблиц. 2с ну никак не пригодно для табличного поля. Табличное поле работает отлично, но вот ограничение выборки с такойто по такуюто запись, что должно ускорять работу, не всегда понимает sql сервер и пересчитывает всю таблицу целиком, и это почти всегда медленно.
  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #24 - 06. Октября 2016 :: 19:11
Печать  
Собственно, 19 секунд - время выполнения запроса в QA. Текст запроса взят из отладки и там стоит select top 49. В полном запросе от виртуальных таблиц откажусь, но тормоза от этого не кончатся ((
  
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #25 - 06. Октября 2016 :: 20:02
Печать  
1с для того чтобы такого не было сделала отдельный регистр остатков (без партий) Подмигивание

Есть у меня убитая конфа где ПартииНаличие вообще почти не закрываются, и чинить это я там не собираюсь... Накреативили разные умники с моим напарником всякое, вот пусть он и чинит...
Для того чтобы подобное работало с приемлемй скоростью врубил индекс по Номенклатуре и вычисляю остаток отдельным запросом при выводе строки ТП. Там мне еще и партии нужны, но по ним индекс я поднимать не стал, хватило по Номенклатуре. Иначе никак не шевелится.

Это табличное поле... т.е. ограничения топ49 то есть, а sql все равно может вычислять все, и только потом ограничивать по топ49  Печаль


« Последняя редакция: 06. Октября 2016 :: 22:28 - Djelf »  
Наверх
www  
IP записан
 
pavel_tr
Senior Member
****
Отсутствует



Сообщений: 278
Местоположение: Казань
Зарегистрирован: 14. Октября 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #26 - 07. Октября 2016 :: 04:19
Печать  
Да, занятно...
Переделал на получение остатков при выводе строки ТП. Так работает быстро. Не очень красиво, конечно, но что делать
« Последняя редакция: 07. Октября 2016 :: 06:27 - pavel_tr »  
Наверх
 
IP записан
 
trad
1c++ power user
1c++ donor
1c++ moderator
Отсутствует



Сообщений: 3046
Местоположение: Киров
Зарегистрирован: 23. Мая 2006
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #27 - 07. Октября 2016 :: 06:53
Печать  
можно получение остатка еще в корреляционный подзапрос перенести, тоже скорее всего будет сикать по обоим полям
  

1&&2&&3
Наверх
 
IP записан
 
Djelf
God Member
*****
Отсутствует


Ubuntu + wine@etersoft
+ 1C 7.7

Сообщений: 631
Местоположение: Питер
Зарегистрирован: 02. Ноября 2007
Пол: Мужской
Re: оптимизация запроса для формы списка справочника с остатком
Ответ #28 - 07. Октября 2016 :: 18:48
Печать  
trad писал(а) 07. Октября 2016 :: 06:53:
можно получение остатка еще в корреляционный подзапрос перенести, тоже скорее всего будет сикать по обоим полям

может сработать. скорости не сильно прибавит, но будет красивее...
эх... нету такого в sqlite ;(
« Последняя редакция: 07. Октября 2016 :: 21:37 - Djelf »  
Наверх
www  
IP записан
 
Переключение на Главную Страницу Страницы: [1] 
ОтправитьПечать