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

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

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

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

<?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'

<?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 : РезультатЗапроса = Запрос.Выполнить();'

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