Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MS SQL Server > SQL Server 2008r2, процедуры с динамическим SQL

Автор: lv151 5.12.2014, 08:53

SQL Server 2008r2
Есть процедура, которая в зависимости от выбранных полей
на клиенте, строит SELECT запрос, который представляет собой динамический SQL:

Код


SELECT f1 FROM table1
или
SELECT f1, f2 FROM table1
или
SELECT t1.f4, t2.f5 FROM table1 t1
INNER JOIN table2 t2
ON t1.f1 = t2.f1
и т.д. и т.п.

Вопрос. Я думаю, что для данной процедуры хранится не оптимальный план запроса.
Т.е. если после выполнения

Код


SELECT f1 FROM table1

выполнится

Код


SELECT t1.f4, t2.f5 FROM table1 t1
INNER JOIN table2 t2
ON t1.f1 = t2.f1

Как будет строиться план для второго запроса?

Автор: Akina 5.12.2014, 10:43

Цитата(lv151 @ 5.12.2014, 09:53

)

Как будет строиться план для второго запроса?

Независимо от предыдущего запроса.

Автор: lv151 5.12.2014, 12:24

Понял.

Автор: Akina 5.12.2014, 12:26

Нет. Это абсолютно.

Цитата(lv151 @ 5.12.2014, 09:53

)

Есть процедура, которая в зависимости от выбранных полей на клиенте, строит SELECT запрос, который представляет собой динамический SQL:

Вообще это неправильный подход.
Процедура должна принимать один параметр, в ней должны быть прописаны все варианты запросов, и выбираться должен тот, который нужен. Типа:

Код


CREATE PROCEDURE MyProc (IN param INT)
RETURNS TABLE
AS
IF param=1 THEN
  RETURN (SELECT f1 FROM table1)
ELSEIF param=2 THEN
  RETURN (SELECT f1, f2 FROM table1)
ELSEIF param=3 THEN
  RETURN (SELECT t1.f4, t2.f5 FROM table1 t1)
-- ...
ELSE
  RETURN (SELECT NULL dummy FROM dual)
END IF

Автор: lv151 5.12.2014, 12:34

Добавлено @ 12:43

Цитата(Akina @ 5.12.2014, 12:26

)

Вообще это неправильный подход.Процедура должна принимать один параметр, в ней должны быть прописаны все варианты запросов, и выбираться должен тот, который нужен. Типа:

Согласен, как раз хочу сделать что-то подобное.

Цитата(lv151 @ 5.12.2014, 12:34

)

Нет. Это абсолютно.

Почему?Как я понимаю, для обычной ХП, после первого выполнения создаётся план.При последующих выполнениях, план берётся из кэша.
?

Автор: Akina 5.12.2014, 12:59

Цитата(lv151 @ 5.12.2014, 13:34

)

для обычной ХП, после первого выполнения создаётся план.При последующих выполнениях, план берётся из кэша.

Ага... при условии, что текст процедуры не изменился ни на байт - вернее, если не было ALTER/DROP PROCEDURE.
Если же текст процедуры динамически изменялся - план строится заново. Даже тогда, когда новый текст точно равен старому.
Иначе с запросами - там, насколько я знаю, сравнивается именно текст.
Что же до динамических запросов - то ЕМНИП они не кэшируются.

Автор: Дрон 7.12.2014, 23:54

Насколько я помню план для динамических запросов всё-таки кэшируется по их тексту. Причём от хранимой процедуры, в которой собирается этот запрос, он не зависит.

Кстати, если собирать запрос вот так

Код


set @sql = 'select * from T where P = ' + @arg
exec (@sql)

То для каждого значения @arg будет отдельный план.
А если вот так:

Код

set @sql = 'select * from T where P = @A'
exec sp_executesql @sql, N'@A int', @arg

То план будет один для любого параметра (т.к. текст запроса не меняется).
В этом есть и плюсы, и минусы.

Этот ответ добавлен с нового Винграда - http://ru.vingrad.com/SQL-Server-2008r2-protsedury-s-dinamicheskim-SQL-id54814880ae201598148b4567#findElement_E7045_5484be85ae20153d7233d32c_0

Автор: lv151 8.12.2014, 08:42

Где можно почитать про все ньюансы динамическго SQL?