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

Откуда:
Сообщений: 2321
Доброго дня.

Есть большой регистр с данными - InventTrans
Есть сложная бизнес логика которая может дать условие на регистр, а если не накладывает - то показываемый все данные.
Логика.
declare @d1 smalldatetime , @d2 smalldatetime, @company_id varchar(5), @n int
select @d1  = '2012-05-01', @d2  = '2012-05-31', @company_id  = 'ret'
...
Create table #Tmp_Filter ( regID int)
Insert into #Tmp_Filter  (regID ) 
Select regID  From dbo.fn_logic(@ItemID, @PartnerID) 

SELECT  @n = @@RowCount
IF @n  = 0 
Insert into #Tmp_Filter  (regID ) values (1)

...

Select
..... 
From 
InventTrans  t inner join #Tmp_Filter k on t.reg_id = case when @n=0 then t.reg_id else k.regID end
Where
t.DocDate between @d1 and @d2 and t.company_ID = @company_id


Подобная конструкция case when - очень удачно помогает решению, но только для
Where. Конструкции типа
Where
  t.ID = case when @n=0 then t.ID else @N end


Проблема в том, что если во временную таблицу приходят записи для фильтра, то в плане запроса все равно идет скан всей таблицы InventTrans ( переменные даты и компания дают фильтр, а вот бизнес логика - нет.) Скорость запроса так себе - до 20-30 сек.

Если запрос переписать на
declare @d1 smalldatetime , @d2 smalldatetime, @company_id varchar(5), @n int
select @d1  = '2012-05-01', @d2  = '2012-05-31', @company_id  = 'ret'
...
Create table #Tmp_Filter ( regID int)
Insert into #Tmp_Filter  (regID ) 
Select regID  From dbo.fn_logic(@ItemID, @PartnerID) 

SELECT  @n = @@RowCount
IF @n  = 0 
Insert into #Tmp_Filter  (regID ) values (1)

...

Select
..... 
From 
InventTrans  t inner join 
( SELECT 
         regid As reg_id
   FROM 
      #Tmp_Filter 
   UNION 
   Select
             th.reg_ID
    From 
         InventTrans  th Where @n = 0 and th.DocDate between @d1 and @d2 and th.company_ID = @company_id
) k on t.reg_id = k.reg_ID 
Where
t.DocDate between @d1 and @d2 and t.company_ID = @company_id


То запрос отлетает менее чем за 1 сек.

Но что не нравиться.... Двойное обращение к таблице InventTrans , а она тяжелая... если бизнес логика не дает фильтр.
Динамический sql не годится. Сложно сопровождать.

Возможное есть еще какие то решения.
Очень хочется использовать конструкцию типа JOIN CASE WHEN

Спасибо.
22 апр 15, 16:41    [17549922]     Ответить | Цитировать Сообщить модератору
 Re: JOIN CASE WHEN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20603
JOIN CASE WHEN требует расчёта выражения перед связыванием - неудивительно, что работает долго. В то время как UNION может без проблем работать с индексами...
Но если приглядеться - то не очень понятно, зачем UNION упрятан в подзапрос. Может, избавиться от подзапроса и реализовать весь запрос в форме внешнего UNION?
А если всё это происходит в процедуре - так и вовсе реализовать две независимые ветки (IF), каждая со своим запросом в зависимости от наличия или отсутствия фильтра.
22 апр 15, 16:56    [17550036]     Ответить | Цитировать Сообщить модератору
 Re: JOIN CASE WHEN  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Если смысл inner join только в фильтре, то перенесите выражение фильтра в WERE exists ().

И да, ВСЁ сложите во временную таблицу, не берите из двух источников.
22 апр 15, 18:28    [17550589]     Ответить | Цитировать Сообщить модератору
 Re: JOIN CASE WHEN  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Volochkova,

1.
Where
  t.ID = case when @n=0 then t.ID else @N end


Отрабатывает быстро.

А вот это:
2.
join #Tmp_Filter k on t.reg_id = case when @n=0 then t.reg_id else k.regID end


медленно?

Ну так ведь у вас в where написано else @n, а в условии соединения else regID.
В первом случае, он может использовать Filter with startup predicate, во втором нет. В третьем случае,
3.
+
declare @d1 smalldatetime , @d2 smalldatetime, @company_id varchar(5), @n int
select @d1  = '2012-05-01', @d2  = '2012-05-31', @company_id  = 'ret'
...
Create table #Tmp_Filter ( regID int)
Insert into #Tmp_Filter  (regID ) 
Select regID  From dbo.fn_logic(@ItemID, @PartnerID) 

SELECT  @n = @@RowCount
IF @n  = 0 
Insert into #Tmp_Filter  (regID ) values (1)

...

Select
..... 
From 
InventTrans  t inner join 
( SELECT 
         regid As reg_id
   FROM 
      #Tmp_Filter 
   UNION 
   Select
             th.reg_ID
    From 
         InventTrans  th Where @n = 0 and th.DocDate between @d1 and @d2 and th.company_ID = @company_id
) k on t.reg_id = k.reg_ID 
Where
t.DocDate between @d1 and @d2 and t.company_ID = @company_id


если @n=0, то в плане также должен быть Filter with startup predicate. Этот фильтр значит, что если не выполняется start up предикат, вся ветка что под фильтром не будет реально выполняться, если предикат false (по этому быстро), даже если она есть в плане. Ведь план должен строиться для любого значения переменной.

+ я предполагаю, что должно быть что-то вроде

Select
Nested Loops
Merge/Hash Union
Filter (with startup predicate @n=0)
Scan/Seek(InventTrans)
Scan (#Tmp_Filter)
Seek/Clustered Seek (InventTrans)


Можете попробовать так переписать ваш запрос (опять же если я правильно понял логику из вашего объяснения):
+
Select
	..... 
From 
	InventTrans  t
	left join #Tmp_Filter f on t.reg_id = f.reg_id
where
	(f.reg_id is not null or isnull(@n,1) = 0)
option(recompile)

Но я бы проще, разделил это на разные ветки кода.
В плане поддержки, реально проще добавить чего-то в простые запросы, чем разбираться со сложными условиями.
Лучше планы давайте.

А вообще, в таких случаях обычно либо читают статью: Dynamic Search Conditions in T‑SQL.
Можете еще так переписать свой третий запрос (если я правильно понял):


Но я бы просто разделил на две ветки кода.
22 апр 15, 20:14    [17550913]     Ответить | Цитировать Сообщить модератору
 Re: JOIN CASE WHEN  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

Перепуталось маленько, но, надеюсь, мысль ясна.
22 апр 15, 20:16    [17550922]     Ответить | Цитировать Сообщить модератору
 Re: JOIN CASE WHEN  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
option(recompile)

Помогло, на все 100%
24 апр 15, 09:49    [17557341]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить