доброго времени суток, уважаемые друзья.
есть несколько таблиц размером по 300-400 Гб в базе, из них мы делаем выборку с группировкой.

какой запрос отработает быстрее?

выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
объединить
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2

или

выбрать
выборка.измерение1
из
(выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
объединить все
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2) как выборка

сгруппировать по измерение1

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

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

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

(0) вместо того что-бы играть в угадайку, посмотри планы запросов. вопросы все отпадут.

складывать результаты во вложенную таблицу, это ты смешно сказал. а вообще по моему план запроса одинаковый будет.

(0) Результаты не будут идентичны

надо бы планы посмотреть

(5) почему не  будут?

(7) потому что во втором варианте отстуствует группировка по измерению в объединяемых запросах

то есть в первом

(7) хотя, нет. Будут идентичны.

но, самый быстрый вариант ИМХО вот:

ВЫБРАТЬ
    Рег1.изм1
ИЗ
    Рег1 КАК Рег1
СГРУППИРОВАТЬ ПО
    Рег1.изм1

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    Рег2.изм1
ИЗ
    Рег2 КАК Рег2
СГРУППИРОВАТЬ ПО
    Рег2.изм1

(8) а ты знаешь как Объединить работает на уровне SQL ?

(11) неа, технической реализации не знаю.

а вот xуюшки на SQL-2014

1)
выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
сгруппировать по измерение1
объединить
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2
сгруппировать по измерение1

2 845 080 за 19.428 сек

2)
выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
объединить
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2

2 845 080 за 19.160 сек

3) выбрать
выборка.измерение1
из
(выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
объединить все
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2) как выборка

сгруппировать по измерение1

2 845 080 за 8.144 сек

(13) не чистый эксперимент

это в тестовой базе, на реальной базе объем данных в 30 раз больше

(14) отчего ж, типа закешировались результаты уже, да?

(16) у тебя выделенный сервер без нагрузки для тестов? каждый раз перезапускал?

(16) да

(17) да, сервер без нагрузки, фоновые отключены.

(18) перезапускал по 3 раза каждый запрос в рандомном порядке. разница в десятых долях секунд есть. но все равно 19 и 8 секунд по сути.

(20) А никто не говорит что вложенный запрос - всегда плохо.
Да он часто ведет к проблемам, но на объединении как правило быстрее работае, потому что если данных много - не надо сохранять промежуточные данные

(17) сервак не перезапускал, геморно слишком.

(20) Технически между 1 и 2 нет разницы

Оператор Объединить работает как РАЗЛИЧНЫЕ.
Группировка на сколько мне известно часто показывает лучший результат

В документации использование оператора Объединить - крайне нерекомендовано из-за особенности его выполнения.

(25) спасибо за ликбез. практика и выявила все.

Смысл использовать вложение если без него можно обойтись
Надо исходить из задачи

(27) это кусок пользовательского интерфейса, на рабочей базе 62 127 012 записей выбираются за 127 секунд запросом с вложенной выборкой и 516 секунд с использованием ОБЪЕДИНИТЬ
пусть лучше выбирается за 127 секунд.

те какая то форма открывалась 10 мин, а теперь 2?

(28) Т.е. у твоего запроса объединение в подчиненном (вложенном) запросе быстрее, чем объединение без подчиненного (вложенного) запроса, так?

А почему запрос вложенный, а не временная таблица?

(31) тип со временной таблой будет еще быстрее?

(32) её править легче. Правила хорошего тона для одинесников, так сказать

(32) Если верить книжки и практике, то да. Я бы еще попробовал вариант:
выбрать рег1.измерение1 из регистрсведений.рег1 как рег1
объединить
выбрать рег2.измерение1 из регистрсведений.рег2 как рег2
поместить вт_измерений;
выбрать рег1.измерение1 сгруппировать по рег1.измерение1

синтаксис кривой, конечно, но идею, надеюсь донес)

(33) это понятно, просто потом все равно вложенный запрос убивается. зачем класть его во временную таблицу, если ей не пользоваться в других местах в итоге?
(34) понял, вынести вложенный запрос во временную таблицу.

на 30+ миллионах записей я бы поосторожнее со временными таблицами был

Запись во временную таблицу она ни разу не бесплатная

(34) то есть выбрать 1000 записей, потом записать на диск их, потом снова выбрать, это будет быстрее чем просто выбрать 1000 записей?

вы с какой субд работаете где так все происходит?

(38) ладно 1000 то еще скорее всего в память поместится, там разницы скорее всего не будет.
А вот 30M точно будут скидыватся на диск + сгруппировать скорее всего принесет сюрприз

А еще доступ дать к этой обработке паре десяктов пользователей и можно outofmemory поймать

(39) 1000 это просто пример. люди вообще не отдупляют как запросы к субд работают. на 40 постов гадание на кофейной гуще, вместо того что-бы уделить 4 минуты и сравнить 2 плана запроса.

По крайней мере на 1С эксперт одна из главных рекомендаций не использовать ВТ, особенно на больших выборках без жизненной на это необходимости.

(41) Угу

(41) план запроса и любой 1сер сможет глянуть, а вот теоретически доказать, тут нужны знания ))))

(44) теоретически доказать - это пофлудить на форуме. посмотреть планы и сравнить cost, тут нужны знания

(44) оптимальность выполнения запросов и оценивают по плану запроса, по его стоимости, а не рассуждая теоретически.

А что за особенность у Объединить, что она работает дольше чем группировка?

(45) ок, убедил. присоединяюсь к (47).

(47) https://its.1c.ru/db/v8std#content:-2145783213:hdoc

(47) Объединить - в тексте запроса приводит к появлению ключевого слова district
Ну а группировка это группировка, будут совершенно разные планы запросов

(50) с чего бы это? откуда там дистинкт? да и дистинкт, это обычно хорошо, потому что быстрее. ни с чем не путаешь?

(51) Вот смотрю длительные запросы и в половине
UNION ALL SELECT DISTINCT
после переписывания этот запрос из длительных пропадает

(52) а длительность то на каких операциях плана запроса? у тебя мож тупо в индекс не попадает.

(52) UNION никак не связан с DISTINCT

(53)
'SELECT DISTINCT
Q_001_T_002.Q_001_F_000 Q_001_F_000,
CASE  WHEN (Q_001_T_001.Q_001_F_003 = DATETIME(2100,1,1)) THEN DATETIME(1,1,1) ELSE Q_001_T_001.Q_001_F_003 END  Q_001_F_001,
Q_001_T_001.Q_001_F_004 Q_001_F_002
INTO #Tfc2cfeef303e4d67bf9f9b290bbd740b
FROM
#T579b40d3cf1a40318f9d0b41de3c9556 Q_001_T_001 INNER JOIN #Tc13767f46d0645fba30154096d051a66 Q_001_T_002 ON (((Q_001_T_001.Q_001_F_000_00 = Q_001_T_002.Q_001_F_002_00) AND (Q_001_T_001.Q_001_F_001 = Q_001_T_002.Q_001_F_001)) AND (Q_001_T_001.Q_001_F_002 = Q_001_T_002.Q_001_F_003))
UNION ALL
SELECT DISTINCT
Q_002_T_002.Q_001_F_000,
CASE  WHEN (Q_002_T_001.Q_001_F_004 = DATETIME(2100,1,1)) THEN DATETIME(1,1,1) ELSE Q_002_T_001.Q_001_F_004 END ,
Q_002_T_001.Q_001_F_005
FROM
#T4fe24e94d41c4cfda8bcd0b9cd905e37 Q_002_T_001 INNER JOIN #T1441c665115e4cb69588f2f73dc180a5 Q_002_T_002 ON ((((Q_002_T_001.Q_001_F_000 = Q_002_T_002.Q_001_F_001) AND (Q_002_T_001.Q_001_F_001 = Q_002_T_002.Q_001_F_003)) AND (Q_002_T_001.Q_001_F_002 = Q_002_T_002.Q_001_F_004)) AND (Q_002_T_001.Q_001_F_003 = Q_002_T_002.Q_001_F_002))

Вот простой запрос, полное попадание в индекс. Время выполнения 8с
Если объединить заменить на Объединить Все с последующей групировкой - менее 1с

Прямо из ТЖ достал

Я бы фото самого запроса дал, но у меня больше нет доступа к той базе

(57) нужен план из консоли в формате .sqlplan. или в xml

(58) Доступа к профайлеру нет.
Исправлял прям так.
Было плохо стало хорошо.
Возможно можно и лучше, но заказчик доволен

Ну и естественно доступ сейчас туда у меня уже закрыт...

А это не тот SELECT DISTINCT, который генерируется при ВЫБРАТЬ РАЗЛИЧНЫЕ?

(61) конечно тот. и тот UNION  который ОБЪЕДИНИТЬ

(61) нет, ключевого слова РАЗЛИЧНЫЕ в тексте запроса 1с не было.

(63) Хочешь сказать, что это ОБЪЕДИНИТЬ так развернулось в UNION ALL SELECT DISTINCT?
Какое-то удивительное решение.

Странно
Сейчас на демо базе попробовал - там
INSERT INTO ВременнаяТаблица1 WITH(TABLOCK) (_Q_000_F_000RRef) SELECT
T1.Ссылка
FROM Справочник._ДемоНоменклатура T1
WHERE (T1.ОбластьДанныхОсновныеДанные = ?)
UNION SELECT
T2.Ссылка
FROM Справочник._ДемоНоменклатура T2
WHERE (T2.ОбластьДанныхОсновныеДанные = ?)
p_0: 0N
p_1: 0N
Но я точно помню что в том случае не было слова РАЗЛИЧНЫЕ.
Может я уже того?(

(65) в общем случае DISTINCT как правило прибавляет производительности из-за того что обрабатывается меньшее кол-во строк.

(66) Подсказка DISTINCT это операция "Distinct sort", которая, как-бы, тоже имеет свою стоимость. И надо смотреть на то, на каком шаге запроса это используется. Если в конце -  то производительности это не добавляет, а убавляет. Если где-то в начале, а затем над полученной выборкой еще выполняются действия, тогда может и прибавит скорости запросу. А может и нет.

(67) откуда теперь уже sort прилип к distinct?

(66) а шо там с затратами на сортировку при использовании DISTINCT&

(68) а как оно выбирает различные?

(70) внутреннего алгоритма я не знаю. типа кроме как сортировкой уникальные записи не отобрать? нет больше алгоритмов, да?

(68) Какбэ намекну..., для того что бы выбрать различные, данные нужно отсортировать или нет?

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

(72) от алгоритма зависит

глянул простой случай, distinct использует Stream Aggregate. Сортировка есть, да. Но если выходной набор гораздо меньше, вполне оправдано.

(71) Для того выбрать уникальные значения их необходимо отсортировать, затем сравниваются соседние на уникальность.

ЗадачаОтвет: Есть 2 списка значений, нужно оптимально сформировать 3-й список уникальных значений.

Можно перебирать все во вложенных циклах, например сваливаем все в один список, затем добавляем элемент в другой список, в цикле проверяя нет ли такого элемента уже в списке

А Можно отсортировать итоговый список и в один цикл(без вложенных) добавить уникальные значения в список #3

данные сортируются в момент чтения из памяти

(74) как скуль это делает?

(78) в (75) написал. https://docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2008/ms189907(v%3dsql.100)

при чтении сразу сортирует

(75) Stream Aggregate вроде только на сортированных таблицах
http://www.sql.ru/articles/mssql/2007/070602streamaggregate.shtml

(79) неохота самому замеры делать
вот по ссылочке 3-й пост
https://community.oracle.com/thread/2547409

(76) ок...а как тогда делается группировка и почему она быстрей отрабатывает?

после заявлений некоторых персонажей в теме, решил проверить а есть ли там дистинкт или нет, а уж про сортировку, о которой я знал, начал сомневаться, но нет

простой запрос:
ВЫБРАТЬ
1 п1,
2 п2,
3 п3
Поместить Т1
Объединить все

Выбрать
1,
2,
3
;
Выбрать
п1,
п2,
п3
Из
Т1

Объединить

Выбрать
1,
2,
3

результат
1, 2, 3

план запроса
Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC, [Union1010] ASC))
Concatenation
Compute Scalar
    Table Scan
Constant Scan

+(83) duration 17
вес сорт дистинкт 78%

(76) Все зависит от набора данных...к тому же, можно добавлять в хэш-таблицу, тогда поиск по ней будет быстрей и не факт, что сортировка исходной будет эффективней.

Запросы
ВЫБРАТЬ
п1,
п2,
п3
Из
(Выбрать
п1,
п2,
п3
Из
Т1

Объединить все

Выбрать
1,
2,
3) КАК Т
СГРУППИРОВАТЬ ПО
п1, п2, п3

и

ВЫБРАТЬ РАЗЛИЧНЫЕ
п1,
п2,
п3
Из
(Выбрать
п1,
п2,
п3
Из
Т1

Объединить все

Выбрать
1,
2,
3) КАК Т

один и тот же план время выполнения

(82) Группировка тоже имеет SORT!

сейчас что-нибудь посложнее возьму

(85) хм.., а сколько времени уйдет на формирование хэшей в этой таблице?

(87) да много чего имеет. Джойны некоторые. Мердж джойн по моему точно.

(90) мердж джой делается только по отсортированным (индексирвоанным) таблицам. иначе хэш джойн

Но когда это по полю с упорядоченным индексом, вроде как не страшно

как не напиши запрос - всегда один план получается

(89) Я и говорю, что все зависит от данных. Если часто повторяются и нужно будет больше читать, чем писать, то эффективней будет хэш-таблица, если нет, то, скорей всего, сортировка.

(87) а почему она тогда быстрей?

у кого есть постгри, попробуйте там

(95) Быстрее чего?
И почему быстрее?

(94) Можно конкретные примеры этих волшебных данных?

(96) есть постгри на домашнем серваке, но базы на нем мелкие.

сотенко

Что в итоге решили?

(97) Группировка быстрее соединения. В (13) примеры.
(98) Конкретных под рукой нет. Но теоретически, это данные, в которых очень часто повторяются строки и они даже частично не отсортированы(т.е. сложность сортировки возрастает) и неравномерно распределены.

(102) Какого соединения?
в посте речь о об объединить и объединить все...
Группировка как и объединить имеет в себе сортировку, а так это совершенно разные операции и сравнивать их некорректно.., корректнее сравнивать время выполнения решения, которое имеет одинаковые выходные данные и включает в себя данные операторы. А говорить "Группировка" быстрее "Соединения(я думаю имелось ввиду объединение)" - полный бред!

"Но теоретически, это данные..." - теоретически мы все обезьяны по Дарвину.

(103) Да, опечатался, имел ввиду именно Объединить.

А на каких данных объединение будет быстрее?

(103) "теоретически мы все обезьяны по Дарвину" // Это миф. Ты б первоисточник почитал? хотя бы в кратком изложении.

(вместо вопросика должна быть запятая - раскладка не переключилась)

(105) а что там, в двух словах?

(105) Нах.., если вики есть
На основании сравнительно-анатомических, эмбриологических данных, указывающих на огромное сходство человека и человекообразных обезьян, Дарвин обосновал идею их родства, а следовательно, и общности их происхождения от древнего исходного предка. Так родилась симиальная (обезьянья) теория антропогенеза[16]. Работа Дарвина «Происхождение человека и половой отбор» вышла спустя 12 лет после «Происхождения видов». По мнению историка Б. Ф. Поршнева, известное выражение «человек произошел от обезьяны» принадлежит в первую очередь не Дарвину, а его последователям Т. Гексли, К. Фохту и Э. Геккелю
https://ru.wikipedia.org/wiki/Происхождение_человека_от_обезьяны

(107) (108) Если из А следует Б и из А следует С, то это не означает, что из Б следует С. Логика.

(105) а по теме есть что?

(109) в обратку прокрути

Зашел почитать про ОБЪЕДИНИТЬ, наткнулся на Дарвина.

(110) Так по теме тот же самый совет - почитать первоисточник (МСДН, например). В отличие от эволюции, у нас имеется возможность на практике убедиться, во что запрос 1С предвращается в СУБД.

(113) ну, а я о чем в (103)

(109) Если С потомок А и Б потомок А, то С и Б являются подтипом А и отчасти они являются А, в нашем случе О
https://ru.wikipedia.org/wiki/Список_прямых_предков_человека_современного

И давай не флудить тут!

(115) Не путай "является потомком" и "следует". В моей формулировке никаких допущений нет, в отличие от твоей )

(116) не путай свое субъективное понятие моего поста с какими-либо допущениями

(117) Утверждение, что кто-то является потомком кого-то - это конечно же допущение. Ты что ли не согласен с этим?

(118) Ой все!
ЗЫ: перечитай все выше, можно несколько раз!

(119) Выше только твои утверждения, являющиеся допущениями, на что Я твое внимание и обратил. В отличие от моих утверждений, являющихся истиной.

(120) Не оправдывайся, лучше сходи в(119)

(121) Это конечно же не оправдание, а объяснение, почему твоя отсылка перечитать что-то выше безполезна

(122) трансформация одного в другое не произойдет лишь из-за твоего понимания что это было -> (121)

(123) Что-то ты мудришь )

пацаны, в топ мисты. чего-нибудь за экзистенциализм еще затрите.

(125) Мы тебе ветку поднимаем, может еще кто-нить что-то полезное по теме скажет.

(126) Говори за себя: Я лишь топлю за логику и уличаю тебя в ее нарушении, на подъем ветки мне пофиг )

так на чем остановились?

(128) См. (113) - эмпирически

Классно у нас на мисте...начнется все с запроса, а потом плавно перетечет к Дарвину и научным методам обоснования.
Каждый раз заходя в любую ветку, чувствуешь себя первооткрывателем.
Да по нам можно диссер писать по психологии)))

(130) Дарвин попал в обсуждение не сам по себе и вообще не особо важно, что именно было в оффтопе: просто была замечена кривда и нарушение законов логики, и понеслась. А уж на какую тему - пофиг.