Заметки про запросы. Последовательность.

Опубликовал Николай Васильев (vasilev2015) в раздел Программирование - Практика программирования

Рассуждения, основанные на контрпримерах. Определяем последовательность операций с помощью одной только консоли запросов. Версия 2.

Хорошо ли мы знаем синтаксис команд языка запросов 1С ?

Попробуйте расставить порядок выполнения операций в запросе:

ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ <..> <Имя таблицы>.<Имя поля>, СУММА( <..> ) ИЗ <Имя таблицы> КАК <Имя таблицы> ВНУТРЕННЕЕ СОЕДИНЕНИЕ <Имя таблицы> ПО <Условие> ГДЕ <Условие> СГРУППИРОВАТЬ ПО <Имя таблицы>.<Имя поля> ИМЕЮЩИЕ СУММА( <..> ) = 0 УПОРЯДОЧИТЬ ПО <Имя поля>
Порядок выполнения операций очень важен для понимания оптимизации, влияет на результат запроса. Определим порядок выполнения операций с помощью одной только консоли запросов. Планы запросов, оптимизатор и прочее в этой статье не рассматриваются. Результаты в конце статьи. Дальше будут использоваться вспомогательные таблицы ТабПример1, ТабПример2. Обратите внимание на слово «Все» в ТабПример2.
Чтобы проверить рассуждения, нужно копировать текст запроса временной таблицы и текст запроса выборки данных в консоль, выполнить запрос с полученным текстом. Результаты на файловых и серверных базах одинаковые.
В 
ВЫБРАТЬ 2 КАК Поле1, 4 КАК Поле2, 0 КАК Поле3 ПОМЕСТИТЬ ТабПример1 ОБЪЕДИНИТЬ ВЫБРАТЬ 3/2, 3, 0 ;
Поле1 Поле2 Поле3
2 4 0
3/2 3 0
ВЫБРАТЬ 1 КАК Поле1, 3 КАК Поле2, 0 КАК Поле3 ПОМЕСТИТЬ ТабПример2 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1, 3, 0 ; 
Поле1 Поле2 Поле3
1 3 0
1 3 0

Соединение таблиц  VS. Проверка условия ГДЕ  

Комментарий. Сначала выполняется Соединение таблиц. Об этом написано в учебниках SQL. Поэтому перед соединением необходимо тщательно подготовить таблицы: убрать «ненужные» записи, подключить индексы. Контрпример на сравнение очередности "Соединение таблиц" - "Проверка условия ГДЕ" пока не придумал.

Проверка условия ГДЕ  VS. Расчет агрегатных функций без группировки

ВЫБРАТЬ МАКСИМУМ(ТабПример1.Поле1) КАК Поле1, МАКСИМУМ(ТабПример1.Поле2) КАК Поле2 ИЗ ТабПример1 КАК ТабПример1 ГДЕ ТабПример1.Поле2 = 3

Результат: Поле1 = 1.5, Поле2 = 3. 

Комментарий. Сначала выполняется Проверка "ГДЕ". Иначе был бы пустой результат.

Расчет агрегатных функций без группировки VS. Опции ПЕРВЫЕ <ХХ>– Упорядочить По.

ВЫБРАТЬ ПЕРВЫЕ 1 СУММА(ТабПример1.Поле1) КАК Поле1, СУММА(ТабПример1.Поле2) КАК Поле2 ИЗ ТабПример1 КАК ТабПример1 УПОРЯДОЧИТЬ ПО Поле1

Результат: Поле1 = 3.5, Поле2 = 7. 

Комментарий. Сначала выполняется расчет агрегатных функций без группировки, затем команды ПЕРВЫЕ <ХХ>– Упорядочить По. 

Группировка с расчетом агрегатных функций VS. опции ПЕРВЫЕ <ХХ>– Упорядочить По

ВЫБРАТЬ ПЕРВЫЕ 1 СУММА(ТабПример1.Поле1) КАК Поле1, СУММА(ТабПример1.Поле2) КАК Поле2, ТабПример1.Поле3 ИЗ ТабПример1 КАК ТабПример1 СГРУППИРОВАТЬ ПО ТабПример1.Поле3 УПОРЯДОЧИТЬ ПО Поле2

Результат: Поле1 = 3.5, Поле2 = 7, Поле3 = 0. 

Комментарий. Сначала выполняется группировка с расчетом агрегатных функции, затем команды ПЕРВЫЕ <ХХ>– Упорядочить По. Сразу после расчета агрегатных функций проверяется условие «Имеющие». Контрпример на сравнение очередности "Имеющие"-"Различные" пока не придумал.

Группировка с расчетом агрегатных функций VS. опция Различные

ВЫБРАТЬ РАЗЛИЧНЫЕ ТабПример2.Поле3, СУММА(ТабПример2.Поле1) КАК Поле1, СУММА(ТабПример2.Поле2) КАК Поле2 ИЗ ТабПример2 КАК ТабПример2 СГРУППИРОВАТЬ ПО ТабПример2.Поле3

Результат: Поле1 = 2, Поле2 = 6.  

Комментарий. Сначала выполняется Группировка с расчетом агрегатных функций. Конечно, применение в одном запросе этих операций бессмысленно.

опция Различные VS. опции ПЕРВЫЕ <ХХ>– Упорядочить По

ВЫБРАТЬ ТабПример2.Поле1, ТабПример2.Поле2, ТабПример2.Поле3 ПОМЕСТИТЬ ТабПример3 ИЗ ТабПример2 КАК ТабПример2 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ТабПример1.Поле1, ТабПример1.Поле2, ТабПример1.Поле3 ИЗ ТабПример1 КАК ТабПример1 ; ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ 3 ТабПример3.Поле1, ТабПример3.Поле2, ТабПример3.Поле3 ИЗ ТабПример3 КАК ТабПример3 УПОРЯДОЧИТЬ ПО Поле1
Результат, строка 1: Поле1 = 1, Поле2 = 3, Поле3 = 0.  
Результат, строка 2: Поле1 = 1.5, Поле2 = 3, Поле3 = 0.  
Результат, строка 3: Поле1 = 2, Поле2 = 4, Поле3 = 0.

Комментарий. Сначала выполняется Опция "Различные", затем опции  Первые <ХХ> -Упорядочить По. Если был бы другой порядок выполнения, то в результате получилось бы не три строки, а две.

Смотрите следующий запрос:

ВЫБРАТЬ ПЕРВЫЕ 3 ТабПример3.Поле1, ТабПример3.Поле2, ТабПример3.Поле3 ИЗ ТабПример3 КАК ТабПример3 УПОРЯДОЧИТЬ ПО ТабПример3.Поле1
Результат, строка 1: Поле1 = 1, Поле2 = 3, Поле3 = 0.  
Результат, строка 2: Поле1 = 1, Поле2 = 3, Поле3 = 0.  
Результат, строка 3: Поле1 = 1.5, Поле2 = 3, Поле3 = 0.

Упорядочить По VS. ПЕРВЫЕ <ХХ> 

ВЫБРАТЬ ПЕРВЫЕ 1 ТабПример1.Поле1 КАК Поле1, ТабПример1.Поле2 КАК Поле2 ИЗ ТабПример1 КАК ТабПример1 УПОРЯДОЧИТЬ ПО Поле1

Результат: Поле1 = 1,5, Поле2 = 3. 

Комментарий. Сначала выполняется Упорядочить По. Смотрите следующий запрос.

ВЫБРАТЬ ПЕРВЫЕ 1 ТабПример1.Поле1 КАК Поле1, ТабПример1.Поле2 КАК Поле2 ИЗ ТабПример1 КАК ТабПример1

Результат: Поле1 = 2, Поле2 = 4. 

Вывод: операции SQL выполняются в последовательности

  1. Соединение
  2. Проверка условия "ГДЕ"
  3. Группировка с расчетом агрегатных функций
  4. Проверка условия «Имеющие»
  5. Опция Различные
  6. Опция Упорядочить по
  7. Опция Первые <ХХ>

Послесловие

Пока я писал статью, узнал, что упорядочивание может происходить по агрегатным функциям, что вычисление агрегатных функций без группировки существенно отличается от группировки с вычислением агрегатных функций. Спасибо всем, кто проявил интерес к теме, давал конструктивные комментарии. Мне подсказали, что аналогичная информация по SQL содержится

https://msdn.microsoft.com/ru-ru/library/ms189499%28v=sql.120%29.aspx

https://msdn.microsoft.com/en-us/library/ms189499.aspx

Порядок для SQL в общем, совпадает с 1С, хотя слова другие :-))

  1. FROM
  2. ON

  3. JOIN

  4. WHERE

  5. GROUP BY

  6. WITH CUBE или WITH ROLLUP

  7. HAVING

  8. SELECT

  9. DISTINCT

  10. ORDER BY

  11. TOP

См. также

Добавить вознаграждение
Комментарии
1. ash (ashvik) 27.05.16 15:02 Сейчас в теме
А где же условия виртуальных таблиц?
2. Николай Васильев (vasilev2015) 204 27.05.16 15:21 Сейчас в теме
Да, спасибо, формирование виртуальных таблиц происходит еще раньше соединений.
3. Сергей Рудаков (fishca) 1034 27.05.16 15:54 Сейчас в теме
4. Николай Васильев (vasilev2015) 204 27.05.16 16:52 Сейчас в теме
Очень ценный комментарий. Статья будет дополнена.
5. Sergey Andreev (starik-2005) 833 27.05.16 21:22 Сейчас в теме
Есть мнение, что SQL делает так, как ему лучше будет (в соответствии со статистикой). Главное - чтобы результат был таким, какой требует пользователь. Не знаю, как сейчас, но раньше если во временную таблицу вытаскиваешь всю таблицу, а потом агрегируешь ее и берешь оттуда только количество значений, то запрос вообще не выполнялся (на мелкософт скуле по крайней мере).
zinal; asved.ru; ProgrammistC; +3 Ответить 2
6. Роман Корюкин (ProgrammistC) 26 28.05.16 21:13 Сейчас в теме
Статистикой пользуется анализатор запроса и на ее основе выбирает оптимальный план запроса, планы запроса надо читать для разных случаев
7. Сергей Старых (tormozit) 4105 29.05.16 12:42 Сейчас в теме
Утверждение
Сначала выполняются команды ПЕРВЫЕ <ХХ>– Упорядочить По, затем агрегатные функции с группировкой

Ошибочно. Оператор TOP (ПЕРВЫЕ) выполняется последним (логически), а ORDER (УПОРЯДОЧИТЬ) предпоследним (логически).
igo1; Chif13; igormiro; Dementor; JesteR; Yashazz; _also; awk; GorDAn; 1cWin; cool.vlad4; mrBart; Diversus; +13 Ответить 1
8. Sergey Andreev (starik-2005) 833 29.05.16 18:21 Сейчас в теме
(7) tormozit, ИМХО, автор имел ввиду, что топ и ордер выполняются сначала, а агрегирование - потом. Хотя... Понимать - вообще штука сложная. Может на форуме сделать кнопку "Вы уверены, что поняли текст, на который отвечаете. и хотите ответить именно так, как написали?" Какому-то школьнику за это дали вкусных плюшек.
9. Сергей Старых (tormozit) 4105 29.05.16 21:54 Сейчас в теме
(8) В операторе УПОРЯДОЧИТЬ доступны псевдонимы (результаты) полей оператора ВЫБРАТЬ, в том числе выражений с агрегатными функциями, а также непосредственно в операторе УПОРЯДОЧИТЬ можно указывать выражения, содержащие агрегатные функции.
Dach; herfis; cool.vlad4; +3 Ответить
10. Ийон Тихий (cool.vlad4) 41 29.05.16 22:44 Сейчас в теме
(8) starik-2005, не надо ерничать. как обработается запрос в реальности зависит и от запроса, и от движка обрабатывающего sql (как обрабатывает запросы же файловая версия 1С, я без понятия).поэтому и есть понятие logical processing order, о котором и написал tormozit. чтобы иметь представления при написании запросов.
например см https://msdn.microsoft.com/en-us/library/ms189499.aspx
FR OM
ON
JOIN
WH ERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SEL ECT
DISTINCT
ORDER BY
TOP
т.е. топ вообще на последнем месте.
смотрим запрос автора, но при другой таблице
ВЫБРАТЬ
2 КАК Поле1,
4 КАК Поле2,
0 КАК Поле3

ПОМЕСТИТЬ ТабПример1

ОБЪЕДИНИТЬ

ВЫБРАТЬ
1,
3,
0

ОБЪЕДИНИТЬ

ВЫБРАТЬ
1,
34,
0
ОБЪЕДИНИТЬ

ВЫБРАТЬ
2,
34,
0

;
ВЫБРАТЬ ПЕРВЫЕ 1
ТабПример1.Поле1 КАК Поле1,
СУММА(ТабПример1.Поле2) КАК Поле2
ИЗ ТабПример1 КАК ТабПример1
СГРУППИРОВАТЬ ПО ТабПример1.Поле1
УПОРЯДОЧИТЬ ПО Поле2
...Показать Скрыть

Результат будет 1, 37 . т.е. сначала произошло агрегирование и только потом TOP . понятно, что в целях оптимизации в реальности TOP может выполниться сначала. но говорить надо именно о логической последовательности выполнения. там же по ссылке
Note that the actual physical execution of the statement is determined by the query processor and the order may vary fr om this list.

автор был бы прав в случае если бы указал, что у меня на таком-то сервере, только в этом запросе, в данной таблице, сначала выполняется...и т.д. и то, как у него происходит сначала упорядочивание по агрегатному полю, а потом агрегация, непонятно. но мало ли. может оптимизировалось выполнение так запроса.
Дмитрий74Чел; +1 Ответить 3
11. Максим Кузнецов (Makushimo) 148 30.05.16 06:10 Сейчас в теме
Автор не ссылался на планы запросов, оптимизатор и прочее.
Значит в этой теме [возможно] глубоко не разбирался.
А то что он провел исследование и поделился результатами весьма неплохо.
К тому же в основном его выводы верны.

Порекомендуйте коллеге материал для изучения планов запросов, оптимизатора и прочее
и тогда он [возможно] перепишет статью более корректно.

12. Канат Джумадылов (Fox-trot) 49 30.05.16 07:17 Сейчас в теме
(11) Makushimo, ты не поверишь, но есть БД у которых нет ни оптимизаторов, ни планов
13. Максим Кузнецов (Makushimo) 148 30.05.16 09:56 Сейчас в теме
(12) Fox-trot, которые из тех,что использует 1С?
я не ерничаю, а правда кроме SQL ничего и не знаю.
14. Николай Васильев (vasilev2015) 204 30.05.16 10:11 Сейчас в теме
(10) cool.vlad4, Скорее всего, Вы правы: топ на последнем месте. Проверю еще раз, изменю статью. Спасибо.
15. Антонио (Fragster) 639 30.05.16 11:52 Сейчас в теме
Вот тут майкрософт дает хорошее пояснение:
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP // этого в 1с нет
HAVING
SELECT
DISTINCT
ORDER BY
TOP

остается вопрос с "итоги ... по ...", которые делаются 1сом программно, вероятно это происходит уже с результатом всех операций с БД
16. Sergey Andreev (starik-2005) 833 30.05.16 13:49 Сейчас в теме
(10) cool.vlad4, Вы не поверите, но об этом я говорил аж в (5). Но уметь читать - та еще наука. Некоторым проще писать )))
17. Ийон Тихий (cool.vlad4) 41 30.05.16 13:55 Сейчас в теме
(16) starik-2005, ну да, ну да. человек , который пишет сначала
автор имел ввиду, что топ и ордер выполняются сначала, а агрегирование - потом.

но имеет ввиду противоположное, будет тут нас на форуме читать и писать учить. тебе на форум изучения букварей надо.
PS не надоело флудить?
18. Sergey Andreev (starik-2005) 833 30.05.16 14:00 Сейчас в теме
19. Ийон Тихий (cool.vlad4) 41 30.05.16 14:01 Сейчас в теме
(16) starik-2005, это тот самый редкий случай , когда starik-2005 знает лучше , что имел в виду автор, нежели сам автор. см.
(10) cool.vlad4, Скорее всего, Вы правы: топ на последнем месте. Проверю еще раз, изменю статью. Спасибо.
20. Ийон Тихий (cool.vlad4) 41 30.05.16 14:03 Сейчас в теме
(18) starik-2005, по теме есть что возразить или будем все флудить и писать оффтопный бред?
21. Канат Джумадылов (Fox-trot) 49 30.05.16 14:26 Сейчас в теме
(13) Makushimo, пусть так, но это все равно не имеет никакого отношения к синтаксису
22. Павел Алексеенко (qwinter) 470 30.05.16 21:27 Сейчас в теме
(12) Fox-trot, оптимизатор запросов есть даже внутри платформы. А еще есть оптимизатор СКД, со своими блекджеками и ш***)))
23. Канат Джумадылов (Fox-trot) 49 30.05.16 23:06 Сейчас в теме
(22) qwinter, пусть так, но это все равно не имеет никакого отношения к синтаксису )
24. Максим Кузнецов (Makushimo) 148 01.06.16 06:17 Сейчас в теме
(23) Fox-trot, этот аргумент ничем не перекрыть :-)) ну просто уложил на обе лопатки.
красава.
25. Maximilian Alekseevich (1cmax) 151 01.06.16 08:21 Сейчас в теме
(22) qwinter, c преферансом и куртизанками
26. Антон Стеклов (asved.ru) 31 01.06.16 09:26 Сейчас в теме
Статья не имеет никакого отношения к оптимизации и хайлоаду. Это теория программирования в чистом виде.

Потому, что узнать, какие операции и в какой последовательности выполняются в действительности, можно только посмотрев план запроса. Но вот если различный порядок исполнения операций запроса допускает различные результаты, то результат исполнения плана будет логически соответствовать изложенному порядку

Иначе при выполнении

1. Соединение
2. Проверка условия "ГДЕ"

SQL был бы вынужден прочитать все содержимое всех таблиц даже при существовании идеально покрывающих индексов. Вы представляете себе, во что бы вылился запрос чтения, скажем, документа с табличной частью, если их ввведено несколько миллионов?

Фактически SQL первым делом стремится применить имеющиеся условия к исходным таблицам.

Смысла в порядке выполнения TOP вообще никакого нет, он просто прерывает исполнение плана, получив достаточно строк результата.
Dach; Sergey.Noskov; Makushimo; +3 Ответить
27. Илья Козлов (Dem1urg) 46 01.06.16 11:03 Сейчас в теме
Сначала выполняется Соединение таблиц. Об этом написано в учебниках SQL


Не совсем.
Не буду утверждать про все СУБД, но MS SQL если в условии WHERE (ГДЕ) одна из частей условия является константой, а соединение INNER (внутреннее), то движок MS SQL сначала выполняет предварительную фильтрацию таблицы и только потом уже соединение.
28. Антонио (Fragster) 639 01.06.16 12:06 Сейчас в теме
(27) Dem1urg, физическое выполнение и "логическое" - немного разные вещи. если результат от разного порядка действий одинаковый - оптимизатор СУБД может извращаться как ему удобно, но логический порядок определен в (15), он не меняется и задается программистом.
29. Сергей JesteR (JesteR) 108 02.06.16 00:07 Сейчас в теме
(5) starik-2005, ТормозИТ не зря в скобочках написал ЛОГИЧЕСКИ.
Не путайте логическое исполнение и физическое. В статье автор описывает логическое исполнение запросов.
30. Alexander Speshilov (speshuric) 882 02.06.16 01:27 Сейчас в теме
(29) JesteR, Есть нюанс. Оптимизатор может нашаманить план так, что, например, будете ловить деление на 0 или другие арифметические ошибки, хотя "логически" эта строка уже должна быть отфильтрованной. Причем ловить нестабильно от статистики. Но, да, логический порядок обработки программист знать обязан. Но применительно к 1С его ломают дополнительные возможности 1С (которые эмулируются) например:
  • составные типы (да, я до сих пор их люблю, хотя уже 2 года на 1С не пишу),
  • виртуальные таблицы
  • (expr1, exp2) В (ВЫБРАТЬ)
  • в иерархии
  • команды построителю и прочий наш сахер синтаксический
После них в SQL такой милый фарш иногда выходит, что внятно предсказать (да еще для всех СУБД) могут только астрологи-телепаты (причем с опытом 10+ лет разработки на 1С и SQL)