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

Откуда:
Сообщений: 2694
Скажите пожалуйста,
как можно преобразовать такой динамический запрос в НЕ динамический?

ALTER PROCEDURE [dbo].[sp_filter]
(
@rol VARCHAR(500),
@MyZayavka BIT=0,
@MyProblem BIT=0,
@AllZayavka BIT=0,
@comboStatus INT=0,
@comboType INT=0,
@t_num_bk VARCHAR(50)='',
@c_dog_num INT=0,
@d_datRegStart DATETIME = NULL,
@d_datRegEnd DATETIME = NULL,
@t_name_set VARCHAR(250)='',

@c_user_bk INT=0,
@t_namedoc VARCHAR(50)='',
@t_namecod VARCHAR(50)='',
@t_invnum VARCHAR(50)='',
@t_out_num_letter VARCHAR(50)='',
@t_objectname VARCHAR(50)='',
@c_user_zakaz INT=0,
@c_depart_zakaz INT=0
)
AS
BEGIN
SET NOCOUNT ON;


DECLARE @str nvarchar(max), @sql nvarchar(max)
DECLARE @user_full nvarchar(500)

SET @str=''
SET @sql=''

SELECT TOP 1 @user_full=FullName FROM dbo.spr_users WHERE UserName=suser_sname()

set @str=
case when @MyZayavka=1 then ' inner join (select id from q_main_zayavka where user_zakaz_str='''+@user_full+''') MyZayavka on mz.id = MyZayavka.id ' else '' end
+
case when @MyProblem=1 then ' inner join (select id from q_actual_ispolnitel) MyProblem on mz.id = MyProblem.id ' else '' end
+
case when @AllZayavka=1 then ' inner join (select id from q_main_zayavka) AllZayavka on mz.id = AllZayavka.id ' else '' end
+
case when @comboStatus<>-1 then ' inner join (select id from q_main_zayavka where id_status='+ltrim(str(@comboStatus))+') comboStatus on mz.id = comboStatus.id ' else '' END
+
case when @comboType<>0 then ' inner join (select id from q_main_zayavka where id_zayavka_type='+ltrim(str(@comboType))+') comboType on mz.id = comboType.id ' else '' END
+
case when @t_num_bk<>'' then ' inner join (select id from q_main_zayavka where num_bk='''+@t_num_bk+''') t_num_bk on mz.id = t_num_bk.id ' else '' END
+
case when @c_dog_num<>0 then ' inner join (select id from q_main_zayavka where id_dog='+ltrim(str(@c_dog_num))+') c_dog_num on mz.id = c_dog_num.id ' else '' END
+
case when @d_datRegStart IS NOT NULL AND @d_datRegEnd IS NOT NULL then ' inner join (select id from q_main_zayavka WHERE date_first_reg BETWEEN '''+CONVERT(VARCHAR(10),@d_datRegStart,104)+''' AND '''+CONVERT(VARCHAR(10),@d_datRegEnd,104)+''') t_num_bk on mz.id = t_num_bk.id ' else '' end
+
case when @t_name_set<>'' then ' inner join (select id from q_main_zayavka where name_set=''%'+@t_name_set+'%'') t_name_set on mz.id = t_name_set.id ' else '' END

----Дополнительные фильтры
+
case when @c_user_bk<>0 then ' inner join (select id from q_main_zayavka where user_bk='+ltrim(str(@c_user_bk))+') c_user_bk on mz.id = c_user_bk.id ' else '' END
+
case when @t_namedoc<>'' then ' inner join (select id_zayavka from q_main_document where name like ''%'+@t_namedoc+'%'') t_namedoc on mz.id = t_namedoc.id_zayavka ' else '' END
+
case when @t_namecod<>'' then ' inner join (select id_zayavka from q_main_document where code like ''%'+@t_namecod+'%'') t_namecod on mz.id = t_namecod.id_zayavka ' else '' END
+
case when @t_invnum<>'' then ' inner join (select id_zayavka from q_main_document where num_inv like ''%'+@t_invnum+'%'') t_invnum on mz.id = t_invnum.id_zayavka ' else '' END
+
case when @t_out_num_letter<>'' then ' inner join (select id from q_main_zayavka where out_num_letter like ''%'+@t_out_num_letter+'%'') t_out_num_letter on mz.id = t_out_num_letter.id ' else '' END
+
case when @t_objectname<>'' then ' inner join (select id from q_main_zayavka where objectname like ''%'+@t_objectname+'%'') t_objectname on mz.id = t_objectname.id ' else '' END
+
case when @c_user_zakaz<>0 then ' inner join (select id from q_main_zayavka where user_zakaz='+ltrim(str(@c_user_zakaz))+') c_user_zakaz on mz.id = c_user_zakaz.id ' else '' END
+
case when @c_depart_zakaz<>0 then ' inner join (select id from q_main_zayavka where depart_zakaz='+ltrim(str(@c_depart_zakaz))+') c_depart_zakaz on mz.id = c_depart_zakaz.id ' else '' END

set @sql = @sql + 'SELECT mz.id FROM q_main_zayavka mz' + @str




exec (@sql)

END




Заранее благодарен.

Сообщение было отредактировано: 22 янв 14, 15:04
22 янв 14, 14:19    [15452186]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Сваливать все, что возвращают подзапросы, во времянки, потом джоинить с основной таблицей. Но не факт, что выйдет быстрее, ибо если условие будет не селективное, во времянку польется куча всего.
22 янв 14, 15:07    [15452485]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
vah
Member

Откуда:
Сообщений: 2694
Гавриленко Сергей Алексеевич
Сваливать все, что возвращают подзапросы, во времянки, потом джоинить с основной таблицей. Но не факт, что выйдет быстрее, ибо если условие будет не селективное, во времянку польется куча всего.


а в чем преимущество временных таблиц перед подзапросами?
22 янв 14, 15:16    [15452531]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
vah,

Как вариант можно так попробовать...
ALTER PROCEDURE [dbo].[sp_filter]
(
@rol VARCHAR(500),
@MyZayavka BIT=0,
@MyProblem BIT=0,
@AllZayavka BIT=0,
@comboStatus INT=0,
@comboType INT=0,
@t_num_bk VARCHAR(50)='',
@c_dog_num INT=0,
@d_datRegStart DATETIME = NULL,
@d_datRegEnd DATETIME = NULL,
@t_name_set VARCHAR(250)='',

@c_user_bk INT=0,
@t_namedoc VARCHAR(50)='',
@t_namecod VARCHAR(50)='',
@t_invnum VARCHAR(50)='',
@t_out_num_letter VARCHAR(50)='',
@t_objectname VARCHAR(50)='',
@c_user_zakaz INT=0,
@c_depart_zakaz INT=0
)
AS
    SELECT id FROM q_main_zayavka
    where 1=1
    and (@MyZayavka=1 and user_zakaz_str=@user_full or isnull(@MyZayavka<>1))
    and (@comboStatus<>-1 and id_status=ltrim(str(@comboStatus)) or @comboStatus=-1)
    ... и т.д.


Не стал дальше вникать, особенно смутило условие @AllZayavka=1 с продолжением... абсолютно не влияющий на результат выборки какое бы значение этой переменной бы не было, при условие что id в таблице q_main_zayavka уникально.
22 янв 14, 16:57    [15453143]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Алексей Куренков,

немного исправил ошибки синтаксиса, что бы не запутать... но здесь суть не в конечном запросе а в идее.
    SELECT id FROM q_main_zayavka
    where 1=1
    and (@MyZayavka=1 and user_zakaz_str=@user_full or isnull(@MyZayavka,0)<>1))
    and (@comboStatus<>-1 and id_status=ltrim(str(@comboStatus)) or isnull(@comboStatus,0)=-1)
    ... и т.д.
22 янв 14, 16:59    [15453159]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
SERG1257
Member

Откуда:
Сообщений: 2873
Я бы пошел другим путем - все эти "гибкие формы поиска" по факту разбиваются на два, три типичных особо популярных случаев плюс редкое остальное для галочки шоб было.
То бишь в процедуре анализировал параметры и прописывал оптимизированные запросы для типичных случаев, а остальное валил как есть см предыдущий ответ.
22 янв 14, 17:33    [15453355]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
vah,

я думаю, что такой запрос как раз не нужно преобразовывать в статический.
22 янв 14, 21:32    [15454156]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
MasterZiv опять не угадал.

Тут классический ReCompile исправляет проблему. Если конечно условий не 100500, что просто долго будет компилиться.

* ltrim(str( - жесть
* name_set=''%'+@t_name_set+'%'' - очепятка

Писать JOIN вместо Exists? Это типа пересечение индексов?
Я не знаю что тут сказать. Может стоит отдать на сьедение статистики, пусть лучше выберет лучший базовый индекс. Или сам решает что там перемножать или нет.

Я пока так оставлю, но надо планы смотреть, с ходу не скажу.
ALTER PROCEDURE [dbo].[sp_filter]
	@rol			VarChar(500)	-- Not Used
,	@MyZayavka		Bit		= 0
,	@MyProblem		Bit		= 0
,	@AllZayavka		Bit		= 0
,	@comboStatus		Int		= 0	-- -1 Особый параметр?
,	@comboType		Int		= 0
,	@t_num_bk		VarChar(50)	= ''
,	@c_dog_num		Int		= 0
,	@d_datRegStart		DateTime	= NULL
,	@d_datRegEnd		DateTime	= NULL
,	@t_name_set		VarChar(250)	= ''
-- Дополнительные фильтры
,	@c_user_bk		Int		= 0
,	@t_namedoc		VarChar(50)	= ''
,	@t_namecod		VarChar(50)	= ''
,	@t_invnum		VarChar(50)	= ''
,	@t_out_num_letter	VarChar(50)	= ''
,	@t_objectname		VarChar(50)	= ''
,	@c_user_zakaz		Int		= 0
,	@c_depart_zakaz		Int		= 0
AS BEGIN
	SET NOCOUNT ON;

	SELECT MZ.ID FROM dbo.q_main_zayavka MZ
	WHERE(	IsNull(@MyZayavka	,0 ) = 0	OR Exists(SELECT * FROM dbo.spr_users		U JOIN
										dbo.q_main_zayavka	E				 ON E.user_zakaz_str	 = U.FullName
													  WHERE MZ.ID = E.ID		AND U.UserName		 = SUser_SName()			))
	AND (	IsNull(@MyProblem	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_actual_ispolnitel	E WHERE MZ.ID = E.ID										))
	AND (	IsNull(@AllZayavka	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID										))
	AND (	IsNull(@comboStatus	,-1) = -1	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.id_status		 = @comboStatus				))
	AND (	IsNull(@comboType	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.id_zayavka_type	 = @comboType				))
	AND (	IsNull(@t_num_bk	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.num_bk		 = @t_num_bk				))
	AND (	IsNull(@c_dog_num	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.id_dog		 = @c_dog_num				))
	AND (	@d_datRegStart		IS NULL
	OR	@d_datRegEnd		IS NULL		OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.date_first_reg	>= @d_datRegStart
																	AND E.date_first_reg	<  DateAdd(Day,1,@d_datRegEnd)		))
	AND (	IsNull(@t_name_set	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.name_set		LIKE '%' + @t_name_set		+ '%'	))
-- Дополнительные фильтры
	AND (	IsNull(@c_user_bk	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.user_bk		 = @c_user_bk				))
	AND (	IsNull(@t_namedoc	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_document	E WHERE MZ.ID = E.ID_zayavka	AND E.name		LIKE '%' + @t_namedoc		+ '%'	))
	AND (	IsNull(@t_namecod	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_document	E WHERE MZ.ID = E.ID_zayavka	AND E.code		LIKE '%' + @t_namecod		+ '%'	))
	AND (	IsNull(@t_invnum	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_document	E WHERE MZ.ID = E.ID_zayavka	AND E.num_inv		LIKE '%' + @t_invnum		+ '%'	))
	AND (	IsNull(@t_out_num_letter,'') = ''	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.out_num_letter	LIKE '%' + @t_out_num_letter	+ '%'	))
	AND (	IsNull(@t_objectname	,'') = ''	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.objectname	LIKE '%' + @t_objectname	+ '%'	))
	AND (	IsNull(@c_user_zakaz	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.user_zakaz	 = @c_user_zakaz			))
	AND (	IsNull(@c_depart_zakaz	,0 ) = 0	OR Exists(SELECT * FROM dbo.q_main_zayavka	E WHERE MZ.ID = E.ID		AND E.depart_zakaz	 = @c_depart_zakaz			))
	OPTION(ReCompile)
END
GO
Подход у процедуры ТС изначально говно. Не решена бизнес задача.
Нужно разбить всё по процессам, и соответственно будет фиксированное число вариантов, и базовый набор параметров для них.
И в интерфейсе тоже покопаться надо. Я се представляю головную боль операторов видеть весь набор и сопоставлять "что надо и какие параметры для этого нужно выбрать".
Таким разрабам яйца отрывать надо за издевательство, принудительное BDSM какое-то.
23 янв 14, 03:58    [15454802]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
vah
Member

Откуда:
Сообщений: 2694
Спасибо!
23 янв 14, 09:39    [15455099]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34703
Mnior
MasterZiv опять не угадал.

Тут классический ReCompile исправляет проблему. Если конечно условий не 100500, что просто долго будет компилиться.


А тебе понравится, что каждый раз будет запрос перекомпилироваться?
23 янв 14, 18:38    [15458915]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
MasterZiv
Mnior
MasterZiv опять не угадал.

Тут классический ReCompile исправляет проблему. Если конечно условий не 100500, что просто долго будет компилиться.


А тебе понравится, что каждый раз будет запрос перекомпилироваться?
Согласен с Mnior, только надо учитывать, что в строках типа:
AND ( IsNull(@AllZayavka ,0 ) = 0 OR Exists(SELECT * FROM dbo.q_main_zayavka E WHERE MZ.ID = E.ID ))

нужно убрать Exists, потому что там это пишется просто в виде условия на основную таблицу.

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

Ну и дополнительно можно пойти по пути (который уже выше упомянул SERG1257) - выделить пару-тройку вариантов сочетаний параметров, которые наиболее часто встречаются, обычно так можно покрыть большинство вызовов. А остальное пусть перекомпилируется.
23 янв 14, 23:38    [15459939]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
MasterZiv, а при динамике не будет компилится часто?
Вы только в том случае правы, если бы вы подразумевали переделать на параметризованный динамический запрос.
Который не через EXEC, а через sp_executesql
А тут параметры прямо в тексте (привет SQL Injection), и запрос сложный, следовательно параметризация на нём может и не сработать.

Для тех кто не понял: Если генерировать каждый раз параметризованный запрос, то скуль будет находить в кеше готовый план, в случае если такая комбинация уже запускалась, и не была вымыта из кэша.
Если не делать параметры, то сложные запросы по умолчанию не параметризируются, и опасность выполнить произвольный код (SQL Injection).

Но если я ничего не путаю, то можно и без динамики запустить выше указанный блок целиком через sp_executesql (без ReCompile) и тогда всё равно будут несколько разных планов, в зависимости от параметров, и будет подбираться подходящий.
Хотя оптимальность не будет такой хорошей как в случае простого ReCompile.

Скажу так. Я могу иногда ошибаться по построении костылей и говнокода, не спорю.
Если плясать по приоритету, где переделать бизнес процесс важнее (чем удачно писать под неправильный бизнес процесс), то не будет никакого ReCompile и динамики изначально (или будет сильно ограниченна).
24 янв 14, 00:07    [15459992]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
alexeyvg
AND ( IsNull(@AllZayavka ,0 ) = 0 OR Exists(SELECT * FROM dbo.q_main_zayavka E WHERE MZ.ID = E.ID ))
Гы-гы

Прошляпил. Сам по себе параметр глупость полная.
Ну не привык когда подвох на каждом углу, пачками. Тут нужно несколько подходов делать к снаряду.
24 янв 14, 00:17    [15460007]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от динамики  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
Mnior
Ну не привык когда подвох на каждом углу, пачками.
:-)
Mnior
MasterZiv, а при динамике не будет компилится часто?
Сиквел запомнит разные варианты, а параметры-константы он уже умеет выковыривать.
Хотя лучше конечно переделать на параметризованный динамический запрос через sp_executesql
24 янв 14, 00:28    [15460038]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить