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

Откуда:
Сообщений: 371
CREATE PROC [MetalMotion].[GetAllOrderRecords]
@OrderRecordId as int = NULL,
@SteelId as int = NULL,
@NumberOrderRecord as varchar(50) = NULL,
@SteelId as int = NULL,
@ClientId as int = NULL


AS
BEGIN

SELECT * FROM Clients 
WHERE -- если параметр не NULL то включить его сюда


END
3 июн 14, 11:11    [16111072]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37052
https://www.sql.ru/faq/faq_topic.aspx?fid=114

Или:
SELECT * FROM Clients a
WHERE -- если параметр не NULL то включить его сюда
  (@OrderRecordId is null or a.OrderRecordId = @OrderRecordId  )
  and (@SteelId is null or a.SteelId = @SteelId)
  and ...


Или динамика.

Сообщение было отредактировано: 3 июн 14, 11:13
3 июн 14, 11:13    [16111090]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
sanekoffice
Member

Откуда:
Сообщений: 371
Гавриленко Сергей Алексеевич
https://www.sql.ru/faq/faq_topic.aspx?fid=114

Или:
SELECT * FROM Clients a
WHERE -- если параметр не NULL то включить его сюда
  (@OrderRecordId is null or a.OrderRecordId = @OrderRecordId  )
  and (@SteelId is null or a.SteelId = @SteelId)
  and ...


Или динамика.


Спасибо! То что нужно
3 июн 14, 11:18    [16111120]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
    (@OrderRecordId IS NULL OR OrderRecordId=@OrderRecordId)
AND (@SteelId IS NULL OR SteelId=@SteelId)
AND (@NumberOrderRecord IS NULL OR NumberOrderRecord=@NumberOrderRecord)
AND (@ClientId IS NULL OR ClientId=@ClientId)
или
    OrderRecordId BETWEEN ISNULL(@OrderRecordId,-2147483648) AND ISNULL(@OrderRecordId,2147483647)
AND SteelId BETWEEN ISNULL(@SteelId,-2147483648) AND ISNULL(@SteelId,2147483647)
AND NumberOrderRecord LIKE ISNULL(@NumberOrderRecord,'%')
AND ClientId BETWEEN ISNULL(@ClientId,-2147483648) AND ISNULL(@ClientId,2147483647)
3 июн 14, 11:22    [16111147]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
SteelId = isnull(@SteelId, SteelId)

при условии, что SteelId is not null
3 июн 14, 13:23    [16112289]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iap
    (@OrderRecordId IS NULL OR OrderRecordId=@OrderRecordId)
AND (@SteelId IS NULL OR SteelId=@SteelId)
AND (@NumberOrderRecord IS NULL OR NumberOrderRecord=@NumberOrderRecord)
AND (@ClientId IS NULL OR ClientId=@ClientId)


Да я обычно такой подход использую
3 июн 14, 14:54    [16113115]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Если добавить OPTION (Recompile) то все OR схлопнутся.
3 июн 14, 20:34    [16115431]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
kalimba
Member

Откуда:
Сообщений: 297
Mnior,

Сорри, чего-то не очень понял, что имелось в виду под "схлопнуться"?
4 июн 14, 00:39    [16116177]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
kiwo
Member

Откуда:
Сообщений: 10
В случае больших таблиц важно помнить, что подобные универсальные WHERE приводят к неоптимальным планам выполнения.
Разбиение запроса на несколько или использование динамического сиквела зачастую может дать огромный выигрыш.
Гуглить "parameter sniffing"
4 июн 14, 00:47    [16116193]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
kiwo
Member

Откуда:
Сообщений: 10
Mnior
Если добавить OPTION (Recompile) то все OR схлопнутся.

Схлопнутся (т.е. оптимизируются под текущие критерии). Но получите большой оверхед в рекомпиляции, если запрос выполняется достаточно часто.
4 июн 14, 00:51    [16116196]     Ответить | Цитировать Сообщить модератору
 Re: Where в зависимости от числа пришедших параметров  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
kiwo, конечно.

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

Не должно в принципе быть такого, что одновременно запрос сложный (сильно параметризованный) и часто запускаемый.
Часто запускаемые вещи должны быть чётко и жёстко определены.

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

Иногда выносят частые варианты запуска в отдельные запросы или отдельные UNION ALL-ы запроса (без Recompile).

Я согласен что в случае ТС нужно именно UNION ALL или отдельные запросы. Что-то типа, как вариант
	SELECT	* -- Поиск по ID
	FROM	dbo.Clients	C
	WHERE	@ClientID		IS NOT NULL
	AND	C.ClientID		= @ClientID
UNION ALL
	SELECT	* -- Поиск по SteelID + OrderRecordID
	FROM	dbo.Clients	C
	WHERE	@ClientID		IS     NULL
	AND	@OrderRecordID		IS NOT NULL
	AND	@NumberOrderRecord	IS     NULL
	AND	C.SteelID		= @SteelID
	AND	C.OrderRecordID		= @OrderRecordID
UNION ALL
	SELECT	* -- Поиск по SteelID + NumberOrderRecord
	FROM	dbo.Clients	C
	WHERE	@ClientID		IS     NULL
	AND	@OrderRecordID		IS     NULL
	AND	@NumberOrderRecord	IS NOT NULL
	AND	C.NumberOrderRecord	= @NumberOrderRecord
Только надо подстроится под необходимые варианты.
Можно в последнем случае собрать самый редкие и оставшиеся вариант.
Если отдельные запросы, то можно ещё использовать и Recompile для особых случаев.

Для тех кто не в курсе. Простые условия для переменных (хотя и в ругих случаях может быть тоже) будут преобразовыватся в специальный тип фильтра - Startup filter. Который отключает всю ветку плана.
4 июн 14, 17:36    [16121227]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить