Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Доброго дня, коллеги.
Стоит у меня пропатченный MSSSQL 2008
вот два, на первый взгляд одинаковых запроса:
1.
declare @DateNow int;
set @DateNow = 77102;
select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > @DateNow

2.
select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > 77102

Первый на 100% дороже другого.
В планах запроса, в первом случае полный скан индекса (45 Мб), во втором (15байт) и, соответственно, маленький скан.

Если сделать вот так, но, оно конечно же заработает быстро
declare @DateNow int;
set @DateNow = 77102;
declare @sql nvarchar(1000)
set @sql='
select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > ' + cast(@DateNow1 as varchar(10))
execute( @sql)

НО!!! в чём же дело всё-таки, подскажите пожалуйста.
Почему нельзя подставлять переменные в условие?
17 фев 12, 11:02    [12107897]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
iljy
Member

Откуда:
Сообщений: 8711
missing.fox,

какого типа .f_DATEOPEN?
17 фев 12, 11:03    [12107907]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
f_DATEOPEN int
17 фев 12, 11:08    [12107938]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
https://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml
17 фев 12, 11:08    [12107942]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
missing.fox,

Возможно дело в этом. При использовании локаьных переменных сервер не знает их значение, по этому использует оценку в 30% строк при неравенстве в предикате, по этому выбирает скан. Посмотрите на оценочное число строк. В случае константы, оценка точнее и план лучше. Попробуйте добавить option(recompile) в конец первого запроса и посмотреть что изменится.
17 фев 12, 11:09    [12107956]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Такое впечатление, что при указании переменной в условии, реляционный механизм просто тупо сканирует весь индекс (запрос четко проходит по индексу), именно сначала сканирует весь индекс, а не ищет значение ..
17 фев 12, 11:10    [12107964]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
missing.fox
Такое впечатление, что при указании переменной в условии, реляционный механизм просто тупо сканирует весь индекс (запрос четко проходит по индексу), именно сначала сканирует весь индекс, а не ищет значение ..
А с чего ему делать по другому, если он не может оценить?
17 фев 12, 11:11    [12107979]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Glory
Member

Откуда:
Сообщений: 104751
missing.fox
В планах запроса, в первом случае полный скан индекса (45 Мб), во втором (15байт) и, соответственно, маленький скан.

А что такое "маленький скан" ? Так в плане и написано - "Tiny scan" ?
17 фев 12, 11:12    [12107987]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Смешно, спасибо.
при сравнении, действительно не большой.
17 фев 12, 11:16    [12108011]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
LeadyGaaga
Member

Откуда:
Сообщений: 37
В вашем случае, что ипользовать некластерные индексы для скана диапазонов. А я подозреваю, что у вас есть индес по f_DATEOPEN. Нужно включать все столбцы, в вашем случае это f_ACCOUNTANLID для того, чтобы выполнение стабильное было.
17 фев 12, 11:20    [12108058]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Такой индекс создан специально, этот запрос кручу а тестовой среде.
17 фев 12, 11:32    [12108173]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
По всей видимости надо использовать параметризацию ...
17 фев 12, 11:33    [12108181]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Glory
Member

Откуда:
Сообщений: 104751
missing.fox
Такой индекс создан специально, этот запрос кручу а тестовой среде.

Реальные планы то будут предоставлены ?
17 фев 12, 11:33    [12108185]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Вот такая параметризация помогла:

declare @DateNow1 int;

set @DateNow1 = 77102;

EXEC sp_executesql N'select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > @sql', N'@sql int', @DateNow1
17 фев 12, 11:40    [12108264]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Вот план запроса:

К сообщению приложен файл. Размер - 79Kb
17 фев 12, 11:52    [12108376]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Glory
Member

Откуда:
Сообщений: 104751
missing.fox
Вот план запроса:

Эти планы - одинаковые
17 фев 12, 11:55    [12108418]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Crimean
Member

Откуда:
Сообщений: 13147
tpg
https://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml


это уже не совсем неактуально :) надо пользовать option и в нем OPTIMIZE FOR и / или RECOMPILE
17 фев 12, 12:03    [12108513]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
missing.fox
Member

Откуда:
Сообщений: 264
Да, вот результат по времени и скану разный ...
set statistics time on
set statistics io on
declare @DateNow1 int;

set @DateNow1 = 77102;

select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > @DateNow1

select A.f_DATEOPEN
,A.f_ACCOUNTANLID
from t_AccountAnl as A
where A.f_DATEOPEN > 77102

set statistics io on
set statistics time off

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

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

(41671 row(s) affected)
Table 't_AccountAnl'. Scan count 1, logical reads 144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 271 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(41671 row(s) affected)
Table 't_AccountAnl'. Scan count 1, logical reads 144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
17 фев 12, 12:05    [12108529]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Glory
Member

Откуда:
Сообщений: 104751
missing.fox
Да, вот результат по времени и скану разный ...

Во-первых, там не скан, а поиск по индексу
Во-вторых, число статистика чтений тоже одинаковая.
В-третьих, у вашего запроса с константой время как раз больше.
17 фев 12, 12:09    [12108559]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Crimean
tpg
https://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml


это уже не совсем неактуально :) надо пользовать option и в нем OPTIMIZE FOR и / или RECOMPILE
Я имел в виду объяснение, а не решение.
17 фев 12, 12:16    [12108635]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит запрос с переменной в условии  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
missing.fox,

измерять время исполнения запросов в одном пакете не совсем корректно. попробуйте в вашем тесте поменять запросы местами и несколько раз прогоните - бывает, что картина меняется на противоположную. смотреть лучше по чтениям (а здесь они одинаковые), ну или измерьте раз 20 время исполнения каждого запроса независимо от другого и сравните средние значения - будет более правдиво
17 фев 12, 12:30    [12108756]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить