Добрый день. 1С УПП. Учусь писать запросы, но зашла в тупик. Есть два регистра Продажи и ТоварыНаСкладах. В один столбец надо вывести сколько товара продали(количество из регистра Продажи) во второй столбец нужно вывести информацию сколько остатка по этому же товару (количество из ТоварыНаСкладе). Пробовала и через ВТ,и через Вложенный запрос. Последний вариант ОБЪЕДИНЕНИЕ.
Ругается на
Поле объекта не обнаружено (ТоварыНаСкладах)
ОбластьДанные.Параметры.Остатки = ВыборкаДетальныеЗаписи.ТоварыНаСкладах;
Запрос.Текст =
"ВЫБРАТЬ
| Продажи.Номенклатура КАК Номенклатура,
| СУММА(Продажи.Количество) КАК Количество,
| РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ) КАК ДнейПродажи
|ИЗ
| РегистрНакопления.Продажи КАК Продажи
|ГДЕ
| Продажи.Период МЕЖДУ &НачалоПериода И &КонецПериода
|
|СГРУППИРОВАТЬ ПО
| Продажи.Номенклатура
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| ТоварыНаСкладах.Номенклатура,
| СУММА(ТоварыНаСкладах.Количество),
| РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
|ИЗ
| РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах
|
|СГРУППИРОВАТЬ ПО
| ТоварыНаСкладах.Номенклатура
|
|УПОРЯДОЧИТЬ ПО
| Номенклатура
|АВТОУПОРЯДОЧИВАНИЕ";
ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();
Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
ОбластьДанные.Параметры.Товар = ВыборкаДетальныеЗаписи.Номенклатура;
ОбластьДанные.Параметры.Продано = ВыборкаДетальныеЗаписи.Количество;
ОбластьДанные.Параметры.Остатки = ВыборкаДетальныеЗаписи.ТоварыНаСкладах;
ТабДок.Вывести(ОбластьДанные);
КонецЦикла;
ПоказатьРугается на
Поле объекта не обнаружено (ТоварыНаСкладах)
ОбластьДанные.Параметры.Остатки = ВыборкаДетальныеЗаписи.ТоварыНаСкладах;
По теме из базы знаний
- Оптимизация запросов 1С:Предприятие – от теории к практике
- Заметки про запросы. Коллекция
- Работа со схемой запроса
- Планы запросов - это просто! Разбор оптимизаций запросов PostgreSQL на живых примерах
- Комплексный контроль остатков. Для одного или сразу нескольких логически связанных регистров накопления. Универсальное решение уровня данных для контроля не только складских остатков
Ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличетсвоРасход КАК Продано,
Остатки.КоличествоОстаток КАК Остаток
ИЗ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&КонецПериода) КАК Остатки
ПО Продажи.Номенклатура = Остатки.Номенклатура // возможно еще склад нужен и организация
(3)
Есть подозрение, что получится не совсем оптимальный план запроса.
ЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличетсвоРасход КАК Продано,
Остатки.КоличествоОстаток КАК Остаток
ИЗ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.О
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличетсвоРасход КАК Продано,
Остатки.КоличествоОстаток КАК Остаток
ИЗ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.О
Есть подозрение, что получится не совсем оптимальный план запроса.
(4)
Ну вот научили 1С-негов сувать все во временные таблицы. Да, это позволяет в ряде случаев избежать некоторых проблем, но таких случаев осталось немного. И поэтому 1С решила, что научить собирать данные через временные таблицы - это хорошая мина при плохой игре.
Ну а как дело обстоит на самом деле? Не так много вариантов выборки и соединения/объединения данных существует в природе. Упорядоченный список может быть "обойден" с помощью деления пополам, неупорядоченный придется обойти целиком. В данном случае период отсечет лишние данные, а период входит в индекс. А вот товар может в индекс и не входить, поэтому я и написал, что лучше организацию и склад добавить - они также входят в индекс. Там ведь какое дело - поля должны быть те, которые вверху индекса, порядок их совершенно все-равно какой будет - скул уже 20 лет назад научился не обращать на это внимание. И если ым добавим все поля индекса, то получим отличное соединение. Но тут еще одна штука - остатки и обороты хранятся во всех разрезах, поэтому все измерения регистра в остатках - это индексы. Поэтому даже не добавляя организацию и склад мы получим соединение по индексированным полям... Упс...
Но Вы всегда можете предложить вариант еще более оптимальный. Интересно на него погляжу...
Есть подозрение
А кому нужен оптимальный план? Да и какой будет этот оптимальный план? Если то же самое положить во временную таблицу, а потом соединить, думаете будет оптимальнее? Не на столько туп оптимизатор, чтобы не собрать из этих ДВУХ таблиц (упс, их всего две, и обе вполне себе физические - это не соединение с подзапросом при срезе первых/последних, да и с ними сейчас перепилили - срезы тоже хранятся в отдельных таблицах).
Ну вот научили 1С-негов сувать все во временные таблицы. Да, это позволяет в ряде случаев избежать некоторых проблем, но таких случаев осталось немного. И поэтому 1С решила, что научить собирать данные через временные таблицы - это хорошая мина при плохой игре.
Ну а как дело обстоит на самом деле? Не так много вариантов выборки и соединения/объединения данных существует в природе. Упорядоченный список может быть "обойден" с помощью деления пополам, неупорядоченный придется обойти целиком. В данном случае период отсечет лишние данные, а период входит в индекс. А вот товар может в индекс и не входить, поэтому я и написал, что лучше организацию и склад добавить - они также входят в индекс. Там ведь какое дело - поля должны быть те, которые вверху индекса, порядок их совершенно все-равно какой будет - скул уже 20 лет назад научился не обращать на это внимание. И если ым добавим все поля индекса, то получим отличное соединение. Но тут еще одна штука - остатки и обороты хранятся во всех разрезах, поэтому все измерения регистра в остатках - это индексы. Поэтому даже не добавляя организацию и склад мы получим соединение по индексированным полям... Упс...
Но Вы всегда можете предложить вариант еще более оптимальный. Интересно на него погляжу...
(5)
Конечно хорошо бы посмотреть план запроса. Но на сколько я помню, в таблицах Остатки и Обороты данные хранятся на конец месяца (помесячно). Учитывая, что выборка далеко не всегда попадает в нужный интервал, то добавляется соединение с таблицей движений и происходят вычисления. Именно в подзапросах. Не уверен, что индексы останутся. Именно из-за этого и есть рекомендация не соединять виртуальные таблицы с физическими.
Не на столько туп оптимизатор, чтобы не собрать из этих ДВУХ таблиц
Но тут еще одна штука - остатки и обороты хранятся во всех разрезах, поэтому все измерения регистра в остатках - это индексы. Поэтому даже не добавляя организацию и склад мы получим соединение по индексированным полям... Упс...
Конечно хорошо бы посмотреть план запроса. Но на сколько я помню, в таблицах Остатки и Обороты данные хранятся на конец месяца (помесячно). Учитывая, что выборка далеко не всегда попадает в нужный интервал, то добавляется соединение с таблицей движений и происходят вычисления. Именно в подзапросах. Не уверен, что индексы останутся. Именно из-за этого и есть рекомендация не соединять виртуальные таблицы с физическими.
(6)
Но тут какое дело, собственно. Остаток - это сумма всех приходов за минус расходов. Фактически "подзапрос" будет прост, как три копейки: "ВЫБРАТЬ ИзмеренияУчаствующиеВЗапросе, СУММА(Приод) - Сумма(Расход) ИЗ Движения ГДЕ Период МЕЖДУ ДатаПоследнегоВычисленногоИтога И ВерхняяГраница СГРУППИРОВАТЬ ПО ИзмеренияУчаствующиеВЗапросе". Фактически здесь выбираются ВСЕ записи регистра (за указанный период). Допустим, Вы поместите это во временную таблицу, и что произойдет? Система могла бы отфильтровать записи правой таблицы при выборке, но она уже во временной таблице, вся целиком. В итоге мы первый раз прочитали ровно это же во временную таблицу, потом вторые остатки - целиком(!) - положили во вторую временную таблицу, потом соединили вместе по товару. Разницы нет никакой - мы в любом случае прочитали всю таблицу целиком, а в случае с временной таблицей еще и записали ее в TempDB, как будто делать системе больше нечего.
В действительности с даже четырьмя таблицами скул разберется легко. Ведь у оптимизатора цель - найти минимальное количество чтений с диска и минимальное количество вычислений произвести. А если нет разницы, то зачем платить больше?
Учитывая, что выборка далеко не всегда попадает в нужный интервал, то добавляется соединение с таблицей движений и происходят вычисления.
С этим не поспоришь, но для отчетов, обычно, собираются данные по границам периодов, а вот при получении остатков на момент времени - да, остатки от границы собираются подзапросом...
Но тут какое дело, собственно. Остаток - это сумма всех приходов за минус расходов. Фактически "подзапрос" будет прост, как три копейки: "ВЫБРАТЬ ИзмеренияУчаствующиеВЗапросе, СУММА(Приод) - Сумма(Расход) ИЗ Движения ГДЕ Период МЕЖДУ ДатаПоследнегоВычисленногоИтога И ВерхняяГраница СГРУППИРОВАТЬ ПО ИзмеренияУчаствующиеВЗапросе". Фактически здесь выбираются ВСЕ записи регистра (за указанный период). Допустим, Вы поместите это во временную таблицу, и что произойдет? Система могла бы отфильтровать записи правой таблицы при выборке, но она уже во временной таблице, вся целиком. В итоге мы первый раз прочитали ровно это же во временную таблицу, потом вторые остатки - целиком(!) - положили во вторую временную таблицу, потом соединили вместе по товару. Разницы нет никакой - мы в любом случае прочитали всю таблицу целиком, а в случае с временной таблицей еще и записали ее в TempDB, как будто делать системе больше нечего.
В действительности с даже четырьмя таблицами скул разберется легко. Ведь у оптимизатора цель - найти минимальное количество чтений с диска и минимальное количество вычислений произвести. А если нет разницы, то зачем платить больше?
(7) используя временную таблицу можно в ней получить Данные по оборотам. Проиндексировать. Далее в основном запросе использовать соединение с данными из временной таблицы и виртуальной таблице по остаткам и в параметры виртуально таблицы передать полученный ссылки номенклатуры из временной таблицы. Получение будет более оптимальным и понятным оптимизатору.
(8) оптимизатор при четырех таблицах даже методом полного перебора выдаст идеальный результат за сотые доли секунды.
Кажется, что поместить в ВТ и проиндексировать - это очень быстро, но создать индекс - это не бесплатная операция. Фактически, индекс (двоичные дерево) позволяет осуществлять поиск за Log2(N). При сотне позиций разница даже с полным поиском О(N/2) будет не такой уж и серьезной. При этом можно оценить, сколько ресурсов будет потрачено на создание индекса: упорядочивание + индексация - это еще O(Log2(N)). 100*100/2 = 5000, против 100*7*2 = 1400. Я бы не назвал это таким уж большим преимуществом, тем более если принять во внимание время записи данных в TempDB, которое, к сожалению, никак не может быть равно нулю.
Предположу, что оптимизатор на 4-х таблицах все же вообще откажется от временной таблицы и провернет все это в памяти, может даже виртуально проиндексирует набор записей для поиска. Так что есть там помещение в TempDB или нет - будет работать одинаково.
Кажется, что поместить в ВТ и проиндексировать - это очень быстро, но создать индекс - это не бесплатная операция. Фактически, индекс (двоичные дерево) позволяет осуществлять поиск за Log2(N). При сотне позиций разница даже с полным поиском О(N/2) будет не такой уж и серьезной. При этом можно оценить, сколько ресурсов будет потрачено на создание индекса: упорядочивание + индексация - это еще O(Log2(N)). 100*100/2 = 5000, против 100*7*2 = 1400. Я бы не назвал это таким уж большим преимуществом, тем более если принять во внимание время записи данных в TempDB, которое, к сожалению, никак не может быть равно нулю.
Предположу, что оптимизатор на 4-х таблицах все же вообще откажется от временной таблицы и провернет все это в памяти, может даже виртуально проиндексирует набор записей для поиска. Так что есть там помещение в TempDB или нет - будет работать одинаково.
(9) почему не учитываете, что при левом соединении данные из виртуальной таблицы Остатки будут выбраны все и только при соединении будут отсеяны ненужные?
А при соединении со временной таблице с отбором по только нужной номенклатуре данные будут сразу выбраны только нужные. Если разница нужные/ненужные будет большой, то это может сильно сказаться.
А при соединении со временной таблице с отбором по только нужной номенклатуре данные будут сразу выбраны только нужные. Если разница нужные/ненужные будет большой, то это может сильно сказаться.
(11)
Конечно нужно смотреть план запроса.
Я проверил на файловой в консоле запроса разработчиков от 1С. Там получил план. Там как раз наглядно показало, что разница в прочитанных данных из базы в несколько раз отличается. Когда мы в параметрах виртуальной таблицы указываем отбор по конкретному списку номенклатуры, то и выбираются только они. В другом случае - выбираются все данной по остатку.
Нужно еще посмотреть что выдаст sql оптимизатор, но сомневаюсь, что будет сильно по другому.
данные будут выбираться все в любом случае - ограничение лишь по периоду, если период будет указагэн, конечно.
Конечно нужно смотреть план запроса.
Я проверил на файловой в консоле запроса разработчиков от 1С. Там получил план. Там как раз наглядно показало, что разница в прочитанных данных из базы в несколько раз отличается. Когда мы в параметрах виртуальной таблицы указываем отбор по конкретному списку номенклатуры, то и выбираются только они. В другом случае - выбираются все данной по остатку.
Нужно еще посмотреть что выдаст sql оптимизатор, но сомневаюсь, что будет сильно по другому.
(12)
Кстати, как Вы в файловой базе получили план? Техжурнал? Он рисует план запроса к файловой?
В отчете динамическая фильтрация, т.к. запрос СКД перерабатывает в зависимости от установленных отборов. Если поле отбора не имеет отдельного индекса, а в остальных индексах оно не на первом месте (ну или после обязательного разделителя), то все-равно система будет сканировать таблицу (скан кластерного индекса для мелкософтовского скула = скан таблицы), т.е. таблица будет прочитана вся (в пределах периода). Вся прочитана и покладена во временную таблицу или просто вся прочитана и во временную таблицу не покладена.
В SQL есть несколько вариантов соединения = циклический поиск ссылок во второй таблице по первой - так называемый "nested loop", просто проход по индексу (фактически при разнице поля в таблице 1 и таблице 2 происходит инкремент индекса одной из таблиц, а сели равны - то обоих - частая задача на собеседованиях, кстати). Ну и HASH JOIN - затратная по памяти операция, плохо работающая при низкой селективности данных или большом их количестве. Оценка тут проста: в первом случае N*Log2(M), вовтором случае MAX(M, N), в третьем случае N + большой расход памяти для организации хеш-таблицы (у Макконнелла есть в книжке). Если Вы тащите сначала все во временные таблицы и потом индексируете, то получаете чтение всей таблицы, ограниченной периодом, в TempDB, запись туда, создание индекса O(Log2N * N), чтение второй таблицы, ограниченной периодом, запись ее в TempDB, создание индекса O(Log2M * M), потом соединение - MAX(N, M). Вот какая-то такая эффективность будет. Будет ли это быстрее - вопрос.
Когда мы в параметрах виртуальной таблицы указываем отбор по конкретному списку номенклатуры, то и выбираются только они.
Когда указываем период - выбирается этот период, все так.
Кстати, как Вы в файловой базе получили план? Техжурнал? Он рисует план запроса к файловой?
В отчете динамическая фильтрация, т.к. запрос СКД перерабатывает в зависимости от установленных отборов. Если поле отбора не имеет отдельного индекса, а в остальных индексах оно не на первом месте (ну или после обязательного разделителя), то все-равно система будет сканировать таблицу (скан кластерного индекса для мелкософтовского скула = скан таблицы), т.е. таблица будет прочитана вся (в пределах периода). Вся прочитана и покладена во временную таблицу или просто вся прочитана и во временную таблицу не покладена.
В SQL есть несколько вариантов соединения = циклический поиск ссылок во второй таблице по первой - так называемый "nested loop", просто проход по индексу (фактически при разнице поля в таблице 1 и таблице 2 происходит инкремент индекса одной из таблиц, а сели равны - то обоих - частая задача на собеседованиях, кстати). Ну и HASH JOIN - затратная по памяти операция, плохо работающая при низкой селективности данных или большом их количестве. Оценка тут проста: в первом случае N*Log2(M), вовтором случае MAX(M, N), в третьем случае N + большой расход памяти для организации хеш-таблицы (у Макконнелла есть в книжке). Если Вы тащите сначала все во временные таблицы и потом индексируете, то получаете чтение всей таблицы, ограниченной периодом, в TempDB, запись туда, создание индекса O(Log2N * N), чтение второй таблицы, ограниченной периодом, запись ее в TempDB, создание индекса O(Log2M * M), потом соединение - MAX(N, M). Вот какая-то такая эффективность будет. Будет ли это быстрее - вопрос.
(14)
Может я не достаточно подробно описал код с использованием временной таблицей? Я не предлагал тащить в TempDB данные из обоих виртуальных таблиц.
Ориентировочный код, который я предлагал см в (16)
Если Вы тащите сначала все во временные таблицы и потом индексируете, то получаете чтение всей таблицы, ограниченной периодом, в TempDB, запись туда, создание индекса O(Log2N * N), чтение второй таблицы, ограниченной периодом, запись ее в TempDB, создание индекса O(Log2M * M), потом соединение - MAX(N, M).
Может я не достаточно подробно описал код с использованием временной таблицей? Я не предлагал тащить в TempDB данные из обоих виртуальных таблиц.
Ориентировочный код, который я предлагал см в (16)
(14)
Вместе с конфигурацией Библиотека стандартных подсистем идет обработка Консоль запросов. Она позволяет сохранять План выполнения как файл на диске и анализировать полученные данные.
Вот примеры:
1.
План запроса:
Кстати, как Вы в файловой базе получили план? Техжурнал? Он рисует план запроса к файловой?
Вместе с конфигурацией Библиотека стандартных подсистем идет обработка Консоль запросов. Она позволяет сохранять План выполнения как файл на диске и анализировать полученные данные.
Вот примеры:
1.
ВЫБРАТЬ
РегистрНакопления3Обороты.Номенклатура КАК Номенклатура,
РегистрНакопления3Обороты.КоличествоОборот КАК КоличествоОборот,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
РегистрНакопления.РегистрНакопления3.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК РегистрНакопления3Обороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаОкончания, ) КАК ТоварыНаСкладахОстатки
ПО РегистрНакопления3Обороты.Номенклатура = ТоварыНаСкладахОстатки.НоменклатураПлан запроса:
Fields:(
T1.Период,
T1._UseTotals,
T1._ActualPeriod,
T1._UseSplitter,
T1._MinPeriod,
T1._MinCalculatedPeriod
)
(T1) RANGE SCAN USING INDEX (_ACCUMRGOPT552_1) (1 fields)
Statistics: RecordsScanned = 1, ParseTime = 1, ExecuteTime = 0, BuffersMemory = 25847, ResultRecords = 1, RecordSize = 51
Fields:(
T1.Период,
T1._UseTotals,
T1._ActualPeriod,
T1._UseSplitter,
T1._MinPeriod,
T1._MinCalculatedPeriod
)
(T1) RANGE SCAN USING INDEX (_ACCUMRGOPT143_1) (1 fields)
Statistics: RecordsScanned = 1, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25847, ResultRecords = 1, RecordSize = 51
Fields:(
T1.Fld548RRef,
T1.Fld550Turnover_,
T3.Fld141Balance_
)
NESTED SELECT FULL SCAN
(
Fields:(
T2.Номенклатура,
SUM(CASE WHEN (T2._RecordKind = 0) THEN T2.Количество ELSE -T2.КоличествоEND)
)
РегистрНакопления.РегистрНакопления3 (T2) RANGE SCAN USING INDEX (_ACCUMRG547_1) (1 fields)
WHERE
(T2.Активность = TRUE)
GROUPING
)
NESTED OUTER LOOP BY SELECT RANGE SCAN USING INDEX (AUTOINDEX) (1 fields)
(
Fields:(
T4.Fld139RRef,
SUM(T4.Fld141Balance_)
)
NESTED SELECT FULL SCAN
(
Fields:(
T5.Номенклатура,
SUM(T5.Количество)
)
(T5) RANGE SCAN USING INDEX (_ACCUMRG142_BYDIMS_TRRN) (1 fields)
WHERE
(T5.Количество <> 0)
AND
(T5.Количество <> 0)
GROUPING
UNION ALL
Fields:(
T6.Номенклатура,
cast(SUM(CASE WHEN (T6._RecordKind = 0) THEN -T6.Количество ELSE T6.КоличествоEND) as NUMERIC(27, 2))
)
РегистрНакопления.ТоварыНаСкладах (T6) RANGE SCAN USING INDEX (РегистрНакопления.ТоварыНаСкладах_ByPeriod_TRN) (1 fields)
WHERE
(T6.Активность = TRUE)
GROUPING
)
GROUPING
)
WHERE
(T1.Fld548RRef = T3.Fld139RRef)
Statistics: RecordsScanned = 34, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
ПоказатьT1.Период,
T1._UseTotals,
T1._ActualPeriod,
T1._UseSplitter,
T1._MinPeriod,
T1._MinCalculatedPeriod
)
(T1) RANGE SCAN USING INDEX (_ACCUMRGOPT552_1) (1 fields)
Statistics: RecordsScanned = 1, ParseTime = 1, ExecuteTime = 0, BuffersMemory = 25847, ResultRecords = 1, RecordSize = 51
Fields:(
T1.Период,
T1._UseTotals,
T1._ActualPeriod,
T1._UseSplitter,
T1._MinPeriod,
T1._MinCalculatedPeriod
)
(T1) RANGE SCAN USING INDEX (_ACCUMRGOPT143_1) (1 fields)
Statistics: RecordsScanned = 1, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25847, ResultRecords = 1, RecordSize = 51
Fields:(
T1.Fld548RRef,
T1.Fld550Turnover_,
T3.Fld141Balance_
)
NESTED SELECT FULL SCAN
(
Fields:(
T2.Номенклатура,
SUM(CASE WHEN (T2._RecordKind = 0) THEN T2.Количество ELSE -T2.КоличествоEND)
)
РегистрНакопления.РегистрНакопления3 (T2) RANGE SCAN USING INDEX (_ACCUMRG547_1) (1 fields)
WHERE
(T2.Активность = TRUE)
GROUPING
)
NESTED OUTER LOOP BY SELECT RANGE SCAN USING INDEX (AUTOINDEX) (1 fields)
(
Fields:(
T4.Fld139RRef,
SUM(T4.Fld141Balance_)
)
NESTED SELECT FULL SCAN
(
Fields:(
T5.Номенклатура,
SUM(T5.Количество)
)
(T5) RANGE SCAN USING INDEX (_ACCUMRG142_BYDIMS_TRRN) (1 fields)
WHERE
(T5.Количество <> 0)
AND
(T5.Количество <> 0)
GROUPING
UNION ALL
Fields:(
T6.Номенклатура,
cast(SUM(CASE WHEN (T6._RecordKind = 0) THEN -T6.Количество ELSE T6.КоличествоEND) as NUMERIC(27, 2))
)
РегистрНакопления.ТоварыНаСкладах (T6) RANGE SCAN USING INDEX (РегистрНакопления.ТоварыНаСкладах_ByPeriod_TRN) (1 fields)
WHERE
(T6.Активность = TRUE)
GROUPING
)
GROUPING
)
WHERE
(T1.Fld548RRef = T3.Fld139RRef)
Statistics: RecordsScanned = 34, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
(14)
2. Код:
2. Код:
ВЫБРАТЬ
РегистрНакопления3Обороты.Номенклатура КАК Номенклатура,
РегистрНакопления3Обороты.КоличествоОборот КАК КоличествоОборот
ПОМЕСТИТЬ ВТ_Обороты
ИЗ
РегистрНакопления.РегистрНакопления3.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК РегистрНакопления3Обороты
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
//////////////////////////////////////////////////////////// Показать
(14) из-за ограничений сообщения прикладываю файл.
Обратите внимание на статистику
1. Statistics: RecordsScanned = 34, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
2. Statistics: RecordsScanned = 10, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
Обратите внимание на статистику
1. Statistics: RecordsScanned = 34, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
2. Statistics: RecordsScanned = 10, ParseTime = 0, ExecuteTime = 0, BuffersMemory = 25664, ResultRecords = 2, RecordSize = 60
Прикрепленные файлы:
План запроса к коду 2.txt
(17) Ну посмотреть внимательнее, то в первом случае у нас три сканирования, а во втором 4, при том в первом случае у нас два первых сканирования возвращают одну запись, а третье - 34 записи. Во втором случае 1, 5, 1, 10. В первом случае у нас нет лишней сортировки для создания индекса, во втором случае засчет индекса мы получаем меньше итоговых строк.
На SQL этот план запроса по всей видимости будет другим (или Вы это на SQL уже сделали?)
На SQL этот план запроса по всей видимости будет другим (или Вы это на SQL уже сделали?)
(20)
Вообще, стоит посмотреть, какую оценку времени система даст при большом количестве данных каждому сканированию. Может так оказаться, что итоговая сборка будет меньше времени занимать, чем создание индекса во временной таблице.
Что будет при очень больших данных в остатках?
А на сколько могут быть в остатках большие данные? Поверьте, платформа их будет дольше визуализировать, чем выбирать из базы.
Вообще, стоит посмотреть, какую оценку времени система даст при большом количестве данных каждому сканированию. Может так оказаться, что итоговая сборка будет меньше времени занимать, чем создание индекса во временной таблице.
(21)
я тут и не буду спорить без подробных данных для анализа.
Такое ощущение, что мы говорим о разных вещах. Я исходил из анализа кода в (3). Т.е. данные за период выбираем все из виртуальной таблицыОстатки Обороты. Далее левым соединением присоединяется виртуальная таблица Остатки.
Теперь вопрос... Что будет если за небольшой период продано немного, но на остатках очень много данных? Платформа вообще ничего не будет визуализировать из Остатки. Из нее только количество будет браться.
Вообще, стоит посмотреть, какую оценку времени система даст при большом количестве данных каждому сканированию. Может так оказаться, что итоговая сборка будет меньше времени занимать, чем создание индекса во временной таблице.
я тут и не буду спорить без подробных данных для анализа.
А на сколько могут быть в остатках большие данные? Поверьте, платформа их будет дольше визуализировать, чем выбирать из базы.
Такое ощущение, что мы говорим о разных вещах. Я исходил из анализа кода в (3). Т.е. данные за период выбираем все из виртуальной таблицы
Теперь вопрос... Что будет если за небольшой период продано немного, но на остатках очень много данных? Платформа вообще ничего не будет визуализировать из Остатки. Из нее только количество будет браться.
(22)
Но вообще нужно смотреть на план в SQL. Поиск в данных - это задача, которая решается в общем-то ограниченным количеством способов, время которых разнится. Да, самое быстрое для большого количества данных - это соединение по индексированному полю, ни в одном запросе план не показал соединение по индексу, ибо нет индекса к полю соединения (номенклатура), поэтому в соединение в идеале было бы добавить организацию и склад, что должно привести к соединению по этому индексу. И тут уже скорость может быть очень даже и безо всех этих ВТ.
Что будет если за небольшой период продано немного, но на остатках очень много данных?
В итоге мы покладем во временную таблицу овер дофига данных, построим по ней индекс, потом соединим с небольшим количеством продаж. Если временную таблицу не мутить, то мы также выберем все из первой таблицы и соединим ее со второй, очень небольшой. В общем, если таблицы будут неодинаковы, то у нас перекос будет или в излишнем покладании с созданием индекса во временную таблицу, или с очень долгим NESTED LOOP при поиске во второй таблице всех значений первой таблицы. И у нас в любом случае индекс только с одной стороны (с левой), и поиск в правой таблице по неиндексированному полю - это полный перебор до победного (NESTED SELECT FULL SCAN и в вашем варианте с ВТ, и в варианте без нее)...
Но вообще нужно смотреть на план в SQL. Поиск в данных - это задача, которая решается в общем-то ограниченным количеством способов, время которых разнится. Да, самое быстрое для большого количества данных - это соединение по индексированному полю, ни в одном запросе план не показал соединение по индексу, ибо нет индекса к полю соединения (номенклатура), поэтому в соединение в идеале было бы добавить организацию и склад, что должно привести к соединению по этому индексу. И тут уже скорость может быть очень даже и безо всех этих ВТ.
(23)
я и говорю, что мы о разных вещах говорим. Во временную таблицу мы положим данные оборотов продаж, которых мало. Их в любом случае получать все. А вот из виртуальной таблицы остатки (которых много) мы выбираем или все (первый случай, который просто соединение виртуальных таблиц), или же отберем только по нужной номенклатуре (записей получим сразу мало и только нужные).
В итоге мы покладем во временную таблицу овер дофига данных, построим по ней индекс, потом соединим с небольшим количеством продаж
я и говорю, что мы о разных вещах говорим. Во временную таблицу мы положим данные оборотов продаж, которых мало. Их в любом случае получать все. А вот из виртуальной таблицы остатки (которых много) мы выбираем или все (первый случай, который просто соединение виртуальных таблиц), или же отберем только по нужной номенклатуре (записей получим сразу мало и только нужные).
(24) кстати, Вы заметили, что при использовании временной таблицы происходит полное сканирование в блоке WHERE, где Т6.Номенклатура проверяется на вхождение во временную таблицу. Такого поведения без временной таблицы нет. Мне кажется, что для парочки товаров тут ничего страшного, но если товаров будет много, то эта конструкция очень неоптимальна:
А по поводу сути, то у нас любая из таблиц может быть больше - хоть продажи, хоть остатки. Переверните ситуацию - и получится то, о чем я говорю.
T6.Номенклатура IN(
(SELECT
Fields:(
T7._Q_001_F_000RRef
)
#Te4487eef12414bdcb8031ddb040b6e70 (T7)(TWICE) FULL SCAN
WITHOUT DUPLICATES
)
(SELECT
Fields:(
T7._Q_001_F_000RRef
)
#Te4487eef12414bdcb8031ddb040b6e70 (T7)(TWICE) FULL SCAN
WITHOUT DUPLICATES
)
А по поводу сути, то у нас любая из таблиц может быть больше - хоть продажи, хоть остатки. Переверните ситуацию - и получится то, о чем я говорю.
(26)
Первый - FULL SCAN:
Второй - SEEK:
Все это был тот самый NESTED LOOP без индекса и с ним.
Но если у нас соединяемые поля в индексе, то мы не ищем во второй таблице значения из первой. Вы ведь понимаете, что либо мы бежим по первой таблице (и тут нам плевать на индекс первой - левой - таблицы), и ищем каждое значение во второй (и тут нам индекс крайне важен, т.к. он экономит время поиска); либо мы бежим по обоим таблицам сразу! Да, просто увеличиваем индексы обоих таблиц, когда данные одинаковы, или увеличиваем индекс той таблицы, где данные меньше. В итоге мы просто сравниваем значения по текущему индексу, и от результата этого сравнения увеличиваем или оба индекса, или один из них. В итоге нам не нужно бегать по второй таблице даже Log2M N раз, у нас на все про все уйдет всего N сравнений.
А уже по ней ищется по индексу вхождение.
Возвращаясь к напечатанному, то есть очень ограниченное количество вариантов поиска.
Первый - FULL SCAN:
Для каждого А ИЗ Элементы Цикл
Если А = Искомое Тогда Возврат Истина КонецЕсли;
КонецЦикла;
Возврат Ложь;Второй - SEEK:
А = 1;
Размер = Элементы.Количество() / 2;
Пока Размер > 0 Цикл
Если Элементы[А] = Искомое Тогда Возврат Истина КонецЕсли;
Если Элементы[А] < Искомое Тогда А = А + Размер Иначе А = А - Размер КонецЕсли;
Размер = Размер / 2;
КонецЦикла;
Возврат ЛожьВсе это был тот самый NESTED LOOP без индекса и с ним.
Но если у нас соединяемые поля в индексе, то мы не ищем во второй таблице значения из первой. Вы ведь понимаете, что либо мы бежим по первой таблице (и тут нам плевать на индекс первой - левой - таблицы), и ищем каждое значение во второй (и тут нам индекс крайне важен, т.к. он экономит время поиска); либо мы бежим по обоим таблицам сразу! Да, просто увеличиваем индексы обоих таблиц, когда данные одинаковы, или увеличиваем индекс той таблицы, где данные меньше. В итоге мы просто сравниваем значения по текущему индексу, и от результата этого сравнения увеличиваем или оба индекса, или один из них. В итоге нам не нужно бегать по второй таблице даже Log2M N раз, у нас на все про все уйдет всего N сравнений.
(28)
Fields:(
ISNULL(cast(SUM(T4.Количество) as NUMERIC(27, 2)),0)
)
(T4) RANGE SCAN USING INDEX (_ACCUMRG142_BYDIMS_TRRN) (2 fields)
WHERE
T4.Номенклатура IN(
(SELECT
Fields:(
T5._Q_001_F_000RRef
)
#Te4487eef12414bdcb8031ddb040b6e70 (T5)(TWICE) FULL SCAN
WITHOUT DUPLICATES
) )
AND
(T4.Количество <> 0) Показать
Full Scan относится к получению данных из временной таблицы. Они обходятся последовательно с полным сканированием. И уже по ним идет RANGE SCAN USING INDEX, для выборки данных из таблицы Остатки. Грубо говоря у нас ЛЕВАЯ таблица это данные временной таблицы, а ПРАВАЯ это таблица Остатки.
Fields:(
ISNULL(cast(SUM(T4.Количество) as NUMERIC(27, 2)),0)
)
(T4) RANGE SCAN USING INDEX (_ACCUMRG142_BYDIMS_TRRN) (2 fields)
WHERE
T4.Номенклатура IN(
(SELECT
Fields:(
T5._Q_001_F_000RRef
)
#Te4487eef12414bdcb8031ddb040b6e70 (T5)(TWICE) FULL SCAN
WITHOUT DUPLICATES
) )
AND
(T4.Количество <> 0)
Full Scan относится к получению данных из временной таблицы. Они обходятся последовательно с полным сканированием. И уже по ним идет RANGE SCAN USING INDEX, для выборки данных из таблицы Остатки. Грубо говоря у нас ЛЕВАЯ таблица это данные временной таблицы, а ПРАВАЯ это таблица Остатки.
(31) для левой таблицы индекс нужен только тогда, когда он совпадает по полям соединения со второй таблицей. Это сильно ускоряет выполнение запроса (третий вариант). В любом другом случае это будет , но в случае наличия по полю индекса во второй таблице, цикл будет отрабатывать за Log2M, а если его нет, то за M (в пределе, если данные из первой таблицы не будут найдены во второй, то придется пройтись по всем записям и вернуть "ложь"). Поэтому многие не любят NESTED LOOP, а их тут у нас воз и маленькая тележка в обоих вариантах )))
(35) кстати, по поводу реляционной алгебры, то существует :
1. Алгоритм соединения вложенными циклами - NESTED LOOP.
2. Алгоритм соединения хеширование - HASH JOIN.
3. Алгоритм соединения слиянием сортированных списков - даже не знаю, как он называется в SQL, но уже три задания делал тестовых в разных конторах (1С интернациональ, МКБ и еще какие-то типа связных или около того - любят они это втыкать, но работать к ним так и не пошел - не отпускают с конторы, замучили контрофферами).
Так вот 1-й алгоритм - он у нас везде, кроме индекса по периоду - для него отдельный индекс. В обоих запросах это индекс юзается (RANGE). 2-й алгоритм работает быстро только на очень небольших выборках (есть люди, которые думают, что это не так, но скул по памяти небесконечен, да и на больших выборках число коллизий резко возрастает, и на 30% это становится менее эффективно, чем NESTED LOOP в индексе), третий - это в случае, когда обе таблицы проиндексированы по полям соединения - очень быстро - за один проход, нет ограничений на размер соединяемых таблиц. Вот для этого не только в правой таблице должен быть индекс по соединяемым полям, а в обоих. Если только в правой - будет NESTED LOOP.
Вообще, иногда хорошо работает соединение проиндексированных двух временных таблиц, ну или ВТ с таблицей с аналогичными индексами, но при выборке в ВТ всех таблиц время на сортировку очень легко может превысить время полного поиска только в одной таблице. В принципе, быстрая сортировка имеет О(N * Log2N), полный поиск - O(N * M) - все значения из N в M. И при небольших выборках это вообще не о чем, а при больших разница узе может быт существенной, но тащить небольшие таблицы во временные, как и большие - контрпродуктивно, т.к. они в любом случае будут прочитаны целиком, а это является самой долгой операцией - прочитать с диска, выделить для этого память, выгрузить старые данные... А если это еще и индексация временной таблицы, то выделить память на индекс, отсортировать данные и поместить в индекс.
Временные таблицы должны использоваться для небольших фильтрующих выборок - это их основное предназначение. Но 1С-негов учат сначала половину базы засунуть во временную таблицу, а потом уже в финальной выборке все отфильтировать. Слава богам, что оптимизатор достаточно умный, чтобы не тащить в ВТ все подряд, но при большом количестве временных таблиц даже оптимизатору от мелкомягких становится плохо и он просто прерывает цикл, останавливаясь далеко не на самом быстром варианте.
1. Алгоритм соединения вложенными циклами - NESTED LOOP.
2. Алгоритм соединения хеширование - HASH JOIN.
3. Алгоритм соединения слиянием сортированных списков - даже не знаю, как он называется в SQL, но уже три задания делал тестовых в разных конторах (1С интернациональ, МКБ и еще какие-то типа связных или около того - любят они это втыкать, но работать к ним так и не пошел - не отпускают с конторы, замучили контрофферами).
Так вот 1-й алгоритм - он у нас везде, кроме индекса по периоду - для него отдельный индекс. В обоих запросах это индекс юзается (RANGE). 2-й алгоритм работает быстро только на очень небольших выборках (есть люди, которые думают, что это не так, но скул по памяти небесконечен, да и на больших выборках число коллизий резко возрастает, и на 30% это становится менее эффективно, чем NESTED LOOP в индексе), третий - это в случае, когда обе таблицы проиндексированы по полям соединения - очень быстро - за один проход, нет ограничений на размер соединяемых таблиц. Вот для этого не только в правой таблице должен быть индекс по соединяемым полям, а в обоих. Если только в правой - будет NESTED LOOP.
Вообще, иногда хорошо работает соединение проиндексированных двух временных таблиц, ну или ВТ с таблицей с аналогичными индексами, но при выборке в ВТ всех таблиц время на сортировку очень легко может превысить время полного поиска только в одной таблице. В принципе, быстрая сортировка имеет О(N * Log2N), полный поиск - O(N * M) - все значения из N в M. И при небольших выборках это вообще не о чем, а при больших разница узе может быт существенной, но тащить небольшие таблицы во временные, как и большие - контрпродуктивно, т.к. они в любом случае будут прочитаны целиком, а это является самой долгой операцией - прочитать с диска, выделить для этого память, выгрузить старые данные... А если это еще и индексация временной таблицы, то выделить память на индекс, отсортировать данные и поместить в индекс.
Временные таблицы должны использоваться для небольших фильтрующих выборок - это их основное предназначение. Но 1С-негов учат сначала половину базы засунуть во временную таблицу, а потом уже в финальной выборке все отфильтировать. Слава богам, что оптимизатор достаточно умный, чтобы не тащить в ВТ все подряд, но при большом количестве временных таблиц даже оптимизатору от мелкомягких становится плохо и он просто прерывает цикл, останавливаясь далеко не на самом быстром варианте.
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот