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

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

Вот пример:

CREATE TABLE #T (id1 int)

INSERT INTO #T
SELECT 1
UNION
SELECT 2
UNION
SELECT 3


Предположим что нам нам пользователь задает id1 или не задает.

Пробуем разные запросы.
Задаем конкретно значение

DECLARE @i int

set @i = 2

SELECT * FROM #T
where id1 = @i


В первом случае делаем в плане запроса видим Index SEEK

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

DECLARE @i int
set @i = null

SELECT * FROM #T
where id1 = ISNULL(@i,id1)

set @i = 1

SELECT * FROM #T
where id1 = ISNULL(@i,id1)

В данном случае в обоих вариант делается Index SCAN

Подскажите, почему?

И вопрос.
Как-то можно хитро написать запрос, который в любом случае всегда бы делал SEEK ?
Задали значение явно его указали id1 = @i, Если не указали, то по сути это условие на это поле нам не нужно.
18 дек 14, 15:05    [17017543]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
scanorseek
В первом случае делаем в плане запроса видим Index SEEK

Откуда, если нет индекса ?

scanorseek
Подскажите, почему?

Потому что переменная и функция - это разные вещи.
18 дек 14, 15:09    [17017578]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
Забыл дописать вначале.

Делаем вот такое еще:

CREATE INDEX aaa ON #t (id1)
18 дек 14, 15:13    [17017604]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
Результат такой:

DECLARE @i int

set @i = 1

SELECT * FROM #T
WHERE (@I IS NULL OR ID1 = @i)


Тоже дает Index SCAN
18 дек 14, 15:15    [17017616]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
scanorseek
Тоже дает Index SCAN

И что в этом не так ?
Если вы хотите, чтобы план конкретного запроса пере-генерировался после присвоения значений переменным, используйте соответствующий хинт
18 дек 14, 15:18    [17017637]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
WHERE (@I IS NULL OR ID1 = @i)


Если я задал @i как не равную NULL, то почему он делает SCAN, а не SEEK ?
А когда явно задал он сделал SEEK.

Какой хинт соответствующий? Название его. Спасибо.
18 дек 14, 15:27    [17017716]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
http://technet.microsoft.com/ru-ru/library/bb510478(v=sql.105).aspx
18 дек 14, 15:29    [17017729]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
scanorseek
Если я задал @i как не равную NULL, то почему он делает SCAN, а не SEEK ?

Потому, что план выполнения пакета создается ДО его выполнения. Когда еще ничего никуда не присвоено.
А для вашего случае есть название - parameters sniffing

scanorseek
Какой хинт соответствующий? Название его. Спасибо.

Все хинта описаны в хелпе

Сообщение было отредактировано: 18 дек 14, 15:31
18 дек 14, 15:30    [17017739]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
Konst_One
http://technet.microsoft.com/ru-ru/library/bb510478(v=sql.105).aspx


Спасибо за данный вариант, НО....
Все бы хорошо, но установлен SQL 2005 :(
18 дек 14, 15:43    [17017813]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
читайте хэлп своей версии, найдёте что-нибудь
18 дек 14, 15:45    [17017818]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
scanorseek
WHERE (@I IS NULL OR ID1 = @i)



Если я задал @i как не равную NULL, то почему он делает SCAN, а не SEEK ?
А когда явно задал он сделал SEEK.

Какой хинт соответствующий? Название его. Спасибо.
@I IS NULL OR ID1 = @i
и
ID1 = ISNULL(@i,ID1)
- это совершенно разные выражения, дающие разный результат.
Почему Вы их пишете так, как будто это одно и то же, непонятно.
Поскольку индекс можно сделать только по полю таблицы,
а во втором случае в сравнении участвует не само поле, а функция от него,
то никакой индекс применить невозможно, ибо не бывает индекса для функции.
18 дек 14, 16:10    [17017996]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
iap, Тогда каким образом пишутся подобные запросы, когда пользователь фильтрует по конкретному значению или просит показать ему все данные из таблицы?

Как в этом случае написать оптимальный запрос?

Я понимаю что можно написать что-то вида:

if @i is null
select * from #t
else
select * from #t
where @i=i


Но этих переменных в реальном запросе штук 7-8 и такие условия не подойдут.

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

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

Вот и спрашиваю мнение опытных, кто возможно встречался с подобными задачами и как их решать.

Спасибо.
18 дек 14, 16:24    [17018101]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Glory
Member

Откуда:
Сообщений: 104751
scanorseek
Но этих переменных в реальном запросе штук 7-8 и такие условия не подойдут.

И о каких поисках по индексу вы тогда говорите ?
18 дек 14, 16:25    [17018112]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
scanorseek
Есть вариант написать динамический запрос, который склеит или проверку по переменной для поля или просто опустит это условие если пользователь ее не задал.

Хочется добиться максимальной производительности подобного вида запросов.
Динамический SQL и sp_executesql будет самым быстрым вариантом.
18 дек 14, 20:11    [17019218]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
alexeyvg
scanorseek
Есть вариант написать динамический запрос, который склеит или проверку по переменной для поля или просто опустит это условие если пользователь ее не задал.

Хочется добиться максимальной производительности подобного вида запросов.
Динамический SQL и sp_executesql будет самым быстрым вариантом.
лучше, чем статика плюс option(recompile) ?
18 дек 14, 20:20    [17019251]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
Shakill
alexeyvg
пропущено...
Динамический SQL и sp_executesql будет самым быстрым вариантом.
лучше, чем статика плюс option(recompile) ?
Там же будет рекомпиляция постоянно, на неё тоже ресурсы нужны.
А с sp_executesql сервер запомнит кучу планов, с разным сочетанием параметров, и будет их вытаскивать. То есть если это часто вызываемый запрос, быстрее sp_executesql, если редко, то можно делать рекомпиляцию.
18 дек 14, 21:14    [17019436]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
alexeyvg
Shakill
пропущено...
лучше, чем статика плюс option(recompile) ?
Там же будет рекомпиляция постоянно, на неё тоже ресурсы нужны.
А с sp_executesql сервер запомнит кучу планов, с разным сочетанием параметров, и будет их вытаскивать. То есть если это часто вызываемый запрос, быстрее sp_executesql, если редко, то можно делать рекомпиляцию.

в общем-то да, у меня при виде условного фильтра "всё или конкретный объект" сразу пришла мысль о каком-то отчете, достаточно сложном и не слишком частом, поэтому и recompile, а автор условий работы запроса (насколько сложный, как часто вызывается) не озвучил
18 дек 14, 21:28    [17019510]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
scanorseek,

Dynamic Search Conditions in T-SQL

Читайте, вникайте. Есть варианты и для 2008 и для более старых версий.
18 дек 14, 22:34    [17019817]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
scanorseek
Guest
Входных параметров как я написал штук 7-8, которые могут быть заданы или все или частично или не заданы вовсе.

Пользуются ими человек 20-30. Запускают ежедневно по 100-200 раз.

alexeyvg, Как понимаю, тогда лучше переписать на sp_executesql в динамике?

Mind, Спасибо! То, что надо :) Прям один в один идет освещение этой проблем от начала до конца.
19 дек 14, 02:18    [17020464]     Ответить | Цитировать Сообщить модератору
 Re: Использование индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
scanorseek
Входных параметров как я написал штук 7-8, которые могут быть заданы или все или частично или не заданы вовсе.

Пользуются ими человек 20-30. Запускают ежедневно по 100-200 раз.

alexeyvg, Как понимаю, тогда лучше переписать на sp_executesql в динамике?

Mind, Спасибо! То, что надо :) Прям один в один идет освещение этой проблем от начала до конца.
Да, лучше так.

По ссылке от Mind тоже нужно почитать, там всё это подробнее рассматривается, много практических приёмов, про которые тут не писали из за объёма.
Например, выделить наиболее часто встречающиеся варианты, сделать их статическими запросами (в IF), а оставшееся - в одном запросе с опцией RECOMPILE
Или выделение некоторых из параметров в таблицы переменные, если речь, например, о небольших справочниках - вполне эффективно.
19 дек 14, 08:50    [17020731]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить