Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Where in vs temp table / 13 сообщений из 13, страница 1 из 1
03.12.2020, 12:59
    #40024206
Grayscale
Grayscale
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Друзья, здравствуйте!

Давно не занимался вопросом. скажите а сейчас есть смысл использовать временные таблицы вместо where in?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 13:13
    #40024212
crutchmaster
crutchmaster
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale,

where in (select id from temp_table) vs join temp_table tt on tt.id = ... ?

Или in (1,2,3, ... 100500)? Так это - плохая идея в общем случае, где-то недавно обсуждали.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 13:19
    #40024213
Grayscale
Grayscale
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
crutchmaster
Grayscale,

where in (select id from temp_table) vs join temp_table tt on tt.id = ... ?

Или in (1,2,3, ... 100500)? Так это - плохая идея в общем случае, где-то недавно обсуждали.


in (1,2,3, ... 100500)

А есть какие-нить пруфы?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 15:17
    #40024266
Grayscale
Grayscale
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Glory! Вернись и помоги. плз!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 15:23
    #40024269
komrad
komrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale

А есть какие-нить пруфы?

помнится раньше Siebel такими конструкциями пользовался
при кол-ве значений на 3-5 листов А4 (образно), сиквел выдавал unable to generate execution plan
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 16:09
    #40024293
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale
Давно не занимался вопросом. скажите а сейчас есть смысл использовать временные таблицы вместо where in?
Да, если в in будет очень много.
Синтаксический анализатор запарится анализировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 20:51
    #40024371
L_argo
L_argo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
У времянки есть важное преимущество - важных полей может быть много. И ее можно вызывать во многих кусках кода.
А в IN еще нужно уметь правильно записать.
А если, к примеру, ключи бинарные ? :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.12.2020, 23:26
    #40024404
Grayscale
Grayscale
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Спасибо, уже кое что) А с точки зрения производительности есть риски какие-нибудь?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2020, 02:11
    #40024425
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale
Спасибо, уже кое что) А с точки зрения производительности есть риски какие-нибудь?


конечно есть.

представьте себе следующую картину:

распределение строк этак:

colrows1102507810010000000555999

Код: sql
1.
2.
3.
select [somecolumn]
from some_big_table
where [col] in (1, 2, 7, ..., N)



использую гистограмму статистики оптимизатор выстроит план в index seek

Код: sql
1.
2.
3.
select [somecolumn]
from some_big_table
where [col] in (select [id] from #tmp);



а вот в этом случае для оптимизатора уже будут некоторые неочевидные вещи.
я кстати не уверен что в таком варианте вообще используются range high key из гистограммы темповой таблицы. корреляция значений является неявной, при этом при достаточно объемной темповой таблицей и неравномерном распределении оно вообще дает аккурат приблизительное распределение.

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

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

и будет ли меняться план в случае когда в темповой таблице будет достаточно много строк что бы распределение по гистограмме превышало порог в 200 шагов и темповая таблица содержала ключ 100 но его не присутствовало бы в range high key
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2020, 11:39
    #40024503
L_argo
L_argo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
автора вот в этом случае для оптимизатора уже будут некоторые неочевидные вещи.Конструкция where .. in (select )
сама по себе не очень оптимальна.
Правильнее использовать обычное объединение или EXISTS()
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2020, 15:14
    #40025282
Grayscale
Grayscale
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
felix_ff,

Спасибо!

С Ваших слов получается что where in (1..n) оптимальнее? А сверху в сообщениях говорят, что
авторИли in (1,2,3, ... 100500)? Так это - плохая идея в общем случае, где-то недавно обсуждали.


авторесли в in будет очень много.
Синтаксический анализатор запарится анализировать.

И тут мне конечно интереснее про общий случай. Понятно что огромный объемпараметров вызовет нагрузку на анализатор и она не буде отражена в плане. Но вот какие еще есть ограничения?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2020, 17:02
    #40025348
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale
felix_ff,

Спасибо!

С Ваших слов получается что where in (1..n) оптимальнее?


Да, в случае когда у Вас не овер 100500 констант в IN

Я точно не помню порог но вроде когда значений приближается к 50 тысячам начинают вылазить "артефакты оптимизатора" вплоть до ошибки 8623.

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


Но обычно такие ситуации разруливаются детально вручную.
Вы можете везде использовать времянку как источник предиката, но в случае когда вы явно видите что конкретный запрос тормозит - тюнить конкретный запрос исходя из значений времянки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.12.2020, 18:04
    #40025370
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Where in vs temp table
Grayscale,

При больших списках будете просто проигрывать на компиляции, причем существенно
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
declare @c int = 20000;
declare @sql nvarchar(max) = N'declare @n int; select @n = Number from master.dbo.spt_values where Number in (%list%)';
declare @list nvarchar(max);

select
 @list = stuff(t.x.value('.', 'nvarchar(max)'), 1, 2, '')
from
 (
  select top (@c)
   ', ' + cast(row_number() over (order by 1/0) as nvarchar(10))
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
  order by
   newid()
  for xml path(''), type
 ) t(x);

set @sql = replace(@sql, '%list%', @list);

--set statistics xml on;

print '---------------------------';
print 'in()';
print '---------------------------';
set statistics io, time on;
exec sys.sp_executesql @sql;
set statistics io, time off;


set @sql = N'declare @n int;

select
 @n = a.Number
from
 master.dbo.spt_values a
where
 exists(select 1 from string_split(@list, '','') where cast(Value as int) = a.Number);'

print '---------------------------';
print 'string_split()';
print '---------------------------';
set statistics io, time on;
exec sys.sp_executesql @sql, N'@list nvarchar(max)', @list;
set statistics io, time off;

--set statistics xml off;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
---------------------------
in()
---------------------------
SQL Server parse and compile time: 
   CPU time = 2313 ms, elapsed time = 2319 ms.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'spt_values'. Scan count 1, logical reads 13, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 2313 ms,  elapsed time = 2322 ms.
---------------------------
string_split()
---------------------------
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'spt_values'. Scan count 20000, logical reads 40000, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 46 ms,  elapsed time = 46 ms.

 SQL Server Execution Times:
   CPU time = 46 ms,  elapsed time = 46 ms.

Completion time: 2020-12-07T18:02:31.6391806+03:00
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Where in vs temp table / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]