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

Откуда: мск
Сообщений: 1887
приветствую
расскажите, если в нескольких столбцах результата значения зависят от одних и тех же условий, вы предпочтете написать так
SELECT 
  CASE 
    WHEN <condition1> THEN case1A
    WHEN <condition2> THEN case2A
    ...
  END AS colA,
  CASE 
    WHEN <condition1> THEN case1B
    WHEN <condition2> THEN case2B
    ...
  END AS colB
FROM tbl
WHERE <megafilter>;
или так
WITH cte AS (
  SELECT <all_columns> 
  FROM tbl
  WHERE <megafilter>
)
SELECT case1A AS colA, case1B AS colB FROM cte WHERE <condition1> 
UNION ALL
SELECT case2A,         case2B         FROM cte WHERE <condition2> 
UNION ALL
...;
или же вообще написать кучу отдельных IFов либо что-то другое и почему? условия <conditionN> зависят только от локальных переменных, истинно из них только одно. на своих примерах вижу, что хоть план и разный, но IO первого и второго запроса совпадает, время приблизительно равно.
13 окт 11, 16:19    [11435394]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shakill
написать кучу отдельных IFов
Надеюсь, вы прикалываетесь. Какой нафиг IF в параметризованных вьюхах (Inline Functions)
Ваш вариант CTE тоже выкиньте (если в условиях колонки), пока не нарывался чтоб скуль мог обобщать это извращение (интересно, удивит ли тут iljy).
И никогда так не делайте при больших по тексту запросах - это путает в первую очередь людей, уверяю вас, сталкивался в реале.

Один из неплохих вариантов:
SELECT	<need_columns>
FROM	<SourceSsss> ...
	CROSS APPLY (	SELECT case1A, case1B, case1C, ... WHERE <condition1>
	UNION ALL	SELECT case2A, case2B, case2C, ... WHERE <condition2>
	UNION ALL	SELECT case3A, case3B, case3C, ... WHERE <condition3>
	...
			) GC  (  colA,   colB,   colC, ...)
WHERE	<PuperFilter>
Это и понятно и просто и не так сильно пудрит мозги оптимизатору.
Недостаток, в том что CASE и UNION ALL несопостовимы. Условия <condition> должны быть максимально однозначные (т.е. не просто по семантике но и синтаксически - декларативно).

Возможно даже стоит иногда добавить Top(1) для убеждения оптимизатора, что он не влияет на наборы данных (мощность). Но в критических случаях лучше уж CASE.

Shakill
IO первого и второго запроса совпадает, время приблизительно равно.
Это можно объяснить только одним:
Shakill
<conditionN> зависят только от локальных переменных
Т.е. вы в плане видите Startup Predicate.

Для случая с переменными (без колонок) лучше проанализировать мой вариант и ваш. По идее в вашем варианте условие сработает один раз, а в моём для каждой строки. По времени тяжелее заметить, т.к. CPU ресурс обычно простаивающий, в отличие от IO (диски).
Случай с переменными (Startup Predicate) классический, но чаще не симметричный и разнородный, и конечно по набору во FROM. Т.е. дело даже не в колонках (и тем более не в CASE), да и CTE тут не причём, обычно же используют представления (не дай бог код повторяется в разных объектах базы).

Какгрится итсдепендс.
14 окт 11, 01:42    [11438472]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Mnior, спасибо за ответ. вопрос возник вообще из-за желания найти компактную конструкцию для нескольких колонок, позволяющую не писать одно и то же условие <conditionN> несколько раз, и чтоб быстро было. CROSS APPLY подошло. хоть план и более громоздкий, но IO получается равный с CASE и при условиях по переменным, и при условиях, зависимых от колонок, а вот приведенное CTE во втором случае из борьбы выбывает

в критичных по скорости случаях всё равно придётся сравнивать варианты, но это и так понятно
Mnior
Какой нафиг IF в параметризованных вьюхах (Inline Functions)
не всегда имеет смысл выделять такой участок кода в отдельную функцию, поэтому предполагаю, что вариант с IFами подойдет для редко используемых тяжелых процедур, которые допустимо перекомпилировать каждый раз. правда тут <conditionN> не может зависеть от столбцов в принципе
14 окт 11, 13:36    [11440758]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Shakill
не всегда имеет смысл выделять такой участок кода в отдельную функцию, поэтому предполагаю, что вариант с IFами подойдет для редко используемых тяжелых процедур, которые допустимо перекомпилировать каждый раз. правда тут <conditionN> не может зависеть от столбцов в принципе
А зачем каждый раз перекомпилировать? Разделение по разным веткам, с точки зрения производительности вообще один из самых хороших способов, если от переменных зависит, будет ли выгодно использовать поиск или просмотр (например, если в процедуру с клиента пришел ИД записи - то выбираем только ее, если нет - то все). Единственное поддерживать неудобно, если условий много - запаришься...вот и приходится выдумывать.
14 окт 11, 14:09    [11441087]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
SomewhereSomehow
Shakill
не всегда имеет смысл выделять такой участок кода в отдельную функцию, поэтому предполагаю, что вариант с IFами подойдет для редко используемых тяжелых процедур, которые допустимо перекомпилировать каждый раз. правда тут <conditionN> не может зависеть от столбцов в принципе
А зачем каждый раз перекомпилировать?
перекомпиляция даёт оптимальный план для конкретных значений параметров, исходил из этого
SomewhereSomehow
Разделение по разным веткам, с точки зрения производительности вообще один из самых хороших способов, если от переменных зависит, будет ли выгодно использовать поиск или просмотр
то есть в каждом отдельном случае придётся сравнивать удобство поддержки и быстродействие обоих подходов -
либо разделять по веткам участок внутри процедуры при помощи IF, либо выносить его в отдельные вьюхи/функции с реализацией ветвлений через CASE или WHERE ... UNION ALL
14 окт 11, 14:34    [11441325]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Shakill
перекомпиляция даёт оптимальный план для конкретных значений параметров, исходил из этого
Это понятно. Я говорил про тот случай когда в зависимости от значения идут разделения на разные ветки. Имелось ввиду типа такого
-- вот тут и без перекомпиляций все отработает ок, с "правильным" планом
create proc _test1
	@ID int
as
if @ID is not null begin
	select * from dbo.MyTable where @ID = ID
end else begin
	select * from dbo.MyTable
end

-- вот тут "правильность" плана будет зависеть от параметра и меняться в зависимости от перекомпиляций
create proc _test2
	@ID int
as
select * from dbo.MyTable where (@ID is null or @ID = ID)
14 окт 11, 14:49    [11441496]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
SomewhereSomehow, понял, recompile зря приплёл
14 окт 11, 14:54    [11441570]     Ответить | Цитировать Сообщить модератору
 Re: несколько результатов с одними условиями  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
CREATE FUNCTION [dbo].[fnTest1] (
	 @ID	Int
) RETURNS TABLE AS RETURN
	SELECT * FROM dbo.MyTable	WHERE @ID = ID
UNION ALL
	SELECT * FROM dbo.MyTable	WHERE @ID IS NULL
Никакой пере-компиляции, переключение сработает в самом плане.
Зачем IF и процедуры в таких случаях.
14 окт 11, 17:50    [11443206]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить