Время исполнения запроса с переменной

@2022N2022 · Регистрация: 18.09.2022

Студворк — интернет-сервис помощи студентам

Есть простой скрипт:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE IF EXISTS test
GO
 
CREATE TABLE test(
id INT IDENTITY(1,1) NOT NULL,
dt DATE)
GO
 
INSERT test VALUES ('2024-12-01')
GO
 
DECLARE @val DATE
SET @val=(SELECT MIN(dt) FROM test)
 
SELECT * FROM table1 WHERE field1=@val
SELECT * FROM table1 WHERE field1='2024-12-01'

Думаю, что объяснять не нужно, все просто. Суть вопроса во времени исполнения двух последних селектов. Кажется, что время исполнения должно быть одинаковым. Но на практике оказывается, что select * from table1 where field1=@val выполняется в разы медленнее, чем второй скрипт. Причем чем больше данных в таблице table1, тем больше будет эта разница. Может кто-то объяснить, почему с @val работает все медленно? Заранее спасибо!

@Andrey-MSK · 03.04.2025, 14:19

Сообщение от 2022N2022

Время исполнения запроса с переменной

Добавьте OPTION (RECOMPILE), или оберните это в хранимку, результат будет как и указанной выше опции.

OPTION(RECOMPILE) — это хинт запроса в SQL Server, который указывает оптимизатору перекомпилировать план запроса каждый раз при его выполнении.

Таким образом, SQL Server генерирует оптимизированный план запроса на основе текущих значений любых переменных, используемых в запросе, а не опирается на кэшированный план

@2022N2022 · 03.04.2025, 14:47 **[ТС]**

Да, действительно при указании этой опции время исполнения одинаковое.
А можно объяснить тупому, почему только с option (recompile) это работает? вроде бы и просто так должно работать...

@Andrey-MSK · 03.04.2025, 15:04

2022N2022, RECOMPILE

@pincet · 03.04.2025, 15:46

Andrey-MSK, и почтенная публика - как option(recompile) повлияет на param sniffing? тема весьма древняя, но до сих пор все так?

Добавлено через 1 минуту
2022N2022, и так-то (емнип) select * это полностью задача оптимизатора

@katamoto · 03.04.2025, 16:13

Сообщение от pincet

как option(recompile) повлияет на param sniffing?

А тут точно param sniffing? С ходу больше похоже на использование локальной переменной с вытекающими отсюда нюансами использования статистики

@2022N2022 · 03.04.2025, 16:27 **[ТС]**

Сообщение от Andrey-MSK

2022N2022, RECOMPILE

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

@uaggster · 03.04.2025, 16:27

Сообщение от katamoto

А тут точно param sniffing? С ходу больше похоже на использование локальной переменной с вытекающими отсюда нюансами использования статистики

А вот и проверьте. Запустите сначала с option(recompile), а потом с OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
Этот хинт то ли с 2017, толи с 2019 работает (или вообще с 2016SP2).

Сообщение от 2022N2022

А можно объяснить тупому, почему только с option (recompile) это работает? вроде бы и просто так должно работать...

А оно и так работает. Только в первом случае оно создает параметризованный запрос, и выдергивает его план из кэша планов, а во втором - создает adhoc запрос, заменяя переменные конкретным значением, причем, возможно, проведя некую оптимизацию, вроде приведения литералов к опр. типам.

@Andrey-MSK · 03.04.2025, 16:49

2022N2022, RECOMPILE создаёт временный план запроса, который оптимизирован под текущее значение параметра, делает из параметра константу, выполняет его и удаляет план. И каждый раз при замене значения будет создаваться новый оптимизированный план именно под это значение.

@2022N2022 · 03.04.2025, 18:37 **[ТС]**

Правильно я понимаю, что в любом скрипте, в котором есть переменные, значение которых получается из каких-либо запросов, необходимо использовать option recompil если необходима максимальная скорость выполнения

@katamoto · 03.04.2025, 20:02

Сообщение от uaggster

А вот и проверьте. Запустите сначала с option(recompile), а потом с OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Т.е. сами не проверяли? Чисто теоретически, как там может быть parameter sniffing, если фильтруем по одному и тому же значению в обоих случаях?

Я немного потыкал в студии и вижу, что в первом случае, с локальной переменной, оптимизатор опирается на density vector, а во втором - на гистограмму. Но надо смотреть, какие там у топикстартера планы и статистики, конечно. Пока всё это на кофейной гуще

Добавлено через 2 минуты

Сообщение от 2022N2022

Правильно я понимаю, что в любом скрипте, в котором есть переменные, значение которых получается из каких-либо запросов, необходимо использовать option recompil если необходима максимальная скорость выполнения

It depends. Если у вас запрос пуляется 10500 раз в секунду, то вы скорее всего не заходите каждый раз его перекомпилировать, это затратно по cpu. Для не особо частых - вполне себе

@uaggster · 03.04.2025, 21:26

Сообщение от katamoto

Т.е. сами не проверяли? Чисто теоретически, как там может быть parameter sniffing, если фильтруем по одному и тому же значению в обоих случаях?

Не подтверждается :-)
В том смысле, что ничего не подтверждается, все строго одинаково, с точностью до получения запросом ресурсов на исполнение и прогрева кэша.

T-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
Set statistics io on; 
Set statistics time on;
 
Use testdb
go
 
DROP TABLE IF EXISTS test
GO
 
CREATE TABLE test(
id INT IDENTITY(1,1) NOT NULL,
dt DATE)
GO
 
INSERT test (dt)
Select Case when (ABS(CHECKSUM(NEWID())) % 10) % 2 = 1 then Dateadd(day,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-01')
else Dateadd(day,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-01')
End
from string_split(Replicate(Cast(' ' as varchar(max)), 1000000), ' ') t
 
GO
 
DECLARE @val DATE;
Print '--------------------------------------------------'
Print 'SET @val=(SELECT MIN(dt) FROM test)'
SET @val=(SELECT MIN(dt) FROM test)
Print '--------------------------------------------------'
Print 'SELECT * FROM test WHERE dt=@val'
SELECT * FROM test WHERE dt=@val
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=''20241201'''
SELECT * FROM test WHERE dt='20241201'
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val option(recompile)'
SELECT * FROM test WHERE dt=@val option(recompile)
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val OPTION (USE HINT (''DISABLE_PARAMETER_SNIFFING''))'
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Результат:

Code
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 3 ms.
Table 'test'. Scan count 0, logical reads 1003718, 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 4000013, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 5017768, lob physical reads 0, lob page server reads 0, lob read-ahead reads 490440, lob page server read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 6265 ms,  elapsed time = 6391 ms.
 
(1000001 rows affected)
SQL Server parse and compile time: 
   CPU time = 168 ms, elapsed time = 168 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SET @val=(SELECT MIN(dt) FROM test)
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'test'. Scan count 1, logical reads 3718, 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 = 140 ms,  elapsed time = 145 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
(99904 rows affected)
Table 'test'. Scan count 1, logical reads 3718, 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 = 157 ms,  elapsed time = 156 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt='20241201'
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
(99904 rows affected)
Table 'test'. Scan count 1, logical reads 3718, 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 = 156 ms,  elapsed time = 156 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val option(recompile)
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
(99904 rows affected)
Table 'test'. Scan count 1, logical reads 3718, 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 = 140 ms,  elapsed time = 153 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
(99904 rows affected)
Table 'test'. Scan count 1, logical reads 3718, 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 = 156 ms,  elapsed time = 157 ms.
 
Completion time: 2025-04-03T22:23:40.4503924+04:00

@katamoto · 03.04.2025, 21:49

Сообщение от uaggster

Не подтверждается :-)
В том смысле, что ничего не подтверждается, все строго одинаково, с точностью до получения запросом ресурсов на исполнение и прогрева кэша.

На равномерно распределённых данных - ну да, кто бы сомневался...

P.S. Плюс индекс нужен по dt, иначе, ясно дело, всё в банальный table scan в любом случае свалится. Ну, и кластерный по id, скорее всего. Не вижу других вариантов, чтоб получить описываемую ситуацию

@Andrey-MSK · 04.04.2025, 10:54

Сообщение от 2022N2022

в котором есть переменные

В котором они есть и не важно откуда берутся их значения.
2022N2022,

Сообщение от katamoto

это затратно по cpu

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

Что самое интересное, если вызывать запросы вот так

C#
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
48
49
50
51
52
53
public async Task<List<LaborHBHeader>> GetLaborHBHeadersAsync(Branches branch)
{
    List<LaborHBHeader> headers = new List<LaborHBHeader>();
    string sqlText = "SELECT ID_LaborHB, ID_Branch, DateHB, DecreeHB, VersionHB FROM dbo.tblLaborHB_00 WHERE ID_Branch = @idBranch ORDER BY DateHB";
 
    try
    {
        using (SqlConnection sqlConnection = new SqlConnection(_connectionString))
        {
            using (SqlCommand sqlCommand = new SqlCommand(sqlText, sqlConnection))
            {
                SqlParameter pBranchID = new SqlParameter
                {
                    ParameterName = "@idBranch",
                    Value = (int)branch,
                    SqlDbType = SqlDbType.Int,
                    Direction = ParameterDirection.Input
                };
                sqlCommand.Parameters.Add(pBranchID);
 
                await sqlCommand.Connection.OpenAsync();
 
                using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
                {
                    if (sqlDataReader.HasRows)
                    {
                        while (await sqlDataReader.ReadAsync())
                        {
                            headers.Add(new LaborHBHeader
                            {
                                LaborHBID = (int)sqlDataReader["ID_LaborHB"],
                                BranchID = (int)sqlDataReader["ID_Branch"],
                                DateHB = (DateTime)sqlDataReader["DateHB"],
                                DescreeHB = (string)sqlDataReader["DecreeHB"],
                                VersionHB = (int)sqlDataReader["VersionHB"]
                            });
                        }
                    }
                }
            }
        }
 
        return headers;
    }
    catch (SqlException sqlEx)
    {
        throw new ApplicationException(string.Format("T-SQL #{0} - {1}", sqlEx.Number, sqlEx.Message), sqlEx);
    }
    catch (Exception ex)
    {
        throw new ApplicationException(ex.Message, ex);
    }
}

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

Добавлено через 21 минуту
То бишь план всегда оптимизирован под эти значения. Вот такая особенность

@katamoto · 04.04.2025, 10:57

Сообщение от Andrey-MSK

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

С recompile? Каким образом оптимизирует?

Сообщение от Andrey-MSK

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

Тут ничего не могу сказать только по этому коду, не разраб. Надо смотреть, что там на стороне базы происходит при этом

@PaulWist · 04.04.2025, 11:05

Сообщение от uaggster

все строго одинаково

Ммм, ну я бы сказал, что всё строго разно (за исключением первого и последнего оператора)

T-SQL
1
SELECT * FROM test WHERE dt=@val

Не использует прослушивание параметра, план компилится под 30%.

T-SQL
1
SELECT * FROM test WHERE dt='20241201'

Тут, вообще Simple parameterization с прослушиванием параметра (компиляцией плана со значением dt='20241201')

T-SQL
1
SELECT * FROM test WHERE dt=@val option(recompile)

Тут, нет параметров вообще.

T-SQL
1
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Здесь, как и ожидалось прослушивание отключено, план компилится под 30%.

@uaggster · 04.04.2025, 11:07

Сообщение от katamoto

P.S. Плюс индекс нужен по dt, иначе, ясно дело, всё в банальный table scan в любом случае свалится. Ну, и кластерный по id, скорее всего. Не вижу других вариантов, чтоб получить описываемую ситуацию

Всё равно не подтверждается.

T-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
Set statistics io on; 
Set statistics time on;
 
Use testdb
go
 
DROP TABLE IF EXISTS test
GO
 
CREATE TABLE test(
id INT IDENTITY(1,1) NOT NULL primary key clustered,
dt DATE, index ix_dt (dt))
GO
 
INSERT test (dt)
Select Case when (ABS(CHECKSUM(NEWID())) % 10) % 2 = 1 then Dateadd(day,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-01')
else Dateadd(month,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-01')
End
from string_split(Replicate(Cast(' ' as varchar(max)), 10000000), ' ') t
 
GO
 
DECLARE @val DATE;
Print '--------------------------------------------------'
Print 'SET @val=(SELECT MIN(dt) FROM test)'
SET @val=(SELECT MIN(dt) FROM test)
Print '--------------------------------------------------'
Print 'SELECT * FROM test WHERE dt=@val'
SELECT * FROM test WHERE dt=@val
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=''20241201'''
SELECT * FROM test WHERE dt='20241201'
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val option(recompile)'
SELECT * FROM test WHERE dt=@val option(recompile)
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val OPTION (USE HINT (''DISABLE_PARAMETER_SNIFFING''))'
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Code
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'test'. Scan count 0, logical reads 60098934, 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 40000013, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 50185737, lob physical reads 0, lob page server reads 0, lob read-ahead reads 43890720, lob page server read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 104844 ms,  elapsed time = 105916 ms.
 
(10000001 rows affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SET @val=(SELECT MIN(dt) FROM test)
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 251 ms, elapsed time = 251 ms.
Table 'test'. Scan count 1, logical reads 3, 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 = 0 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
 
(998600 rows affected)
Table 'test'. Scan count 1, logical reads 3360, 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 = 469 ms,  elapsed time = 7523 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt='20241201'
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
(998600 rows affected)
Table 'test'. Scan count 1, logical reads 3360, 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 = 515 ms,  elapsed time = 9034 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val option(recompile)
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
(998600 rows affected)
Table 'test'. Scan count 1, logical reads 3360, 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 = 610 ms,  elapsed time = 9904 ms.
--------------------------------------------------
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
 
(998600 rows affected)
Table 'test'. Scan count 1, logical reads 3360, 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 = 672 ms,  elapsed time = 9004 ms.
 
Completion time: 2025-04-04T12:04:19.4482201+04:00

Сообщение от katamoto

На равномерно распределённых данных - ну да, кто бы сомневался...

Там как раз НЕ равномерно распределенные данные.

Code
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
Statistics for INDEX 'ix_dt'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
                            Name                         Updated                            Rows                    Rows Sampled                           Steps                         Density              Average Key Length                    String Index
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                           ix_dt             Apr  4 2025 11:03AM                        10000001                          384670                              19                               0                               7                             NO                                                         10000001                               0
 
                     All Density                  Average Length                         Columns
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      0.05263158                               3                              dt
                    2.572493E-06                               7                          dt, id
 
                 Histogram Steps
                    RANGE_HI_KEY                      RANGE_ROWS                         EQ_ROWS             DISTINCT_RANGE_ROWS                  AVG_RANGE_ROWS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              01.12.2024 0:00:00                               0                        996412,6                               0                               1
              02.12.2024 0:00:00                               0                        495567,7                               0                               1
              03.12.2024 0:00:00                               0                        501728,8                               0                               1
              04.12.2024 0:00:00                               0                        501156,9                               0                               1
              05.12.2024 0:00:00                               0                        493929,9                               0                               1
              06.12.2024 0:00:00                               0                        496867,5                               0                               1
              07.12.2024 0:00:00                               0                        501494,8                               0                               1
              08.12.2024 0:00:00                               0                        499155,2                               0                               1
              09.12.2024 0:00:00                               0                        501026,9                               0                               1
              10.12.2024 0:00:00                               0                        500870,9                               0                               1
              01.01.2025 0:00:00                               0                          507370                               0                               1
              01.02.2025 0:00:00                               0                        494501,8                               0                               1
              01.03.2025 0:00:00                               0                        506486,1                               0                               1
              01.04.2025 0:00:00                               0                        501260,9                               0                               1
              01.05.2025 0:00:00                               0                        505576,3                               0                               1
              01.06.2025 0:00:00                               0                        497101,5                               0                               1
              01.07.2025 0:00:00                               0                        495411,7                               0                               1
              01.08.2025 0:00:00                               0                        498011,3                               0                               1
              01.09.2025 0:00:00                               0                        506070,2                               0                               1

@Andrey-MSK · 04.04.2025, 11:17

Сообщение от katamoto

Каким образом оптимизирует?

Либо OPTION(RECOMPILE), либо сделать из запроса хранимку - результат будет одинаковый. План будет одинаковый, сколько раз замечал такое. Без опции, если просто запрос, то вызывается куча LOOP для поиска, а если его обернуть в ХП, то сразу всё приходит в норму. Да и писали про это не раз...

Сообщение от katamoto

Надо смотреть, что там на стороне базы происходит при этом

SSMS показывает как обычно

T-SQL
1
@id = 23

но при этом план запроса получается как с опцией RECOMPILE. Видать особенность класса SqlCommand()...

@katamoto · 04.04.2025, 11:54

Сообщение от uaggster

Всё равно не подтверждается.

Давай так

T-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
Set statistics io on; 
Set statistics time on;
 
Use tempdb
go
 
DROP TABLE IF EXISTS test
GO
 
CREATE TABLE test(
id INT IDENTITY(1,1) NOT NULL primary key clustered,
dt DATE, index ix_dt (dt),
payload UNIQUEIDENTIFIER CONSTRAINT df_payload DEFAULT(NEWID()))
GO
 
INSERT test (dt)
VALUES ('2024-12-01')
 
INSERT test (dt)
Select Case when (ABS(CHECKSUM(NEWID())) % 10) % 2 = 1 then Dateadd(day,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-02')
else Dateadd(month,(ABS(CHECKSUM(NEWID())) % 10) ,'2024-12-02')
End
from string_split(Replicate(Cast(' ' as varchar(max)), 1000000), ' ') t
 
GO
 
DECLARE @val DATE;
Print '--------------------------------------------------'
Print 'SET @val=(SELECT MIN(dt) FROM test)'
SET @val=(SELECT MIN(dt) FROM test)
Print '--------------------------------------------------'
Print 'SELECT * FROM test WHERE dt=@val'
SELECT * FROM test WHERE dt=@val
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=''20241201'''
SELECT * FROM test WHERE dt='20241201'
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val option(recompile)'
SELECT * FROM test WHERE dt=@val option(recompile)
Print '--------------------------------------------------'
print 'SELECT * FROM test WHERE dt=@val OPTION (USE HINT (''DISABLE_PARAMETER_SNIFFING''))'
SELECT * FROM test WHERE dt=@val OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Добавлено через 1 минуту

Сообщение от Andrey-MSK

Либо OPTION(RECOMPILE), либо сделать из запроса хранимку - результат будет одинаковый. План будет одинаковый, сколько раз замечал такое. Без опции, если просто запрос, то вызывается куча LOOP для поиска, а если его обернуть в ХП, то сразу всё приходит в норму. Да и писали про это не раз..

Просто запрос - это что имеется в виду? В ssms?

@Andrey-MSK · 04.04.2025, 11:57

Сообщение от katamoto

В ssms?

Ага. Просто SELECT с параметрами...

Новые блоги и статьи Все статьи Все блоги /
Валидация и контроль данных табличной части документа перед записью Maks 22.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа, разработанного в КА2. Задача: контроль и валидация данных табличной части документа перед записью с учетом регламента компании. . .	Отчёт о затраченных материалах за определенный период с макетом печатной формы Maks 21.04.2026 Отчёт из решения ниже размещён в конфигурации КА2. Задача: разработка отчёта по затраченным материалам за определённый период, с возможностью вывода печатной формы отчёта с шапкой и подвалом. В. . .	Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .
Запрет удаления строк ТЧ документа при определённом условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .

@2022N2022 2 / 2 / 0 Регистрация: 18.09.2022 Сообщений: 242
	03.04.2025, 14:47 [ТС]
	Да, действительно при указании этой опции время исполнения одинаковое. А можно объяснить тупому, почему только с option (recompile) это работает? вроде бы и просто так должно работать... 0

@Andrey-MSK 3362 / 2248 / 388 Регистрация: 14.08.2018 Сообщений: 7,596 Записей в блоге: 4
	03.04.2025, 15:04
	2022N2022, RECOMPILE 2

@pincet 1655 / 1154 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	03.04.2025, 15:46
	Andrey-MSK, и почтенная публика - как option(recompile) повлияет на param sniffing? тема весьма древняя, но до сих пор все так? Добавлено через 1 минуту 2022N2022, и так-то (емнип) select * это полностью задача оптимизатора 0

@Andrey-MSK 3362 / 2248 / 388 Регистрация: 14.08.2018 Сообщений: 7,596 Записей в блоге: 4
	03.04.2025, 16:49
	2022N2022, RECOMPILE создаёт временный план запроса, который оптимизирован под текущее значение параметра, делает из параметра константу, выполняет его и удаляет план. И каждый раз при замене значения будет создаваться новый оптимизированный план именно под это значение. 1

@2022N2022 2 / 2 / 0 Регистрация: 18.09.2022 Сообщений: 242
	03.04.2025, 18:37 [ТС]
	Правильно я понимаю, что в любом скрипте, в котором есть переменные, значение которых получается из каких-либо запросов, необходимо использовать option recompil если необходима максимальная скорость выполнения 0

Время исполнения запроса с переменной

Решение