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

Откуда:
Сообщений: 2321
Дано.
3 справочника
Sp_1 ID Name
Sp_2 ID Name
Sp_3 ID Name

Регистр движений
DocData
SP_1_ID
SP_2_ID
SP_3_ID
KolProd
TotalSum

Пользователь выбирает из справочников Sp_1, Sp_2, Sp_3 те позиции по которым надо сделать фильтр. Если ничего не выбрано то фильтр не накладывается и период. Получает за каждый день продажи.

Реализация...
IF Len(@Params1) > 0
Select * into #sp_1 from Sp_1 Where ID in (@Params1)
ELSE
Select * into #sp_1 from Sp_1 

IF Len(@Params2) > 0
Select * into #sp_2 from Sp_2 Where ID in (@Params2)
ELSE
Select * into #sp_2 from Sp_2 

IF Len(@Params3) > 0
Select * into #sp_3 from Sp_3 Where ID in (@Params3)
ELSE
Select * into #sp_3 from Sp_3 

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData

Все бы ничего, но в справочниках может быть по 100 000 элементов :(
И
 INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID
? если во временную таблицу ( параметр не выбран) попадает JOIN со 100 000 записями... работает гораздо медленнее? чем если бы этого Join вообще не было. Можно ли замутить такую вещь? что если какой то параметр или переменная, то таблица в JOIN не участвует?

MS SQL 2005
IF Len(@Params3) > 0
Select * into #sp_3 from Sp_3 Where ID in (@Params3)
ELSE
Select * into #sp_3 from Sp_3 

Реализовано не так как написано.
Синтаксис весь вытаскивать думаю не надо.
Вариант с динамическим SQL знаю.
4 дек 09, 11:26    [8018639]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Паганель
Member

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

И еще мне непонятно что такое @Params1, @Params2 и @Params3...
4 дек 09, 11:32    [8018675]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Denis@nk
Member

Откуда:
Сообщений: 193
а если в where добавить условие
where s1.ID = case when @p=0 then s1.ID else @p end
это не решит Вашу проблему?
4 дек 09, 11:33    [8018680]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
@Params Это через разделитель элементы, которые надо выбрать.
Параметрический запрос
where s1.ID = case when @p=0 then s1.ID else @p end

не канает, т.к. сравнение не с 1 переменной, а с набором значений в виде временных таблиц.
т.к. в @p может быть до 20000 строк.

Справочники иерархические
В условии не сказано, но во временную таблицу может попасть например строк 10 000.
4 дек 09, 11:44    [8018768]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Volochkova
@Params Это через разделитель элементы, которые надо выбрать.
Параметрический запрос
where s1.ID = case when @p=0 then s1.ID else @p end

не канает, т.к. сравнение не с 1 переменной, а с набором значений в виде временных таблиц.
т.к. в @p может быть до 20000 строк.

Справочники иерархические
В условии не сказано, но во временную таблицу может попасть например строк 10 000.
Мама!

Volochkova, а по-человечески с самого начала можете изложить?
4 дек 09, 11:50    [8018814]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Блин... Ну зачем этот пасьянс??
А хотя...
Имеем справочник с товаром..
ID
ParentID
Name
IfFolder

Пользователь выбирает 2 группы, это 10 000 товарных позиций.... например.
Значит надо сделать выборку из регистра и сделать JOIN с 10 000 строк справочника товаров, которые покладены :-) во временную таблицу. ( механизм сканирования иерархического справочника с определением что попадает в выделенные папки, точно не нужен тут)
Если же ни одна группа не выбрана, то выбираем из регистра все записи. :-(
Реализовано так..
Если не выбрана ни одна группа, то во временную таблицу сливаем коды всего справочника товаров.
Все работает.. Но....
Если исключить JOIN когда не выбрана ни одна группа, то запрос отработает быстрее, чем если делать JOIN с "тяжелой" временной таблицей, даже если в ней ( временной таблице) сделать кластерный индекс по полю с кодом товара.
Вот и ищу решение..
Если не надо накладывать условие, то как не делать JOIN
а если условие есть, ( большое кол-во элементов) то тогда уже делать jOIN
4 дек 09, 12:04    [8018946]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
aleks2
Guest
Volochkova,

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID OR not exists(select * FROM #sp_1)
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData
4 дек 09, 12:21    [8019087]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
iap
Member

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

Во-первых, "Если не надо накладывать условие" - это тоже условие.
Почему бы не сделать так
SELECT ... FROM ... JOIN ... JOIN ... WHERE .... AND <Наложенное условие>
UNION ALL
SELECT ... FROM ... JOIN ... JOIN ... WHERE .... AND <Нет никакого условия!>
Что же касается "выключения" JOIN по условию равенства некоторой переменной
некоторому значению, то, во-первых, проверку этой переменной можно включить в ON
соответствующего JOINа в надежде, что сервер не дурак, или, во-вторых, динамически
сформировать строку запроса, которая в этом случае не будет содержать ничего лишнего изначально.
4 дек 09, 12:29    [8019155]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
aleks2
Volochkova,

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     [i][b]INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID[/b][/i] OR not exists(select * FROM #sp_1)
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData


Не вариант ибо те же тормоза.



iap
Volochkova,

Во-первых, "Если не надо накладывать условие" - это тоже условие.
Почему бы не сделать так
SELECT ... FROM ... JOIN ... JOIN ... WHERE .... AND <Наложенное условие>
UNION ALL
SELECT ... FROM ... JOIN ... JOIN ... WHERE .... AND <Нет никакого условия!>
Что же касается "выключения" JOIN по условию равенства некоторой переменной
некоторому значению, то, во-первых, проверку этой переменной можно включить в ON
соответствующего JOINа в надежде, что сервер не дурак, или, во-вторых, динамически
сформировать строку запроса, которая в этом случае не будет содержать ничего лишнего изначально.


UNION ALL не вариант ибо select из одной таблицы..
Во вторых по переменной пробовали... получается засада

@flag int

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
[i][b]     INNER JOIN  #sp_1  s1 on r.SP_1_ID =  case when @flag  =1 then s1.ID  else r.SP_1_ID end[/b][/i]
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData
[/quot]

Оптимизатор условие не накладывает... Но если в #sp_1 весть каталог, то идет логическое перемножение таблиц... и... Sum(KolProd) выдает мультиплицируеще сумму по кол-ву записей.

А вот если #sp_1 сделать пустой.... то....
надо немного поменять логику отчета...
Спасибо.
7 дек 09, 01:35    [8026153]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
старенький эх
Guest
ранний отказ:
про SQL server я ничего не знаю, потому никакой "готовый запрос" даже начинать предлагать не буду.

Мадам, включите здравый смысл:
вы рисуете join по наборам, размер котрым меняется от малых до больших, не предоставив возможности серверу, который за вас трудится, использовать индексы.
Если на малых наборах безындексный джойн - еще туда - сюда, то на больших - ваши упреки партнеру в нехватке сил - чрезмерны.

Кроме того, никаких джойнов в показанных вами запросах рисовать не требуется.
Требуется составить выражения вида Exists/ IN (Select...) таким образом, чтобы вашему партнеру не было запрещено использовать поиск по индексу (все нужные ему джойны он сам построит без ваших специальных указок).

Вряд ли вы найдете способ сделать "быстрый джйн" в случае наложения запрета на использование индексов.
постройте global temporary table, что ли. Если иначе напрямую искать никак нельзя.
7 дек 09, 02:27    [8026180]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
старенький эх
ранний отказ:
про SQL server я ничего не знаю, потому никакой "готовый запрос" даже начинать предлагать не буду.

Мадам, включите здравый смысл:
вы рисуете join по наборам, размер котрым меняется от малых до больших, не предоставив возможности серверу, который за вас трудится, использовать индексы.
Если на малых наборах безындексный джойн - еще туда - сюда, то на больших - ваши упреки партнеру в нехватке сил - чрезмерны.

Кроме того, никаких джойнов в показанных вами запросах рисовать не требуется.
Требуется составить выражения вида Exists/ IN (Select...) таким образом, чтобы вашему партнеру не было запрещено использовать поиск по индексу (все нужные ему джойны он сам построит без ваших специальных указок).

Вряд ли вы найдете способ сделать "быстрый джйн" в случае наложения запрета на использование индексов.
постройте global temporary table, что ли. Если иначе напрямую искать никак нельзя.


1. Читаем внимательно посты. По временной таблице по полю для джоина делается кластерный индекс.
2. Про Exist в больших временных таблицах вообще не надо, еще хуже чем JOIN

В остальном.... даже ничего не понятно, что хотели сказать то.
7 дек 09, 05:30    [8026212]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
aleks2
Guest
Volochkova
aleks2
Volochkova,

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     [i][b]INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID[/b][/i] OR not exists(select * FROM #sp_1)
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData


Не вариант ибо те же тормоза.



Не жуйте сопли, мадам, лучше предствьте ПЛАН выполнения.

А уж потом... раскажете сказку... хе-хе.
7 дек 09, 06:30    [8026228]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
aleks2
Volochkova
aleks2
Volochkova,

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     [i][b]INNER JOIN  #sp_1  s1 on r.SP_1_ID = s1.ID[/b][/i] OR not exists(select * FROM #sp_1)
     INNER JOIN  #sp_2  s1 on r.SP_2_ID = s2.ID
     INNER JOIN  #sp_3  s1 on r.SP_3_ID = s3.ID
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData


Не вариант ибо те же тормоза.



Не жуйте сопли, мадам, лучше предствьте ПЛАН выполнения.

А уж потом... раскажете сказку... хе-хе.


Это ты типа блеснул интеллектом?
ЧТО тебе скажет план, если ты логики даже не сечешь?
7 дек 09, 07:01    [8026242]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
aleks2
Guest
Volochkova
Это ты типа блеснул интеллектом?
ЧТО тебе скажет план, если ты логики даже не сечешь?


Гм... Мадам, кажеться это вы плачетесь в жилетку?
У меня то все работает... хе-хе. В том числе и подобные конструкции фильтрации.

Так шо вольно вам пыжиться.
7 дек 09, 07:12    [8026252]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Влом регистрироваться
Guest
Volochkova,

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
Where
     ((Len(@Params1) = 0 And Len(@Params2) = 0 And Len(@Params3) = 0) OR
     (ID IN (@Params1, @Params2, @Params3))) And
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData
7 дек 09, 08:20    [8026314]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Влом регистрироваться
Guest
вчитался, в предыдущем сообщении фигню написал...

Вот так будет:

Select 
    DocData ,
    Sum(KolProd)  As Sum_KolProd,
    Sum(TotalSum) As Sum_TotalSum
From
     Register_data r 
     INNER JOIN  sp_1  s1 on r.SP_1_ID = s1.ID And (Len(@Params1) = 0 OR ID = @Params1)
     INNER JOIN  sp_2  s2 on r.SP_2_ID = s2.ID And (Len(@Params2) = 0 OR ID = @Params2)
     INNER JOIN  sp_3  s3 on r.SP_3_ID = s3.ID And (Len(@Params3) = 0 OR ID = @Params3)
Where
     DocData between @d1 And @d2
Group by
      DocData
Order by 
     DocData


Ну и индексы не забываем строить...
7 дек 09, 08:26    [8026321]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Вообщем новая беда..

create table #regdata (Docdata datetime , ProductID int, KolProd int)
Create table #sp_filter (ProductID int)
declare @Flag int
Select @flag = 1

Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-01',1,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-02',1,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-03',1,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-04',1,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-05',1,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-06',1,2)

Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-01',2,1)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-02',2,2)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-03',2,3)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-04',2,4)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-05',2,5)
Insert into #regdata (Docdata, ProductID, KolProd)
Values ('2009-01-06',2,6)

If @flag = 1 
	insert into #sp_filter (ProductID) Values (1)


Select
	DocData,
	Sum(Kolprod)
From
	#regdata r inner join #sp_filter sp_1 on r.ProductID = case when @flag = 1 then sp_1.ProductID else r.ProductID end
Where 
	DocData between '2009-01-02' and '2009-01-03'
Group by
	DocData


drop table #regdata
drop table #sp_filter 


Если запускать с Select @flag = 1 то фильтр отрабатывает как надо
А если с Select @flag = 0 то Join не отрабатывает как надо
7 дек 09, 11:08    [8027043]     Ответить | Цитировать Сообщить модератору
 Re: Хитрый Join  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
В принципе разобрались, тему можно закрывать.
Всем спасибо
7 дек 09, 11:21    [8027162]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить