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

30.09.25

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

Введение

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

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

SQL-оператор IN в предложении WHERE используется для проверки, существует ли определенное значение в наборе значений. Например, мы можем написать запрос для выборки всех клиентов, чьи идентификаторы находятся в заданном списке идентификаторов: SELECT * FROM clients WHERE client_id IN (1, 2, 3, 4, 5).
Хотя оператор IN предлагает удобный и гибкий способ фильтрации данных, есть несколько причин, почему он может быть неоптимальным с точки зрения производительности:

Полный перебор значений: Когда используется оператор IN, СУБД выполняет полный перебор входящих значений для каждого элемента набора данных. Это может быть очень затратной операцией для больших наборов данных или когда количество значений IN достаточно велико. В результате запрос может выполняться долго и занимать большой объем системных ресурсов.
Отсутствие возможности использования индексов: При использовании оператора IN большинство СУБД не может использовать индексы для оптимизации запроса. Вместо этого СУБД просматривает каждое значение IN последовательно для каждой строки таблицы. Это может привести к значительным задержкам в выполнении запроса, особенно когда таблица содержит много строк.
Проблемы с памятью: Если количество значений IN очень большое, это может привести к превышению лимитов памяти СУБД. В результате запрос может вызвать сбои или просто не выполняться из-за нехватки памяти для обработки большого количества значений.
Ограничение на тип данных: Некоторые СУБД имеют ограничение на максимальное количество значений IN в запросе. Если количество значений превышает это ограничение, то запрос может быть невозможно выполнить.

*на основе анализа сайтов sqlservercentral.com, db-engines.com, prokoding.ru, stackoverflow.com

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

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

СУБД 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

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
<dump create="false" type="0" prntscrn="false"/>
<log history="24" location="E:\1Сtehlog\Temp">

<event>
<eq property="name" value="DBMSSQL"/>
<ge property="duration" value="10"/>
</event>

<event>
<eq property="name" value="SDBL"/>
<ge property="duration" value="10"/>
</event>

<property name="all"/>
</log>
<plansql />

</config>

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

Событие DBMSSQL для 150 элементов. Обратите внимание на фрагмент

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

17:06.213000-45999,DBMSSQL,5,process=rphost,p:processName= ,t:clientID=3357,t:applicationName=1CV8,t:computerName= ,t:connectID=142664,SessionID=346,Usr=Васильев Николай,Trans=0,dbpid=160,Sql='SELECT
T1.Fld7882RRef,
T1.Fld7886Balance_
FROM (SELECT
T2._Fld7882RRef AS Fld7882RRef,
CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8)) AS Fld7886Balance_
FROM dbo._AccumRgT7888 T2 WITH(NOLOCK)
WHERE T2._Period = ? AND (T2._Fld7882RRef IN
(SELECT
T3._Q_001_F_000RRef AS INVALUELISTRRef
FROM #tt31 T3 WITH(NOLOCK)
WHERE T3._Q_001_F_000RRef IS NOT NULL)) AND (T2._Fld7886 <> ?) AND (T2._Fld7886 <> ?)
GROUP BY T2._Fld7882RRef
HAVING (CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8))) <> 0.0) T1'

Событие SDBL для 150 элементов. Обратите внимание на фрагмент

Fld7882 IN (***СПИСОК 150***)

17:06.213005-61972,SDBL,4,process=rphost,p:processName= ,t:clientID=3357,t:applicationName=1CV8,t:computerName= ,t:connectID=142664,SessionID=346,Usr=Васильев Николай,Trans=0,Sdbl='SELECT
Q_000_T_001.Fld7882,
Q_000_T_001.Fld7886Balance
FROM
(SELECT Fld7882, SUM(Fld7886) AS Fld7886Balance FROM AccumRgT7888 WHERE Period = DATETIME(3999,11,1) AND (Fld7882 IN (70:9c0e002590e2e24b11ed9fa3440bdb0e, ***СПИСОК 150***, 70:9e61002590e2e24b11f0835458988b3f)) AND (Fld7886 <> 0) AND (Fld7886 <> 0) GROUP BY Fld7882 HAVING Fld7886Balance <> 0) Q_000_T_001'

Событие DBMSSQL для 100 элементов. Обратите внимание на фрагмент

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

17:28.517000-62961,DBMSSQL,5,process=rphost,p:processName= ,t:clientID=3357,t:applicationName=1CV8,t:computerName= ,t:connectID=142664,SessionID=346,Usr=Васильев Николай,Trans=0,dbpid=160,Sql='SELECT
T1.Fld7882RRef,
T1.Fld7886Balance_
FROM (SELECT
T2._Fld7882RRef AS Fld7882RRef,
CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8)) AS Fld7886Balance_
FROM dbo._AccumRgT7888 T2 WITH(NOLOCK)
WHERE T2._Period = ? AND ((T2._Fld7882RRef IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) AND (T2._Fld7886 <> ?) AND (T2._Fld7886 <> ?)
GROUP BY T2._Fld7882RRef
HAVING (CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8))) <> 0.0) T1'

Событие SDBL для 100 элементов. Обратите внимание на фрагмент

Fld7882 IN (***СПИСОК 100***)

17:28.517004-62969,SDBL,4,process=rphost,p:processName=ut2013,t:clientID=3357,t:applicationName=1CV8,t:computerName=it_vasilev,t:connectID=142664,SessionID=346,Usr=Васильев Николай,Trans=0,Sdbl='SELECT
Q_000_T_001.Fld7882,
Q_000_T_001.Fld7886Balance
FROM
(SELECT Fld7882, SUM(Fld7886) AS Fld7886Balance FROM AccumRgT7888 WHERE Period = DATETIME(3999,11,1) AND (Fld7882 IN (70:9c0e002590e2e24b11ed9fa3440bdb0e, ***СПИСОК 100***, 70:9d90002590e2e24b11ee73ce25ba51c4)) AND (Fld7886 <> 0) AND (Fld7886 <> 0) GROUP BY Fld7882 HAVING Fld7886Balance <> 0) Q_000_T_001'

При сравнении событий 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

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
	<dump create="false" type="0" prntscrn="false"/>
	<log history="24" location="D:\temp\log">
		<event>
			<eq property="name" value="DBPOSTGRS"/>
			<ge property="duration" value="0"/>
		</event>
		<property name="all"/>
	</log>
	<plansql />

</config>

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

43:23.850000-46999,DBPOSTGRS,6,level=DEBUG,process=rphost,p:processName=test,OSThread=14028,t:clientID=4419,t:applicationName=1CV8C,t:computerName=ServbaseR,t:connectID=117658,SessionID=3,Usr=ВасильевН,AppID=1CV8C,DBMS=DBPOSTGRS,DataBase=test,Trans=0,dbpid=8832,Sql="SELECT
T1.Fld37189RRef,
T1.Fld37195Balance_
FROM (SELECT
T2._Fld37189RRef AS Fld37189RRef,
SUM(T2._Fld37195) AS Fld37195Balance_
FROM _AccumRgT37199 T2
WHERE ((T2._Fld1615 = CAST(0 AS NUMERIC))) AND (T2._Period = '3999-11-01 00:00:00'::timestamp AND ((T2._Fld37189RRef IN ( ***СПИСОК 1500*** ))) AND (T2._Fld37195 <> CAST(0 AS NUMERIC)) AND (T2._Fld37195 <> CAST(0 AS NUMERIC)))
GROUP BY T2._Fld37189RRef
HAVING (SUM(T2._Fld37195)) <> CAST(0 AS NUMERIC)) T1",planSQLText='GroupAggregate  (cost=18905.65..18912.53 rows=403 width=52) (actual time=10.989..11.550 rows=262 loops=1)
  Output: t2._fld37189rref, sum(t2._fld37195)
  Group Key: t2._fld37189rref
  Filter: (sum(t2._fld37195) <> ''0''::numeric)
  Buffers: shared hit=6299
  ->  Sort  (cost=18905.65..18906.05 rows=405 width=23) (actual time=10.982..11.031 rows=925 loops=1)
        Output: t2._fld37189rref, t2._fld37195
        Sort Key: t2._fld37189rref
        Sort Method: quicksort  Memory: 97kB
        Buffers: shared hit=6299
        ->  Nested Loop  (cost=18.22..18894.00 rows=405 width=23) (actual time=0.743..10.497 rows=925 loops=1)
              Output: t2._fld37189rref, t2._fld37195
              Buffers: shared hit=6299
              ->  HashAggregate  (cost=18.00..33.00 rows=1500 width=32) (actual time=0.694..1.041 rows=1500 loops=1)
                    Output: "*VALUES*".column1
                    Group Key: "*VALUES*".column1
                    Batches: 1  Memory Usage: 193kB
                    ->  Values Scan on "*VALUES*"  (cost=0.00..16.50 rows=1500 width=32) (actual time=0.002..0.314 rows=1500 loops=1)
                          Output: "*VALUES*".column1
              ->  Index Scan using _accumrgt37199_1 on public._accumrgt37199 t2  (cost=0.22..12.56 rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=1500)
                    Output: t2._period, t2._fld37189rref, t2._fld37190rref, t2._fld37191rref, t2._fld37192rref, t2._fld37193rref, t2._fld37194rref, t2._fld37195, t2._fld37196, t2._fld1615
                    Index Cond: ((t2._fld1615 = ''0''::numeric) AND (t2._period = ''3999-11-01 00:00:00''::timestamp without time zone) AND (t2._fld37189rref = "*VALUES*".column1))
                    Filter: ((t2._fld37195 <> ''0''::numeric) AND (t2._fld37195 <> ''0''::numeric))
                    Rows Removed by Filter: 0
                    Buffers: shared hit=6299
Planning Time: 2.114 ms
Execution Time: 11.649 ms
',RowsAffected=262,Result=PGRES_TUPLES_OK,Context='Форма.Вызов : ВнешняяОбработка.ВнешняяОбработка2.Форма.Форма1.Модуль.Команда1НаСервере
ВнешняяОбработка.ВнешняяОбработка2.Форма.Форма1.Форма : 29 : РезультатЗапроса = Запрос.Выполнить();'

Видно, что СУБД 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 применяется для группировки.

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

51:43.656000-14981,DBMSSQL,5,process=rphost,p:processName=,t:clientID=2350,t:applicationName=1CV8,t:computerName=,t:connectID=174150,SessionID=400,Usr=Васильев Николай,Trans=0,dbpid=71,Sql=INSERT INTO #tt1 (_Q_000_F_000RRef) VALUES(?),Prm='
*** СПИСОК 150 ***
',RowsAffected=150,planSQLText='

51:43.719000-62999,DBMSSQL,5,process=rphost,p:processName=,t:clientID=2350,t:applicationName=1CV8,t:computerName=,t:connectID=174150,SessionID=400,Usr=Васильев Николай,Trans=0,dbpid=71,Sql='SELECT
T1.Fld7882RRef,
T1.Fld7886Balance_
FROM (SELECT
T2._Fld7882RRef AS Fld7882RRef,
CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8)) AS Fld7886Balance_
FROM dbo._AccumRgT7888 T2 WITH(NOLOCK)
WHERE T2._Period = ? AND (T2._Fld7882RRef IN
(SELECT
T3._Q_000_F_000RRef AS INVALUELISTRRef
FROM #tt1 T3 WITH(NOLOCK)
WHERE T3._Q_000_F_000RRef IS NOT NULL)) AND (T2._Fld7886 <> ?) AND (T2._Fld7886 <> ?)
GROUP BY T2._Fld7882RRef
HAVING (CAST(SUM(T2._Fld7886) AS NUMERIC(32, 8))) <> 0.0) T1
p_0: 59991101000000
p_1: 0N
p_2: 0N
',Rows=2,RowsAffected=-1,planSQLText='
0, 0, 37.4, 0, 3.74E-006, 40, 7.81, 1,   |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(numeric(32,8),[Expr1005],0)))
2, 1, 37.4, 0, 2.23E-005, 40, 7.81, 1,        |--Filter(WHERE:(CONVERT(numeric(32,8),[Expr1005],0)<>(0.00000000)))
2, 1, 38.4, 0, 0.00134, 40, 7.81, 1,             |--Stream Aggregate(GROUP BY:([T2].[_Fld7882RRef]) DEFINE:([Expr1005]=SUM([].[dbo].[_AccumRgT7888].[_Fld7886] as [T2].[_Fld7886])))
2, 1, 2.34E+003, 0, 0.00977, 36, 7.81, 1,                  |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[_Period], [T2].[_Fld7881RRef], [T2].[_Fld7882RRef], [T2].[_Fld7883RRef], [T2].[_Fld7884RRef], [T2].[_Fld7885RRef], [Expr1011]) WITH ORDERED PREFETCH)
4, 1, 2.34E+003, 0, 0.00977, 95, 0.142, 1,                       |--Nested Loops(Inner Join, OUTER REFERENCES:([T3].[_Q_000_F_000RRef], [Expr1010]) WITH ORDERED PREFETCH)
150, 1, 39.4, 0, 9.47E-005, 23, 0.00354, 1,                       |    |--Stream Aggregate(GROUP BY:([T3].[_Q_000_F_000RRef]))
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].[_AccumRgT7888].[_AccumR7888_ByDims_TRRRRR] AS [T2]), SEEK:([T2].[_Period]=[].[dbo].[_AccumRgT7888].[_Period] as [T2].[_Period] AND [T2].[_Fld7881RRef]=[].[dbo].[_AccumRgT7888].[_Fld7881RRef] as [T2].[_Fld7881RRef] AND [T2].[_Fld7882RRef]=[].[dbo].[_AccumRgT7888].[_Fld7882RRef] as [T2].[_Fld7882RRef] AND [T2].[_Fld7883RRef]=[].[dbo].[_AccumRgT7888].[_Fld7883RRef] as [T2].[_Fld7883RRef] AND [T2].[_Fld7884RRef]=[].[dbo].[_AccumRgT7888].[_Fld7884RRef] as [T2].[_Fld7884RRef] AND [T2].[_Fld7885RRef]=[].[dbo].[_AccumRgT7888].[_Fld7885RRef] as [T2].[_Fld7885RRef]),  WHERE:([].[dbo].[_AccumRgT7888].[_Fld7886] as [T2].[_Fld7886]<>CONVERT_IMPLICIT(numeric(21,3),[@P2],0) AND [].[dbo].[_AccumRgT7888].[_Fld7886] as [T2].[_Fld7886]<>CONVERT_IMPLICIT(numeric(21,3),[@P3],0)) LOOKUP ORDERED FORWARD)
'
51:43.719003-1,DBMSSQL,6,process=rphost,p:processName=,t:clientID=2350,t:applicationName=1CV8,t:computerName=,t:connectID=174150,SessionID=400,Usr=Васильев Николай,Trans=0,dbpid=71,Sql=TRUNCATE TABLE #tt1

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

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

+8 –

См. также

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

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

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

18.02.2025 6728 ivanov660 39

Проблемы производительности. Индексация с дополнительным упорядочиванием

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

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

04.02.2025 5465 ivanov660 21

Длительные вызовы - CALL, SCALL

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

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

24.06.2024 9259 ivanov660 13

Что не так с методом ЗаполнитьЗначенияСвойств

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

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

06.06.2024 14908 Evg-Lylyk 73

[Замер] Кто самый быстрый в конфигураторе?

HighLoad оптимизация Механизмы платформы 1С Системный администратор Программист Стажер 1С v8.3 Бесплатно (free)

Сравним «Регистры сведений» с «Сервисами интеграции» и узнаем, кто быстрее!

28.03.2024 7383 dsdred 23

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

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

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

13.03.2024 7277 spyke 29

Быстродействие типовой 1С

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

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

13.03.2024 10494 vasilev2015 22

Анализ SQL сервера

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

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

5 стартмани

15.02.2024 17565 327 ZAOSTG 100

123

Вознаграждение за ответ

Сумма: 0 $m

Добавили:
vasilev2015 (1.00 $m)

Получили:

paulwist (1 $m)

Показать полностью

Комментарии

Подписаться на ответы Инфостарт бот

Свернуть все

1. paulwist 01.10.25 14:44 Сейчас в теме

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

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

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

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

Эээ, а причём тут СУБД MSSQL, на основании чего вы сделали такой вывод??

2. vasilev2015 2813 01.10.25 14:48 Сейчас в теме

(1) на основании текста события DBMSSQL, фрагмент журнала приложен.

3. paulwist 01.10.25 14:54 Сейчас в теме

(2)

на основании текста события DBMSSQL, фрагмент журнала приложен.

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

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

PS Попробуйте в профайлере трассирнуть кусок с IN > 150 значений и посмотрите какой логин/УЗ создаёт временную таблицу, ... обнаружите, что это сделал юзер от имени которого запущен сервис 1С.

4. vasilev2015 2813 01.10.25 15:15 Сейчас в теме

(3) В публикации не указано, что СУБД создает временную таблицу самостоятельно. Скорее всего временную таблицу создает платформа 1С. Спасибо за уточнение. Присылайте трассировку.

5. starik-2005 3192 01.10.25 16:13 Сейчас в теме

На стековерфлоу вопрос о том, использует ли IN индекс:

Обычно так и происходит, если только предложение IN не охватывает слишком большую часть таблицы, и тогда выполняется сканирование таблицы. Лучший способ выяснить это в вашем конкретном случае — запустить запрос в анализаторе запросов и проверить план выполнения.

Т.е. если у вас есть организация, и по ней есть индекс, то все упирается в то, насколько много организаций. Если там три организации и по каждой миллион записей, то какой смысл использовать индекс? Так что и "В" и "=" не будут использовать индекс. А если это ID (ссылка), то индекс будет использоваться - это как бы само собой разумеется. И нужно понимать, что в жизни куда больше случаев с вариантами между этими двумя, и по ним ясность наступает только при выполнении запроса. Т.е. для поля с высокой селективностью будет юзаться индекс, а для поля с низкой и при "=" он скорее всего юзаться не будет. Т.е. системе во втором случае что индекс целиком вычитывать, а потом таблицу, что сразу таблицу - разницы мало.

6. vasilev2015 2813 01.10.25 16:19 Сейчас в теме

(5) Если внутри IN( ... ) запрос из индексированной таблицы - скорее всего, индекс будет использоваться.
Если внутри IN( ... ) список (массив) - скорее всего, индекс НЕ будет использоваться.

7. starik-2005 3192 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.

8. vasilev2015 2813 01.10.25 16:37 Сейчас в теме

(7) Поддерживаю, согласен.

9. starik-2005 3192 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 2813 01.10.25 16:55 Сейчас в теме

(9) Найду план запроса MS SQL, посмотрим вместе. Мне тоже интересно стало ))

15. vasilev2015 2813 03.10.25 08:35 Сейчас в теме

(9) Добавил более подробный план запроса для MS SQL в статью. В этом частном случае индекс не используется.

16. paulwist 03.10.25 11:25 Сейчас в теме

(15)

В этом частном случае индекс не используется.

Ммм, а индекс на какой таблице не используется?? Что-то я не увидел Table/Clustered Index Scan.

17. vasilev2015 2813 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 3192 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).

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 2813 02.10.25 10:03 Сейчас в теме

(12) Аплодирую стоя за такой подробный пост. Но перейти от частных случаев к общему правилу иногда бывает очень трудно логически.

11. PerlAmutor 160 01.10.25 19:23 Сейчас в теме

Осталось теперь убедить обычных пользователей не настраивать отборы в динамических списках и СКД отчетах, куда помещаются все документы из таблицы по условию "В Списке()". Я с таким сталкивался, пользователь добавил 20к ссылок на документы по условию "НЕ В Списке()". Там ни то чтобы отчет долго формировался, там сама форма отчета открывалась несколько минут.

13. vasilev2015 2813 02.10.25 09:57 Сейчас в теме

(11) "Если безобразие нельзя предотвратить, его нужно возглавить!" (С)

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

???

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

Автор:

Николай Васильев (vasilev2015)

Рейтинг: 2813

Для получения уведомлений о новых публикациях автора подключите телеграм бот: Инфостарт бот

Публикация:

№ 2490767

Создание 30.09.25 15:30

Обновление 30.09.25 15:30

Статистика:

Просмотры 662

Загрузки 0

Рейтинг 8

Комментарии 19

Характеристики:

Код открыт Да

Рубрики HighLoad оптимизация

Кому Программист

Тип файла Нет файла

Платформа 1С v8.3

Конфигурация Универсальные

Операционная система Не имеет значения

Страна Не имеет значения

Отрасль Не имеет значения

Налоги Не имеет значения

Вид учета Не имеет значения

Доступ к файлу Бесплатно (free)