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

Откуда:
Сообщений: 21
Доброго времени суток, уважаемые знатоки!

Занимаюсь запросом, который обрабатывает и возвращает значительное количество записей, что требует его оптимизации. Вот тут то мне и нужна Ваша помощь, Ваши размышления.
Суть задачи такова, необходимо вернуть только записи документов 3-го типа, которые через документ-родитель имеют связь с документом 2-го типа. Количество ссылок у документа-родителя на каждый из типов не должно быть больше одной, в противном случае ссылки документа-родителя не учитывать. Т.е. документ-родитель всегда обязан иметь не более одной ссылки на документ типа 2, также не более одной, но не в обязательном порядке ссылку на документ типа 3.
Ниже приведен пример табличных данных.
Столбец DocRef получен присоединением к таблице документов таблицы ссылок по столбцу DocID.
Документы типов, отличных от 2-3 не учитывать. Один документ может участвовать в ссылочной связи как правильным документом-родителем, так и с неправильным, при этом его все равно выводить.
На текущий момент я решаю эту проблему следующим образом (запрос обезличен):

select distinct sum (case when DC.DocType = 3 then DC.DocID else 0 end) 'ID'
from
	(
	select D.DocID 'Parent'
		, DC.DocID 'Child'
		, DC.DocType
		, count (DC.DocID) over (partition by D.DocID, DC.DocType) Cnt -- Считаем ссылки
	from Docs D
	left join Refs R on D.DocID = R.DocID
	join Docs DC on DC.DocID = R.DocRef and DC.DocType in (2,3)
	where D.DocType = 1
	) X
where X.Cnt = 1 -- Убираем документ-родители с количеством ссылок более 1
group by D.DocID
having (sum (case when DC.DocType = 2 then DC.DocID else 0 end)) * (sum (case when DC.DocType = 3 then DC.DocID else 0 end)) != 0 -- Отсеиваем документы без ссылки на один из типов


Если в моем запросе есть ошибка, то просьба указать на нее. Также буду рад комментариям =)
Надеюсь не сильно перегрузил Вас своей задачей, всем заранее очень благодарен за советы или примеры!

К сообщению приложен файл. Размер - 147Kb
20 дек 13, 22:56    [15326249]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Davulcu
Member

Откуда:
Сообщений: 21
Прошу прощения, в первом сообщении допустил ошибки, прикладываю исправленный вариант, со скриптом создания таблиц. Еще раз спасибо!

CREATE TABLE [dbo].[Docs] 
(
	[DocType] [int] NOT NULL ,
	[DocID] [int] NOT NULL ,
	[DocName] [char] (50) NOT NULL
)
GO

CREATE TABLE [dbo].[Refs] 
(
	[DocID] [int] NOT NULL ,
	[DocRef] [int] NOT NULL ,
)
GO

insert into Docs values(1, 1, 'СТП 2013.001')
insert into Docs values(1, 2, 'СТП 2013.002')
insert into Docs values(1, 3, 'СТП 2013.003')
insert into Docs values(1, 4, 'СТП 2013.004')
insert into Docs values(1, 5, 'СТП 2013.005')
insert into Docs values(1, 7, 'СТП 2013.007')
insert into Docs values(2, 16, 'ГОСТ 2.105-95')
insert into Docs values(2, 17, 'ГОСТ 2.304-81')
insert into Docs values(2, 18, 'ГОСТ 2.307-68')
insert into Docs values(2, 19, 'ГОСТ 3.1109-82')
insert into Docs values(2, 20, 'ГОСТ 3.1118-82')
insert into Docs values(3, 21, 'ГОСТ 7795-70')
insert into Docs values(3, 22, 'ГОСТ 7798-70')
insert into Docs values(3, 23, 'ГОСТ 7817-80')
insert into Docs values(3, 24, 'ГОСТ 15589-70')
insert into Docs values(3, 25, 'ГОСТ 7805-70')
insert into Docs values(4, 26, 'ГОСТ 5915-70')

insert into Refs values(1, 16)
insert into Refs values(1, 21)
insert into Refs values(2, 16)
insert into Refs values(2, 17)
insert into Refs values(2, 22)
insert into Refs values(3, 18)
insert into Refs values(3, 21)
insert into Refs values(3, 23)
insert into Refs values(4, 19)
insert into Refs values(5, 24)
insert into Refs values(6, 22)
insert into Refs values(6, 25)
insert into Refs values(7, 20)
insert into Refs values(7, 25)
insert into Refs values(7, 26)

select distinct sum (case when X.DocType = 3 then X.Child else 0 end) 'ID'
from
	(
	select D.DocID 'Parent'
		, DC.DocID 'Child'
		, DC.DocType
		, count (DC.DocID) over (partition by D.DocID, DC.DocType) Cnt -- Считаем ссылки
	from Docs D
	left join Refs R on D.DocID = R.DocID
	join Docs DC on DC.DocID = R.DocRef and DC.DocType in (2,3)
	where D.DocType = 1
	) X
where X.Cnt = 1 -- Убираем документ-родители с количеством ссылок более 1
group by X.Parent
having (sum (case when X.DocType = 2 then X.Child else 0 end)) * (sum (case when X.DocType = 3 then X.Child else 0 end)) != 0 -- Отсеиваем документы без ссылки на один из типов
20 дек 13, 23:46    [15326483]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Davulcu
Member

Откуда:
Сообщений: 21
Исправил изображение:

К сообщению приложен файл. Размер - 141Kb
21 дек 13, 01:25    [15326743]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Попробуйте такой вариант:
create index IX_Refs_DocID_DocRef  on Refs(DocID,DocRef);
create index IX_Docs_DocType_DocID on Docs(DocType,DocID);
Вообще-то тут можно и select d.*, но требовалось только DocID
select d.DocID
from Docs d
join Refs r on r.DocRef=d.DocID
where d.DocType=3 and exists(
   select NULL
   from Refs f
   join Docs s on s.DocID=f.DocRef and s.DocType in (2,3)
   where f.DocID=r.DocID
   group by f.DocID
   having 
      sum(case when s.DocType=2 then 1 end)=1 and
      sum(case when s.DocType=3 then 1 end)=1
   )
Не по теме: specially для тех, кому CROSS APPLY горло режет, завернул в EXISTS
21 дек 13, 01:47    [15326768]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
o-o
Guest
+
лично мне не режет, то был вполне себе невинный вопрос "зачем ТУТ?" а в ответ как всегда:
вот есть тупоконечники, а есть остроконечники, а давайте мир поделим на тех и этих и повоюем

The APPLY operator is a nonstandard table operator that was introduced in SQL Server 2005
т.е. ограничение на версию + непереносимость на другие платформы.
так еще раз: ну и ЗАЧЕМ, если можно БЕЗ?
21 дек 13, 10:30    [15327011]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
o-o,

да я разве против?

нравятся вам длинные трудночитаемые формулы в запросах - ну и на здоровье, вам их и поддерживать, в конце концов
21 дек 13, 10:56    [15327023]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Davulcu
Member

Откуда:
Сообщений: 21
Cygapb-007,

Прирост в скорости есть! Вы кстати уже не одну задачку мне помогли закрыть, спасибо! У кого еще какие варианты будут?
21 дек 13, 12:00    [15327104]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
o-o
Guest
+
какие все нудные, жуть.
тогда и я буду нудить

мой вариант был самый читабельный: 1 раз считаем "час назад",
вынося в переменную, а потом без всяких группировок и APPLY-ев
делаем простейший запрос с WHERE.

тут же мнея загнобил Glory: лишнее определение переменной.
опять же для тех, кому не нравится подзапрос в WHERE,
aleks2 вынес подсчет "часа" в CTE, его обкакали Вы лично: "курасива".
ну, не угодишь всем: то нечитабельно, то лишняя переменная.
конечно, с APPLY и GROUP BY было куда лучше

---------------------------------------------------------------------
субботний оффтоп в оффтопе (на тему "много кода -- это плохо?")

вчера с утра получаю гневное письмо с кодом и без комментов,
одни ругательства.
начальница-ораклоидка пишет: хренов SQL Server позволяет упрощение кода?
да пошли вы все.
вот 2 варианта одного и того же UPDATE:
--versione1
UPDATE   dbo.TAB_RECUPERI_T
SET CODRESPINT=dbo.TAB_RECUPERI_GI_Riclassificato_T.[gi riclassificato]
FROM         dbo.TAB_RECUPERI_GI_Riclassificato_T INNER JOIN
                      dbo.TAB_RECUPERI_T ON dbo.TAB_RECUPERI_GI_Riclassificato_T.COD_RECUPE = dbo.TAB_RECUPERI_T.COD_RECUPE
--
--versione2
UPDATE [basedati_bi].staging.TAB_RECUPERI_T
   SET [basedati_bi].staging.TAB_RECUPERI_T.CODRESPINT =
      (select (X.[gi riclassificato])
         FROM [basedati_bi].staging.TAB_RECUPERI_GI_Riclassificato_T as X
        where X.COD_RECUPE = [basedati_bi].staging.TAB_RECUPERI_T.COD_RECUPE)
 where exists
      (select 1
         FROM [basedati_bi].staging.TAB_RECUPERI_GI_Riclassificato_T as X
        where X.COD_RECUPE = [basedati_bi].staging.TAB_RECUPERI_T.COD_RECUPE);


первый пропускает ошибки, второй -- нет!
приказ: все, что написано в духе (1) -- переписать как (2) и никогда в жизни
В МОЕЙ КОНТОРЕ чтоб не смели использовать (1).
ОРАКЛ БЫ ТАКОЕ НЕ ПРОПУСТИЛ!!!

кто бы еще что понял, о чем она.
но это вскоре выяснилось....

вот что произошло:
начальница под конец года решила переписать не нравящиеся ей конструкции.
первыми под руку попали "фичи" SQL Server-а.
вся довольная, она запустила свой вариант (2) и обнаружила, что во второй таблице нарушена уникальность
по связующему полю, т.е. ей подзапрос вернул 2 значения и UPDATE вылетел с ошибкой.
почему там оказались дубли (вернее, кто снес ПК), это уже не к нам, и кто-то еще получит,
но т.к. дубли обнаружил ее супер-код, написанный в духе оракла(на самом деле ANSI), то "все беды от SQL Server-а".
и попробуй что-то докажи.

кстати, ее претензия на этот раз (оставив в стороне тон и раздачу обзывательств) вполне обоснована:
в базе, где все подряд дб_овнеры, ключи на таблицах испаряются так же внезапно, как и появляются.
расчитывать нелья ни на что, верить нельзя никому, а код надо писать так,
чтоб он максимально "самозащищался"

вообще-то, все задокументировано:

Using UPDATE with the FROM Clause
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

если первой таблице соответствуют 2 строки из второй, (1) пройдет,
не вызывая ошибок, а вариант с EXISTS обломается.

declare @t1 table (id int, val int);
insert into @t1(id, val) values (1,1);

declare @t2 table (id int, val int);
insert into @t2(id, val) values (1,100), (1,200);

select * from @t1;
---------------------
id	val
1	1

--------------------------
--------------------------
update @t1 
set val = t2.val
from @t1 t1 join @t2 t2 on t1.id = t2.id;

select * from @t1;
--------------------
id	val
1	100

--------------------------
--------------------------
update @t1 
set val = (select t2.val 
           from @t2 t2 
           where id = t2.id)
where exists (select 1
              from @t2 t2 
              where id = t2.id);
---------------------
Msg 512, Level 16, State 1, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= 
or when the subquery is used as an expression.
The statement has been terminated.
        



21 дек 13, 12:29    [15327165]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
o-o
aleks2 вынес подсчет "часа" в CTE, его обкакали Вы лично: "курасива".
ну, не угодишь всем: то нечитабельно, то лишняя переменная.
конечно, с APPLY и GROUP BY было куда лучше
там не в том дело, что CTE, а в том, что 2 раза максимум ищется... Хотя с индексом, конечно, это не так сложно, но тоже вопрос - а зачем?

По поводу GROUP BY - вы считаете, что если писать в select AVG(..), а в конце не писать GROUP BY, то группировки не будет?
21 дек 13, 12:45    [15327189]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
o-o
Guest
Cygapb-007,

+
мериться длиной пиписек кода -- Ваша затея.
Ваш вариант в той теме с APPLY и GROUP BY благодаря им длиннее
----------------------------
предлагаю на этом пожать друг другу руки и мирно разойтись :)
хороших выходных!
21 дек 13, 13:03    [15327223]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
По сути не ответили, но полностью поддерживаю
o-o
предлагаю на этом пожать друг другу руки и мирно разойтись :)
хороших выходных!

Тем более что мериться никто не собирался, а просто был предложен вариант, в котором кому-то по горлу задело применения APPLY
21 дек 13, 14:01    [15327350]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь в оптимизации запроса, отсеивание записей, не удовлетворяющих условиям  [new]
o-o
Guest
по сути вот тут:
15327642
21 дек 13, 15:50    [15327650]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить