Zapytanie SQL wykonuje się 9 godzin - specjalista potrzebny

[piotrjadzia]

Cześć !

Może ktoś z kolegów pomoże - mam zapytanie, którym wyciągam dokumenty WZ na których były wydane indeksy z wybranych zamówień.

Kod: Zaznacz cały

With
	ID as
	(
	select
		(e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
		e.zae_gidtyp,
		e.zae_gidnumer,
		e.zae_gidlp
	from
		cdn.ZamNag z
		inner join cdn.ZamElem e on ZaN_GIDNumer=ZaE_GIDNumer
where
		z.ZaN_ZamRok=2016 and z.ZaN_ZamSeria='WG' and z.ZaN_ZamNumer in
		(9071) -- Tutaj podaję numery zamówień - z wygenerowanej listy - przeważnie około 200 numerów
	),
	WZE as
	(
	select
		(e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
		cdn.numerdokumentutrn(y.trn_gidtyp, y.trn_spityp, 0, y.trn_trnnumer, y.trn_trnrok, y.trn_trnseria) as TrnNumer
	from
		ID e
		join cdn.TraSElem s on s.TrS_ZlcTyp=e.ZaE_GIDTyp and s.TrS_ZlcNumer=e.ZaE_GIDNumer and s.TrS_ZlcLp=e.ZaE_GIDLp
		join cdn.TraElem x on x.TrE_GIDTyp=s.TrS_GIDTyp and x.TrE_GIDNumer=s.TrS_GIDNumer and x.TrE_GIDLp=s.TrS_GIDLp
		join cdn.TraNag y on y.TrN_GidTyp=x.TrE_GidTyp and y.Trn_GidNumer=x.TrE_GidNumer
	)
select
	i.ZaeId,
	STUFF((
		select
			';' + w.TrnNumer as [text()]
		from
			WZE w
		where
			w.ZaeId = i.ZaeId
		for xml path('')),1,1,'')
	as WZE
from
	id i

Takie zapytanie wykonuje się około 30 sekund. W nowym roku muszę przejść na pełne numery zamówień, gdyż mieszają mi zamówienia z tego i zeszłego roku. Przerobiłem zapytanie na takie:

Kod: Zaznacz cały

With
	ID as
	(
	select
		(e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
		e.zae_gidtyp,
		e.zae_gidnumer,
		e.zae_gidlp
	from
		cdn.ZamNag z
		inner join cdn.ZamElem e on ZaN_GIDNumer=ZaE_GIDNumer

where CONVERT(varchar,z.ZaN_ZamNumer)+'/'+CONVERT(varchar,z.Zan_ZamRok)+'/'+z.ZaN_ZamSeria in 
('9071/2016/WG') -- tutaj lista numerów zamówień
),
	WZE as
	(
	select
		(e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
		cdn.numerdokumentutrn(y.trn_gidtyp, y.trn_spityp, 0, y.trn_trnnumer, y.trn_trnrok, y.trn_trnseria) as TrnNumer
	from
		ID e
		join cdn.TraSElem s on s.TrS_ZlcTyp=e.ZaE_GIDTyp and s.TrS_ZlcNumer=e.ZaE_GIDNumer and s.TrS_ZlcLp=e.ZaE_GIDLp
		join cdn.TraElem x on x.TrE_GIDTyp=s.TrS_GIDTyp and x.TrE_GIDNumer=s.TrS_GIDNumer and x.TrE_GIDLp=s.TrS_GIDLp
		join cdn.TraNag y on y.TrN_GidTyp=x.TrE_GidTyp and y.Trn_GidNumer=x.TrE_GidNumer
	)
select
	i.ZaeId,
	STUFF((
		select
			';' + w.TrnNumer as [text()]
		from
			WZE w
		where
			w.ZaeId = i.ZaeId
		for xml path('')),1,1,'')
	as WZE
from
	id i

Po tej zdawało by się niewielkiej zmianie zapytanie wykonuje się 9 godzin (!) - co tutaj zmienić i dlaczego tak się może dziać ? Poszczególne bloki SELECT wykonują się szybko - dopiero jak się to połączy to jest taki efekt.

[Coin]

Spróbuj najpierw tak:

Kod: Zaznacz cały

    With
       ID as
       (
       select
          (e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
          e.zae_gidtyp,
          e.zae_gidnumer,
          e.zae_gidlp
       from
          cdn.ZamNag z
          inner join cdn.ZamElem e on ZaN_GIDNumer=ZaE_GIDNumer

    where cdn.NazwaObiektu(z.zan_gidtyp, z.zan_gidnumer,0,2) in
    ('ZS-1/17',
'ZS-2/17',
'ZS-3/17',
'ZS-4/17',
'ZS-5/17') -- tutaj lista numerów zamówień
    ),
       WZE as
       (
       select
          (e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
          cdn.NazwaObiektu(y.trn_gidtyp, y.trn_gidnumer,0,2) as TrnNumer
       from
          ID e
          join cdn.TraSElem s on s.TrS_ZlcTyp=e.ZaE_GIDTyp and s.TrS_ZlcNumer=e.ZaE_GIDNumer and s.TrS_ZlcLp=e.ZaE_GIDLp
          join cdn.TraElem x on x.TrE_GIDTyp=s.TrS_GIDTyp and x.TrE_GIDNumer=s.TrS_GIDNumer and x.TrE_GIDLp=s.TrS_GIDLp
          join cdn.TraNag y on y.TrN_GidTyp=x.TrE_GidTyp and y.Trn_GidNumer=x.TrE_GidNumer
       )
    select
       i.ZaeId,
       STUFF((
          select
             ';' + w.TrnNumer as [text()]
          from
             WZE w
          where
             w.ZaeId = i.ZaeId
          for xml path('')),1,1,'')
       as WZE
    from
       id i

zamiast składania numeru dokumentu i wolnego cdn.trnnumerdokumentutrn użyto cdn.nazwaobiektu.
Sprawdzisz czy to wystarczy?

[elmiq]

Powodów jest kilka, ale największym problemem wydaje się być merge join który storzyłeś, który zapewne wymusza jakiś hash match, bo dane nie są posortowane. Albo załóż dodatkowy indeks na kolumny których używasz (co powinno trochę poprawić sprawę), albo po prostu wykorzystuj co innego niż numer zamówienia. W komentarzu napisałeś:

Kod: Zaznacz cały

-- Tutaj podaję numery zamówień - z wygenerowanej listy - przeważnie około 200 numerów

Skoro generujesz listę, to czemu nie wygenerujesz jej dla ZaN_GIDNumer, zamiast dla ZaN_ZamNumer, czy też pełnego numeru zamówienia? Wtedy masz zapewnioną unikalność zamówień (czyli znika problem z zamówieniami z różnych lat) i wydajność swojego zapytania.

Pozdrawiam,
Mateusz

[elmiq]

Spróbuj najpierw tak:

Kod: Zaznacz cały

    With
       ID as
       (
       select
          (e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
          e.zae_gidtyp,
          e.zae_gidnumer,
          e.zae_gidlp
       from
          cdn.ZamNag z
          inner join cdn.ZamElem e on ZaN_GIDNumer=ZaE_GIDNumer

    where cdn.NazwaObiektu(z.zan_gidtyp, z.zan_gidnumer,0,2) in
    ('ZS-1/17',
'ZS-2/17',
'ZS-3/17',
'ZS-4/17',
'ZS-5/17') -- tutaj lista numerów zamówień
    ),
       WZE as
       (
       select
          (e.zae_gidnumer*1000)+e.zae_gidlp as ZaeId,
          cdn.NazwaObiektu(y.trn_gidtyp, y.trn_gidnumer,0,2) as TrnNumer
       from
          ID e
          join cdn.TraSElem s on s.TrS_ZlcTyp=e.ZaE_GIDTyp and s.TrS_ZlcNumer=e.ZaE_GIDNumer and s.TrS_ZlcLp=e.ZaE_GIDLp
          join cdn.TraElem x on x.TrE_GIDTyp=s.TrS_GIDTyp and x.TrE_GIDNumer=s.TrS_GIDNumer and x.TrE_GIDLp=s.TrS_GIDLp
          join cdn.TraNag y on y.TrN_GidTyp=x.TrE_GidTyp and y.Trn_GidNumer=x.TrE_GidNumer
       )
    select
       i.ZaeId,
       STUFF((
          select
             ';' + w.TrnNumer as [text()]
          from
             WZE w
          where
             w.ZaeId = i.ZaeId
          for xml path('')),1,1,'')
       as WZE
    from
       id i

zamiast składania numeru dokumentu i wolnego cdn.trnnumerdokumentutrn użyto cdn.nazwaobiektu.
Sprawdzisz czy to wystarczy?

Moim zdaniem to będzie tak samo wolne, albo nawet jeszcze gorsze ze względu na wywołanie funkcji, ale mogę się mylić.

Pozdrawiam,
Mateusz

[Coin]

Jeśli nie mamy zadawać listy a mozna dac tyllko warunek ze chcemy ZSy zatwierdzone to można to napisać tak:

Kod: Zaznacz cały

select   
distinct(ZaELV_Dokument) as Dokument_ZS
 , stuff((  select ';' + cdn.NazwaObiektu(trn_gidtyp, trn_gidnumer,0,2) AS [text()] 
            From cdn.TraNag t 
            Where t.TrN_ZaNTyp=Zan_GIDTyp AND t.TrN_ZaNNumer=Zan_GIDNumer 
            For XML PATH ('')),1,1,'') as [Numer dokumentu] 



 from cdn.ZamElemListView
 join cdn.ZamNag on zan_gidnumer=ZaELV_GIDNumer and zan_gidtyp=ZaELV_GIDTyp

 left outer join cdn.tranag FS  join cdn.traelem FSE join cdn.TraSElem FSS 
 on fse.TrE_GIDNumer=fss.TrS_GIDNumer and fse.tre_gidtyp=fss.TrS_GIDTyp and fse.tre_gidlp=fss.TrS_GIDLp on FSE.TrE_GIDNumer=FS.TrN_GIDNumer and FSE.TrE_GIDTyp=FS.TrN_GIDTyp on FS.TrN_ZaNTyp=ZaELV_GIDTyp AND FS.TrN_ZaNNumer=ZaELV_GIDNumer and ZaELV_TwrNumer=TrE_TwrNumer and ZaElv_TwrTyp=TrE_TwrTyp

 where (ZaN_ZamTyp=1280 and ZaN_Rodzaj =4) and ZaN_Stan<=21 and ZaN_Stan>=21
 group by ZaELV_Dokument, TrN_GIDTyp, TrN_GIDNumer, ZaN_GIDTyp, ZaN_GIDNumer

Praktycznie mozna to odpalac w Xlu na liście zamówień ZS a tu gdzie jest warunek dać {filtrsql} ale nie dzała to już jak chciał kolega na zasadzie podawanej listy numerów ZS w warunku.