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

Откуда:
Сообщений: 651
Всем добрый день!
В запросе на выборку используются опциональные параметры по следующей схеме:
SELECT name FROM some_table WHERE (id = @p1 OR @p1 IS NULL) AND (name = @p2 OR @p2 IS NULL)

Оптимизатор генерирует, ессно, Index Scan.
Если в запрос добавить OPTION(RECOMPILE), он оценит значение конкретного параметра и построит более эффективный Index Seek.
Как можно этот, более оптимальный план, прикрутить к запросу без использований OPTION(RECOMPILE)? Я пробовал разное - не вышло.
Вот тестовый код, который покажет разницу планов:

-- включаем показ планов
CREATE TABLE test_table(
	id INT IDENTITY(1,1) NOT NULL, 
	name varchar(10),  
	CONSTRAINT [pk_test_table] PRIMARY KEY CLUSTERED (id ASC))
GO
INSERT INTO test_table(name) VALUES ('a'),('b'),('c')
GO
DECLARE @p INT = 1
SELECT name FROM test_table WHERE id = @p OR @p IS NULL
SELECT name FROM test_table WHERE id = @p OR @p IS NULL OPTION(RECOMPILE)
GO
DROP TABLE test_table
GO

зы: разница в планах видна не на всех версиях MS SQL. На моем SQL Server 2008 (RTM) - 10.0.1600.22 (X64) - есть.
17 авг 11, 18:08    [11135290]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov,

как вы себе представляете использование INDEX SEEK если параметр не задан? Можно схитрить в таком духе:
select top(case when @p is null then 0 else 9999999999999 end) * from some_table
where id = @p
    union all
select top(case when @p is not null then 0 else 9999999999999 end) * from some_table
, но с несколькими параметрами будет уже хуже.
17 авг 11, 18:20    [11135370]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
iljy, можно еще примерно так:

select * from some_table where id = @p
union all
select * from some_table where @p is null
план будет не оч красивым, но главное - при заполненном параметре scan тупо не запустится ни разу, что в целом решает задачу. Только да, при нескольких параметрах - засада.

А, ну дык вопрос. Могу ли я сказать оптимизатору "делай по этому, плану, сцуко, а если не подойдет - скомпиль себе какой хочешь"? Тем паче, план-то хороший, годный :)
17 авг 11, 18:33    [11135424]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov
iljy, можно еще примерно так:

select * from some_table where id = @p
union all
select * from some_table where @p is null
план будет не оч красивым, но главное - при заполненном параметре scan тупо не запустится ни разу, что в целом решает задачу.

Очень даже запустится, потому что условие будет проверяться для каждой записи. Чтобы ни разу не запустился, требуется перекомпиляция на основе известного значения. Либо отсечение на основе TOP.

Dmitry Gurianov

А, ну дык вопрос. Могу ли я сказать оптимизатору "делай по этому, плану, сцуко, а если не подойдет - скомпиль себе какой хочешь"? Тем паче, план-то хороший, годный :)

Чтобы узнать, подходит план или нет, опять же требуется перекомпиляция.

Можно строить динамику в зависимости от параметров, тогда в конце концов будут использоваться кешированные планы. Короче решения есть, возникает только один вопрос - чего вы пытаетесь добиться?
17 авг 11, 18:53    [11135505]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
iljy
Очень даже запустится, потому что условие будет проверяться для каждой записи. Чтобы ни разу не запустился, требуется перекомпиляция на основе известного значения. Либо отсечение на основе TOP.


не-а. Запустите это:
CREATE TABLE test_table(
	id INT IDENTITY(1,1) NOT NULL, 
	name varchar(10),  
	CONSTRAINT [pk_test_table] PRIMARY KEY CLUSTERED (id ASC))
GO
INSERT INTO test_table(name) VALUES ('a'),('b'),('c')
GO
DECLARE @p INT = 1
SELECT * FROM
(SELECT * FROM test_table where id = @p
UNION all
SELECT * FROM test_table where @p is null) t
GO
DROP TABLE test_table
GO

iljy
Короче решения есть, возникает только один вопрос - чего вы пытаетесь добиться?

Счастья, всем, даром :)
Имеется хранимка, которая вытаскивает сущность по нескольким опциональным праметрам. Я хочу, чтобы ее селект работал по наиболее оптимальному для данных параметров плану. В частности, чтоб не делал Scan, если параметр не NULL.
17 авг 11, 19:03    [11135542]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov
не-а. Запустите это:

А, ну да, в 2008 появилась дополнительная оптимизация - оно теперь перед выполнением умеет отсекать заведомо ложные условия. На форуме это где-то мелькало кстати, не так давно.

Dmitry Gurianov
iljy
Короче решения есть, возникает только один вопрос - чего вы пытаетесь добиться?

Счастья, всем, даром :)
Имеется хранимка, которая вытаскивает сущность по нескольким опциональным праметрам. Я хочу, чтобы ее селект работал по наиболее оптимальному для данных параметров плану. В частности, чтоб не делал Scan, если параметр не NULL.

Счастья даром не бывает. Для одного параметра все элементарно, а если параметров несколько? Чего делать бедному оптимизатору, если проверяется скажем параметра 4?
17 авг 11, 19:20    [11135606]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
iljy
Чего делать бедному оптимизатору, если проверяется скажем параметра 4?

Ну как-то же он справляется с трудностями, оптмизируя запрос, содержаший 2 логические ветки (выше пример с OR) таким образом, как будто их - всего одна? И непонятно: если он _вообще_ в состоянии скомпилировать план с seek, который, по его же мнению, подходит для этого запроса, то почему этот же план ему никаким образом нельзя подсунуть? Почему с помощью USE PLAN это не получается? Почему ему таким же образом не сделать для 4 параметров 4 seek-a, а не 4 scan-a?
18 авг 11, 02:28    [11136831]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov,

какие 4 seek?? У вас выборка идет по одной таблице, и поиск может для разных комбинаций параметров вестись по разным индексам! При компиляции оптимизатор сначала просто проводит оптимизацию условий, отбрасывая лишние, а потом для того, что осталось, строит оптимальный план, учитывая при этом наличие идексов и статистику распределения. И в полученном плане проверки лишних условий просто НЕТ, и для другой комбинации параметров он ну вообще не подходит. А вы чего хотите чтобы он делал?
18 авг 11, 10:24    [11137535]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov,

не, в принципе конечно нет препядствий подготовленному уму: вы можете сами перевести логическое выражение на язык множеств и использовать INTERSECT и UNION. Но план в итоге будет такой, что в 99.(9)% случаев дешевле будет перекомпилять.
18 авг 11, 10:30    [11137581]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
iljy
Dmitry Gurianov,

какие 4 seek?? У вас выборка идет по одной таблице, и поиск может для разных комбинаций параметров вестись по разным индексам! При компиляции оптимизатор сначала просто проводит оптимизацию условий, отбрасывая лишние, а потом для того, что осталось, строит оптимальный план, учитывая при этом наличие идексов и статистику распределения. И в полученном плане проверки лишних условий просто НЕТ, и для другой комбинации параметров он ну вообще не подходит. А вы чего хотите чтобы он делал?


Прошу прощения, когда отвечал про 4 параметра, имел в виду случай, когда эти параметры являются фильтрами для разных таблиц в случае запроса, содержащего несколько джоинов. Если речь про 1 таблицу - то все как обычно: выбирает наиболее подходящий seek, остальное - условиями. В частности, если запрос такой:
select id, firstname, lastname from users where (id = @id or @id is null) and (firstname = @firstname or @firstname is null) and(lastname = @lastname or @lastname is null)
и переданы параметры
@id = 1, @firstname = null, lastname = 'Иванов', и в таблице стоит кластерный PK на поле id, то хочется получить seek на id, а остальное пусть фильтрует в оптимальном для себя порядке.
Если переданы параметры
@id = null, @firstname = 'Вася', lastname = 'Иванов', и в таблице есть индекс на люое из этих полей - то seek по ним.
Еще раз подчеркиваю: он умеет строить seek-планы для запросов типа (id = @id or @id is null) - так почему бы ему не принять от меня такой план в хинте USE PLAN?
18 авг 11, 11:49    [11138427]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
iljy
Dmitry Gurianov,

не, в принципе конечно нет препядствий подготовленному уму: вы можете сами перевести логическое выражение на язык множеств и использовать INTERSECT и UNION. Но план в итоге будет такой, что в 99.(9)% случаев дешевле будет перекомпилять.


Дружище, я знаю несколько способов решить эту задачу. Мой вопрос - в другом, и изложен в первом посте.
18 авг 11, 11:53    [11138464]     Ответить | Цитировать Сообщить модератору
 Re: Опциональные параметры. Хитрый план. Как подставить?  [new]
iljy
Member

Откуда:
Сообщений: 8711
Dmitry Gurianov
Еще раз подчеркиваю: он умеет строить seek-планы для запросов типа (id = @id or @id is null) - так почему бы ему не принять от меня такой план в хинте USE PLAN?

Сервер умеет строить и использовать универсальные планы, если ему помочь.
+ Пример
if OBJECT_ID('test_table', 'U') is not null drop table test_table

CREATE TABLE test_table(
	id INT IDENTITY(1,1) primary key, 
	name varchar(20) unique
)

INSERT INTO test_table(name)
select top 100000 'Object'+ltrim(STR(ROW_NUMBER() over(order by(select 1))))
from sys.objects o1, sys.objects o2, sys.objects o3
GO
DECLARE @p INT = 1, @s varchar(20) ='Object1'
(
	SELECT * FROM test_table WHERE id = @p 
		union all
	select * from test_table where @p IS NULL
)
	intersect
(
	SELECT * FROM test_table WHERE name = @s
		union all
	select * from test_table where @s IS NULL
)
GO
DROP TABLE test_table

А начет использовать план, который вы ему подсовываете, я уже написал: чтобы проверить, можно ли это сделать, сервер должен провести компиляцию с подстановкой параметров, т.е. выполнить всю работу по OPTION(RECOMPILE). Так спрашивается - зачем ему после этого использовать ваш план, если у него уже фактически есть свой??
18 авг 11, 19:52    [11142260]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить