SQL оператор IN( ..list.. ) для 1С

30.09.25

База данных - HighLoad оптимизация

Возможные проблемы быстродействия при использовании SQL оператора IN( ..list.. ) для 1С.

Введение

Недавно обсуждали с коллегой использование условия Where … IN( ..list.. ). Пример запроса 1С ниже, но оператор IN( ..list.. ) может использоваться в любом контексте, необязательно виртуальная таблица остатков. Запрос написан для УТ, без ограничения общности. Поведение запроса зависит от СУБД, есть разные варианты.

 
 Мировая паутина предупреждает...

 

Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
|Остатки.Номенклатура,
|Остатки.ВНаличииОстаток
|ИЗ
|РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура В (&ВсяНоменклатура)) КАК Остатки";

Запрос.УстановитьПараметр("ВсяНоменклатура", ВсяНоменклатура);
РезультатЗапроса = Запрос.Выполнить();

СУБД MS SQL

В документации написано: Явное включение очень большого количества значений (много тысяч значений, разделенных запятыми) в круглые скобки в предложение IN может привести к интенсивному расходованию ресурсов и возврату ошибки 8623 (out of internal resources) или 8632 (An expression services limit has been reached). Чтобы избежать этой проблемы, храните элементы списка IN в таблице и используйте вложенный запрос SELECT в предложении IN.

Пока искал информацию, вспомнил, что Виктор Богачев что-то рассказывал нам об этом операторе, но нигде не смог найти подробности. Награда  ждЁт // того, кто найдЁт! (С)

Посмотрим, как обрабатывается код 1С из примера в технологическом журнале logcfg.xml, события DBMSSQL и SDBL для СУБД MS SQL. Сначала выполним запрос для массива &ВсяНоменклатура 150 элементов, потом сделаем тоже для 100 элементов.

 
Текст logcfg.xml
 
 фрагменты журнала

При сравнении событий SDBL видим одинаковые фрагменты Fld7882 IN (***СПИСОК ...***) которые соответствуют фрагменту запроса Номенклатура В (&ВсяНоменклатура). Программа 1С:Предприятие передает запрос в СУБД одинаково для массива 100 элементов и массива 150 элементов.

Однако, при сравнении событий DBMSSQL видим изменения:

Для 100 элементов СУБД использует массив

T2._Fld7882RRef IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Для 150 элементов СУБД использует временную таблицу #tt31.

T2._Fld7882RRef IN (SELECT ... FROM #tt31 ...)

Наконец, вспомнил! Виктор Богачев рассказывал нам, что массивы больше 128 элементов преобразуются во временные таблицы. В событии SDBL используется массив, в событии DBMSSQL используется временная таблица для массива 150 элементов. Версия MS SQL Server 2016.

СУБД PostgreSQL

Посмотрим, как обрабатывается код 1С из примера в технологическом журнале logcfg.xml, события DBPOSTGRS. Размер массива 1500 элементов. Версия СУБД postgresql_13.21_1.1C_x64. Пробуйте на других версиях, мне интересно. 

 
 текст logcfg.xml
 
 фрагмент журнала

Видно, что СУБД PostgreSQL использует массив T2._Fld37189RRef IN ( ***СПИСОК 1500*** ), использует Nested Loop.
СУБД ORACLE

В документации указано внутреннее ограничение Oracle на максимальное значение в 1000 значений в списке.

Заключение

Мы обсудили, как разные СУБД обрабатывают запрос с оператором IN( ..list.. ). Нужно учитывать, что при большом объеме списка проблемой может стать не только выполнение запроса, не использование в плане запроса индекса, но и передача большого объема данных list на сервер СУБД. Использование подзапроса из индексированной таблицы вместо list выглядит более надежным.

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

Дополнение от 02/10/2025

Проверил две гипотезы на примере списка 150 элементов.

(1) Временная таблица #tt1 создана платформой 1С, команда INSERT INTO, метка времени 51:43.656000. Очищена платформой 1С, команда truncate table, метка времени 51:43.719003

(2) Соединение временной таблицы #tt1 (псевдоним T3) с таблицей AccumRgT7888 (псевдоним T2) происходит оператором Nested Loops, без использования индекса. Оператор Stream Aggregate применяется для группировки.

 
 фрагмент журнала

 

Спасибо всем за толковые комментарии.

Вступайте в нашу телеграмм-группу Инфостарт

См. также

HighLoad оптимизация Программист 1С v8.3 1C:ERP Бесплатно (free)

Приведем примеры использования различных в динамических списках и посмотрим, почему это плохо.

18.02.2025    7157    ivanov660    39    

61

HighLoad оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024    9651    ivanov660    13    

62

HighLoad оптимизация Программист 1С v8.3 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    15339    Evg-Lylyk    73    

45

HighLoad оптимизация Программист 1С v8.3 1C:Бухгалтерия Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    7511    spyke    29    

53

HighLoad оптимизация Программист 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    10716    vasilev2015    22    

45

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист 1С v8.3 1C:Бухгалтерия Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

5 стартмани

15.02.2024    17885    330    ZAOSTG    100    

123
Вознаграждение за ответ
Показать полностью
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. paulwist 01.10.25 14:44 Сейчас в теме
Для 100 элементов СУБД использует массив

T2._Fld7882RRef IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Для 150 элементов СУБД использует временную таблицу #tt31.

T2._Fld7882RRef IN (SEL ECT ... FR OM #tt31 ...)


Эээ, а причём тут СУБД MSSQL, на основании чего вы сделали такой вывод??
2. vasilev2015 2824 01.10.25 14:48 Сейчас в теме
(1) на основании текста события DBMSSQL, фрагмент журнала приложен.
3. paulwist 01.10.25 14:54 Сейчас в теме
(2)
на основании текста события DBMSSQL, фрагмент журнала приложен.


Хех, вы полагаете, что СУБД самостоятельно создало времянку+заполнило её какими-то данными???

Можете привести ссылку из документации вендора, что MSSQL так умеет делать?

PS Попробуйте в профайлере трассирнуть кусок с IN > 150 значений и посмотрите какой логин/УЗ создаёт временную таблицу, ... обнаружите, что это сделал юзер от имени которого запущен сервис 1С.
4. vasilev2015 2824 01.10.25 15:15 Сейчас в теме
(3) В публикации не указано, что СУБД создает временную таблицу самостоятельно. Скорее всего временную таблицу создает платформа 1С. Спасибо за уточнение. Присылайте трассировку.
5. starik-2005 3198 01.10.25 16:13 Сейчас в теме
На стековерфлоу вопрос о том, использует ли IN индекс:
Обычно так и происходит, если только предложение IN не охватывает слишком большую часть таблицы, и тогда выполняется сканирование таблицы. Лучший способ выяснить это в вашем конкретном случае — запустить запрос в анализаторе запросов и проверить план выполнения.
Т.е. если у вас есть организация, и по ней есть индекс, то все упирается в то, насколько много организаций. Если там три организации и по каждой миллион записей, то какой смысл использовать индекс? Так что и "В" и "=" не будут использовать индекс. А если это ID (ссылка), то индекс будет использоваться - это как бы само собой разумеется. И нужно понимать, что в жизни куда больше случаев с вариантами между этими двумя, и по ним ясность наступает только при выполнении запроса. Т.е. для поля с высокой селективностью будет юзаться индекс, а для поля с низкой и при "=" он скорее всего юзаться не будет. Т.е. системе во втором случае что индекс целиком вычитывать, а потом таблицу, что сразу таблицу - разницы мало.
6. vasilev2015 2824 01.10.25 16:19 Сейчас в теме
(5) Если внутри IN( ... ) запрос из индексированной таблицы - скорее всего, индекс будет использоваться.
Если внутри IN( ... ) список (массив) - скорее всего, индекс НЕ будет использоваться.
7. starik-2005 3198 01.10.25 16:33 Сейчас в теме
(6) Есть несколько вариантов, как найти значения в списке. Самый простой и незамысловатый - просто пройтись по всем записям таблицы и сравнить с искомым. Если у нас есть 1500 значений в списке и миллион значений в таблице, то для этого нужно миллион умножить на 1500 = полтора лярда. Это, предположу, долго. Но умные людишки придумали хеш-таблицы, бинарный поиск, может быть даже что-то еще. Хеш-таблица - это такое соответствие. На него некоторым образом указывает "HashAggregate (cost=18.00..33.00 rows=1500 width=32) (actual time=0.694..1.041 rows=1500 loops=1)". Тут 1500 строк и 1 цикл.
Обычный индекс в СУБД - это бинарное дерево, по нему значений ищется за log 2 N - это, предположу, ваш index scan. В итоге данные, которые были засканированы в субд, искались с этой хеш-таблицы.
План запроса для мелкомягких я не обнаружил, но могу предположить, что все примерно также, за исключением ограничения самого мелкомягкого скуля на количество значений в IN.
vasilev2015; +1 Ответить
8. vasilev2015 2824 01.10.25 16:37 Сейчас в теме
(7) Поддерживаю, согласен.
9. starik-2005 3198 01.10.25 16:47 Сейчас в теме
(8) Там неточность. Скан индекса вычитывает весь индекс, а потом каждую его запись с гуидом номенклатуры ищет в хеш-таблице. В итоге O(N * 1..Log2(1500)). Но все-равно это все достаточно быстро. Можем ли мы сказать, что индекс тут не используется? Ну, наверное, не можем. Можем ли мы сказать, что индекс тут используется неоптимально? Конечно, т.к. мы тут не ищем в индексе за Log 2 N, а ищем все значения из него в хеш-таблице. С другой стороны, если искать 1500 значений в индексе, то стоит их предварительно упорядочить, тогда это будет быстрее, т.к. индекс - двоичное дерево. В итоге будет О(1500 * Log 2 N'), где N' - это срезы правее предыдущего найденного. Быстрее ли это, чем O(N * 1..Log2(1500))? Я не знаю )))
10. vasilev2015 2824 01.10.25 16:55 Сейчас в теме
(9) Найду план запроса MS SQL, посмотрим вместе. Мне тоже интересно стало ))
15. vasilev2015 2824 03.10.25 08:35 Сейчас в теме
(9) Добавил более подробный план запроса для MS SQL в статью. В этом частном случае индекс не используется.
16. paulwist 03.10.25 11:25 Сейчас в теме
(15)
В этом частном случае индекс не используется.


Ммм, а индекс на какой таблице не используется?? Что-то я не увидел Table/Clustered Index Scan.
17. vasilev2015 2824 03.10.25 11:32 Сейчас в теме
(16) По моему мнению, здесь оператор IN реализован через внутреннее соединение. Индекс не используется при соединении таблиц. Операторы Nested Loops не используют индекс. Псевдонимы таблиц T2, T3.
19. paulwist 03.10.25 12:48 Сейчас в теме
(17)
Операторы Nested Loops не используют индекс.


Ну давайте разберём.

1. Подзапрос из временной таблички Т3 (#tt1), сначала ищутся значения IsNotNull с использованием поиска по кластерному индексу. (тут конечно, есть некоторый нюанс, физически идёт сканирование кластерного индекса, на это указывает ORDERED FORWARD - есть такая особенность в показе плана MSSQL. Почему сканирование, потому что IsNotNull низко селективное/кардинальное значение)
Результат помещается в буферный кэш.

|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#tt1] AS [T3]), SEEK:([T3].[_Q_000_F_000RRef] IsNotNull) ORDERED FORWARD)


2. Поскольку оператор IN (подзапрос), то оптимизатор убирает дублирующие значения из получившегося набора данных/буферного кэша из п.1

|--Stream Aggregate(GROUP BY:([T3].[_Q_000_F_000RRef]))


3. По значениям из буферного кэша, ищутся соответствия (через NL) в таблице Т2 (_AccumRgT7888]), причём поиск осуществляется по/в индексу (тут опять наблюдаем ORDERED FORWARD - это говорит, что идёт сканирование индекса. Почему, тут два ответа либо низкая кардинальность, либо значения лежат подряд и достаточно отсканировать страницы индекса один раз спустившись по дереву, либо сканирование индекса дешевле хождению по дереву до листьев)

Index Seek(OBJECT:([].[dbo].[_AccumRgT7888].[_AccumR7888_ByDims7887_TR] AS [T2]), SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld7882RRef]=[tempdb].[dbo].[#tt1].[_Q_000_F_000RRef] as [T3].[_Q_000_F_000RRef]ORDERED FORWARD)


4. Ну и заключительный NL - это по данным полученным в п.3 лезем уже в саму таблицу (кластерный индекс) за недостающими для запроса полями

|--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[_Period], [T2].[_Fld7881RRef], [T2].[_Fld7882RRef], [T2].[_Fld7883RRef], [T2].[_Fld7884RRef],[T2].[_Fld7885RRef], [Expr1011]) WITH ORDERED PREFETCH)
....
|--Clustered Index Seek


Собственно всё. Как видим поиск в индексах при NL используется.
18. starik-2005 3198 03.10.25 11:52 Сейчас в теме
(15)
В этом частном случае индекс не используется.
А что в данном случае "Индекс"?
150, 1, 150, 0.00313, 0.000322, 23, 0.00345, 1,                       |    |    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#tt1] AS [T3]), SEEK:([T3].[_Q_000_F_000RRef] IsNotNull) ORDERED FORWARD)
4, 150, 59.3, 0.00313, 0.000222, 95, 0.129, 39.4,                       |    |--Index Seek(OBJECT:([].[dbo].[_AccumRgT7888].[_AccumR7888_ByDims7887_TR] AS [T2]), SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld7882RRef]=[tempdb].[dbo].[#tt1].[_Q_000_F_000RRef] as [T3].[_Q_000_F_000RRef]) ORDERED FORWARD)
2, 4, 2.2E+003, 0.00313, 0.000158, 20, 7.66, 2.34E+003,                       |--Clustered Index Seek(OBJECT:([].[dbo].

Ну и что тут у нас?
Ищем в кластерном индексе 150 наших записей во временной таблице. Получаем упорядоченный список. Ищем эти 150 значений в кластерном индексе по соединению регистра.
Т.е. получили записи из регистра и записи из временной таблицы, по всей видимости уже упорядоченные (ORDERED FORWARD, т.е. ищем уже по упорядоченному списку, поэтому получаем упорядоченные данные).
Дальше ищем 150 записей в записях регистра, чтобы соединить их. Ищем в цикле. Но как ищем? Если по упорядоченному списку, то двоичный поиск, т.е. log 2 N - это так же быстро, как по индексу, только еще быстрее, т.к. это не нужно читать из базы - из базы мы по кластерному индексу все извлекли.
Т.е.:
1. Прочитали из базы по кластерному индексу.
2. Соединили прочитанные выборки через поиск в цикле. Но это не значит, то для поиска мы использовали длительный алгоритм перебора всех записей - у нас списки упорядоченные.

В общем не стоит считать, что nested loop - это плохо. Ведь то же соединение - это поиск данных из левой таблицы в правой. Если в обоих таблицах данные упорядочены, то мы можем идти по первой и второй таблице только вперед. Получается эффективно - O(N+M), если индекс есть только в правой таблице, то O(N * Log 2 M). Если нет в обоих, то O(N * M). И скульный сервер такие шарады решать умеет хорошо. И два последних варианта - это поиск в цикле. При том скул для последнего варианта легко может создать хеш-таблицу для поиска, в итоге все сведется к еще более эффективному O(N * 1..Log 2 N).
23. paulwist 08.10.25 08:44 Сейчас в теме
(18)
Дальше ищем 150 записей в записях регистра, чтобы соединить их. Ищем в цикле. Но как ищем? Если по упорядоченному списку, то двоичный поиск


Поясните свою мысль, в примере как раз два упорядоченных списка (из времянки и регистра), почему NL ищет бинарным поиском, из чего это следует??
24. starik-2005 3198 08.10.25 13:22 Сейчас в теме
(23) По двум упорядоченным спискам двоичный поиск не нужен - достаточно двигаться по обоим в одну сторону. Но у нас есть "Nested Loops Inner Join" выше, что предполагает, что мы ищем данные из уже отобранной одной таблицы в уже отобранной второй, чтобы соединить их. Могу ошибаться, но тогда Nested Loops здесь - это не совсем Nested Loops. Ну или это куда более широкое понятие, чем я привык считать.
25. paulwist 08.10.25 15:22 Сейчас в теме
(24)
Но у нас есть "Nested Loops Inner Join" выше, что предполагает, что мы ищем данные из уже отобранной одной таблицы в уже отобранной второй, чтобы соединить их.


1. Опция ORDERED FORWARD говорит только о том КАК извлекаются данные, например, используя связанный список, но это не значит, что в буферном кэше данные будут расположены упорядоченно.

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

2. Обратите внимание на WITH ORDERED PREFETCH, когда соединяются временная таблица и регистр (вложенный NL), для получения значения кластерного индекса.

Nested Loops(Inner Join, OUTER REFERENCES:([T3].[_Q_000_F_000RRef], [Expr1010]) WITH ORDERED PREFETCH)


это говорит, что DBEngeen использует упреждающее чтение из индекса, то есть в кэше нет второго упорядоченного списка.

Подробнее тут. Чтение страниц данных в ядре СУБД

3. То же самое происходит для внешнего NL, когда из кэша берётся значение кластерного индекса регистра и через упреждающее чтение вынимаются недостающие поля.
26. starik-2005 3198 08.10.25 18:17 Сейчас в теме
(25)
то есть в кэше нет второго упорядоченного списка.
Ну если второй список неупорядочен, то прямо напрашивается двоичный поиск для каждого значения этого неупорядоченного списка в одном упорядоченном, который есть. Это логично. Вряд ли разрабы скула будут искать в двух циклах с О(N*M), если можно искать с O(N * log 2 M).
Об этом же говорит и текст по ссылке:
Подсистема хранилища считывает страницы индекса последовательно, в порядке значений ключей.
27. paulwist 09.10.25 08:37 Сейчас в теме
(26)
Ну если второй список неупорядочен, то прямо напрашивается двоичный поиск для каждого значения этого неупорядоченного списка в одном упорядоченном, который есть.


Первое, в B-Tree индексе нельзя применить двоичный поиск (за исключением, когда имеется один корневой и два листовых узла ), поскольку в двоичном дереве каждый узел имеет только двух потомков, а B-Tree сильно ветвистое дерево.

Второе. Так оно и происходит, сканируется кэш, для каждого значения которого ищется соответствие в индексе регистра.

(26)
Подсистема хранилища считывает страницы индекса последовательно, в порядке значений ключей.


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

Например, надо найти значения от 2100 до 3100 в 1 млн записей.

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

0-1000 - первая страница
1001-2000 - вторая страница
2001-3000 - третья страница
3001-4000 - четвертая страница

Оптимизатор видит, что интервал 2100 до 3100 лежит на третьем и четвертом листе, поэтому достаточно пройти сверху до третьей страницы и далее просканировать эти две страницы, вместо того, что бы 1000 раз пройтись сверху вниз по дереву.

PS надеюсь сумел "донести" :)
28. starik-2005 3198 09.10.25 10:08 Сейчас в теме
(27)
PS надеюсь сумел "донести" :)
В моем понимании все сводится к эффективности поиска. Если у нас есть упорядоченные данные, то мы имеем возможность найти в них что-то за Log 2 N итераций. Если это список, в каждом элементе которого есть ссылка на элемент больше и меньше, то в таком списке эффективность поиска такая-же.

Значит что? 1. Скуль использует индекс и при использовании IN, о чем недвусмысленно говорит нам план запроса. 2. Ничего не мешает скулу в поиске использовать тот алгоритм, который сделает это быстрее, и в разных скулах используются разные алгоритмы, но оба они быстрые, т.е. не O(N) точно.

Надеюсь, сумел "донести" )))

ЗЫ: Вы не писали никогда какие-нить СУБД? Я писал.
29. paulwist 09.10.25 11:03 Сейчас в теме
(28)
В моем понимании все сводится к эффективности поиска. Если у нас есть упорядоченные данные, то мы имеем возможность найти в них что-то за Log 2 N итераций. Если это список, в каждом элементе которого есть ссылка на элемент больше и меньше, то в таком списке эффективность поиска такая-же.


Давайте рассмотрим пример, понятно, что это сильно притянутый за уши.

Пусть есть список из 1000 значений "размером" bigint.

Для бинарного дерева имеем 8 уровней индекса, что бы найти одно значение надо пройти по 8 страницам данных (уровни индекса, те считать их с диска) - с этим согласны?

Для b-tree дерева 1000 значений займут 1 страницу данных - с этим согласны?

Теперь далее, для бинарного дерева "работа" составит 8 страниц чтения.
Для b-tree дерева "работа" составит 1 страницу чтения + поиск/значения поиска.

На сегодняшний момент, чтение с диска на порядки медленная операция чем поиск в буферном кэше, - с этим согласны?

Случай два.

Надо найти два рядом лежащих значения , неапример 2 и 3.

Бинарное дерево, сканирование 8 страниц до значения 1 + 1 страница для значения 2, в сумме получается 9 страниц данных.
Для b-tree дерева "работа" составит всё те же 1 страница чтения + поиск/значения поиска.

Случай три, ищем значения 1 и 1000.

Бинарное дерево, сканирование 8 страниц до значения 1 + 8 страница для значения 1000, в сумме получается 18 страниц данных.
Для b-tree дерева "работа" составит всё те же 1 страница чтения + поиск/значения поиска.

Разницу надеюсь видите.

Ммм, собственно, вы во втором абзаце сами об этом написали.

(28)
но оба они быстрые, т.е. не O(N) точно.


(28)
Вы не писали никогда какие-нить СУБД?


Хех, пытался начать, НО вовремя старшие товарищи "отговорили", сказав "не занимайся ерундой, уже есть коммерческие СУБД" и я послушался :) :) :)
30. starik-2005 3198 09.10.25 11:16 Сейчас в теме
(29)
Разницу надеюсь видите.
В данном конкретном случае у нас есть две таблицы. Одна - временная, в которой 150 элементов, в которые у нас записались те самые значения, которые мы засунули в IN. Мы ее прочитали полностью. Все остальное тут нас мало волнует. Она у нас целиком в памяти. Даже если нам не нужны все значения из нее, мы ее только что записали из памяти.
Вторая - это таблица остатков. И как мы ее читаем? Ищем по индексу и по кластерному индексу, вычитывая остатки из самой таблицы, которых нет в индексах. Судя по плану запроса из мелкомягкого скуля, мы ищем в индексе значения номенклатуры первой таблицы, потом ищем эти записи в самой таблице и читаем остаток.
Дальше соединяем то, что у нас в памяти.
Поэтому не совсем понимаю, о чем мы конкретно спорим.
сказав "не занимайся ерундой, уже есть коммерческие СУБД"
В 1996-м году были, конечно, коммерческие СУБД, но не для обычных пользователей обычных ПК на MS DOS с нороном.
31. paulwist 09.10.25 12:09 Сейчас в теме
(30)
Поэтому не совсем понимаю, о чем мы конкретно спорим.


Хех, причём тут спор, ищём истину :), вы полагали, что поиск в b-tree это поиск в двоичном дереве, но это не так, вот собственно ВСЁ :)

(30)
В 1996-м году были, конечно, коммерческие СУБД, но не для обычных пользователей обычных ПК на MS DOS с нороном.


Если мне не изменяет склероз на тот момент были настольные БД под DOS а-ля Клиппер, FoxPro 2.0, 2.5, 2.6 (2.6 for Windows), Visual FoxPro 3.0 (тут вАще уже ООП, но правда под Win).

Из взрослых: Sybase (версию уже не помню), MSSQL 6.5

Это те которые я юзал.

(30)
с нороном.


Так понимаю подразумевался Нортон командер :)
32. starik-2005 3198 09.10.25 13:24 Сейчас в теме
(31)
вы полагали, что поиск в b-tree это поиск в двоичном дереве, но это не так, вот собственно ВСЁ :)
Ну если принять во внимание это:
Свойство 2 можно сформулировать иначе: каждый узел B-дерева, кроме листьев, можно рассматривать как упорядоченный список, в котором чередуются ключи и указатели на потомков.
То вполне себе согласуется с поиском по двоичному дереву. Другое дело, что этот лист содержит не одну запись, а диапазон, а БД читает листьями, т.к. файловая система не умеет читать меньше, чем кластер файловой системы.

На мой взгляд, вы просто прицепились к тому, что я назвал индекс двоичным деревом, а это сбалансированное дерево (поиска) в MS SQL и, предположу, в PGSQL тоже. Но само по себе B в "B-tree" должно, полагаю, "на что-то намекать".
Бинарное дерево — это древовидная структура, где каждый узел имеет максимум двух потомков (левый и правый). B-дерево является обобщением бинарного дерева поиска, в котором узлы могут содержать множество ключей и иметь множество потомков. B-деревья используются для эффективной работы с большими объемами данных, таких как на дисках, и применяются в базах данных и файловых системах.

Так что не имею ничего против. В обоих случаях эффективность поиска логарифмическая.
33. paulwist 09.10.25 15:02 Сейчас в теме
(32)
На мой взгляд, вы просто прицепились к тому, что я назвал индекс двоичным деревом, а это сбалансированное дерево (поиска)


Пусть будет так.

Информация к размышлению (с) 17 мгновений весны.

Некоторые отличия двоичного дерева от b-tree.

1. буква «B» в обозначении B-tree означает «Balanced», то есть, сбалансированный, иными словами, высота (она же глубина) в таком индексе всегда одинакова для любой его ветви от корня до листа.

2. дерево индекса вовсе не бинарное (можно провести аналогию с бинарным деревом, только – зачем?), поскольку по мере роста индекса ключи могут заполнить блок насколько это возможно, «под завязку».

3. каждый ключ индекса, хранящийся в блоке, если этот блок не листовой, состоит из двух частей – собственно индексируемого значения и указателя на расположенный ниже (в контексте иерархии дерева) блок, так называемый Relative Block Address (RBA). Все листовые блоки представляют собой двусвязный список, связь между соответствующими элементами которого осуществляется также с помощью указателей, но уже не привязанных к значению ключа, поскольку в листовом блоке ключ индекса состоит из индексируемого значения (значения ключа) и кластерного индекса/ROWID строки индексированной таблицы.

4. в каждом нижележащем блоке (опять же относительно корня дерева, в контексте иерархии) в качестве минимального («в самом низу блока») значения ключа содержится то же значение ключа, что и значение ключа в вышележащем блоке. И вот это значение ключа в вышележащем блоке имеет в качестве RBA адрес нижележащего блока.

Спасибо за тред, кое что уже подзабылось.

PS думаю на этом можно закончить. :)
20. Painted 49 07.10.25 10:16 Сейчас в теме
21. starik-2005 3198 07.10.25 13:45 Сейчас в теме
(20)
not a binary tree
Сбалансированное дерево в части поиска эквивалентно двоичному. Да, индекс в современных СУБД бывают разные.
Пруф: https://ocrmnolblog.ru/post/128/indeksy-v-sql-bazah-dannyh\
Бинарные индексы (Binary Indexes)

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

Основное преимущество индексов бинарных заключается в том, что операции поиска, вставки и удаления выполняются в среднем за O(log n) времени, что делает их эффективными для работы с большими наборами данных. Однако для поддержания быстроты операций, важно, чтобы данные были структурированными (отсортированными по убыванию, как указано в предыдущем абзаце). В противном случае время работы может увеличиться до O(n).

Смысл бинарного поиска – делить данные пополам и смотреть – текущее значение больше или меньше нужного, потом снова пополам, пока не обнаружится результат.
22. starik-2005 3198 07.10.25 13:47 Сейчас в теме
(20) Есть многое на свете...
Бинарные индексы (Binary Indexes)

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

Основное преимущество индексов бинарных заключается в том, что операции поиска, вставки и удаления выполняются в среднем за O(log n) времени, что делает их эффективными для работы с большими наборами данных. Однако для поддержания быстроты операций, важно, чтобы данные были структурированными (отсортированными по убыванию, как указано в предыдущем абзаце). В противном случае время работы может увеличиться до O(n).

Смысл бинарного поиска – делить данные пополам и смотреть – текущее значение больше или меньше нужного, потом снова пополам, пока не обнаружится результат.
Пруф вставил в мессагу, которая пропала. найдешь, если захочешь.
Но суть в общем-то не меняется. Поиск за логарифм.
ЗЫ: И да, в мелкомягком скуле и постгресе сбалансированные деревья.
12. paulwist 02.10.25 09:33 Сейчас в теме +1 $m
(6)
Если внутри IN( ... ) запрос из индексированной таблицы - скорее всего, индекс будет использоваться.
Если внутри IN( ... ) список (массив) - скорее всего, индекс НЕ будет использоваться.


Что гадать-то, будет-не будет.

Вот репро-код, когда IN (список) используется индекс и дешевле чем IN (sel ect)

use tempdb
go
cre ate   table #t (id int, name char(100) )
cre ate   index idx_id on #t (id)

cre ate   table #tIN (id int , name char(100) )
cre ate   index idx_id on #tIN (id)

declare @i int = 0
while @i < 3000
begin
	ins ert into #t (id) values (@i)
	set @i = @i + 1
end

ins ert into #tIN (id) values (2), (3)

set statistics io on
go
sel ect * fr om #t t where t.id in (2, 3) 

sel ect * fr om #t t where t.id in (select id fr om #tIN) 
se t statistics io off
go

dr op   table #t
dr op   table #tIN

Показать


Ну и статистика
IN (список) = 6 страниц прочитано
IN (select) = 6+2=8 страниц прочитано

(2 rows affected)
Таблица "#t Сканирований 2, логических операций чтения 6

(1 row affected)

(2 rows affected)
Таблица "#t. Сканирований 2, логических операций чтения 6
Таблица "#tIN Сканирований 1, логических операций чтения 2

(1 row affected)
Показать


Планы, сверху когда IN (список), снизу IN (sele ct).
Прикрепленные файлы:
14. vasilev2015 2824 02.10.25 10:03 Сейчас в теме
(12) Аплодирую стоя за такой подробный пост. Но перейти от частных случаев к общему правилу иногда бывает очень трудно логически.
11. PerlAmutor 160 01.10.25 19:23 Сейчас в теме
Осталось теперь убедить обычных пользователей не настраивать отборы в динамических списках и СКД отчетах, куда помещаются все документы из таблицы по условию "В Списке()". Я с таким сталкивался, пользователь добавил 20к ссылок на документы по условию "НЕ В Списке()". Там ни то чтобы отчет долго формировался, там сама форма отчета открывалась несколько минут.
vasilev2015; +1 Ответить
13. vasilev2015 2824 02.10.25 09:57 Сейчас в теме
(11) "Если безобразие нельзя предотвратить, его нужно возглавить!" (С)

Сделать, заполнить регистр ИсключенияСписка и поставить условие в запрос
Левое Соединение РегистрСведений.ИсключенияСписка КАК ИсключенияСписка
....
Где ИсключенияСписка.Документ is null

???
Для отправки сообщения требуется регистрация/авторизация