[MSSQL] Dubbele JOIN op tabel vertraagt 4 minuten

[coenbijlsma]

Ik ben een query aan het schrijven waarin ik verkoopgegevens van een bepaalde datumrange in dit jaar naast dezelfde datumrange vorig jaar leg. Query afgeschreven (so far, so good), maar nu join ik een tabel twee keer in deze query omdat ik hier de factuurgegevens uit haal van dit jaar en vorig jaar.

De query draait prima (2 sec) met de eerste join (alleen dit jaar), maar zodra ik de join voor de gegevens van vorig jaar toevoeg duurt het opeens 4 minuten! Iemand een idee hoe dit kan?

Voor de volledigheid hieronder de relevante sql-code:

SQL:

declare
    @artVan varchar
    , @artTm varchar
    , @classVan int
    , @classTm int
    , @boekjaar2 int
    , @datumVan datetime
    , @datumTm datetime

set @artVan = '0'
set @artTm = 'ZZZZZZ'
set @classVan = 0
set @classTm = 999999
set @boekjaar2 = 2006
set @datumVan = '2007-01-01'
set @datumTm = '2007-01-31'
 --EINDE TESTEN
declare
    @datumVanVJ datetime
    , @datumTmVJ datetime

set @datumVanVJ = cast(@boekjaar2 as varchar(4)) + '-' + cast(month(@datumVan) as varchar) + '-' + cast(day(@datumVan) as varchar)
set @datumTmVJ = cast(@boekjaar2 as varchar(4)) + '-' + cast(month(@datumTm) as varchar) + '-' + cast(day(@datumTm) as varchar)

--Select alle velden die nodig zijn...

from Items it (nolock)

left join 
    -- FaktuurHistorie Huidig Jaar
    frhsrg as fhhj (nolock) on fhhj.artcode = it.ItemCode and fhhj.fakdat between @datumVan and @DatumTm
left join 
    -- FaktuurHistorie VergelijkingsJaar
    frhsrg as fhvj (nolock) on fhvj.artcode = it.ItemCode and fhvj.fakdat between @datumVanVJ and @datumTmVJ
inner join 
    frhkrg (nolock) on frhkrg.faknr = fhhj.faknr
where it.ItemCode between @artVan and @artTm
and it.Type not in ('P')
and it.Class_02 in(select ItemClassCode from ItemClasses where ItemClassCode between @classVan and @classTm)
group by it.ItemCode
having sum( fhhj.esr_aantal ) is not null
order by groep2, omzet desc, it.ItemCode

[Verwijderd]

je wilt dus de gegevens van bijvoorbeeld januari 2007 tot april 2007 en januari 2006 tot april 2006 ophalen en er iets mee doen?

en dat moet in 1 query? ik zou gewoon 2 queries gebruiken tenzij je dat wat je wilt er mee wilt doen alleen in sql kunt doen.

het tijdsverschill zou wel eens kunnen zitten in die vergelijking. ik weet niet hoe het bij MMSQL gaat maar bij MySQL wordt de vergelijking in sommige gevallen uitgevoerd op de HELE database en dan pas worden de relevante resultaten gefilterd. zou wel eens kunnen dat dat er mee te maken heeft.

[coenbijlsma]

ja, ik wil het indien enigszins mogelijk wel in één query hebben, omdat ik het anders in Crystal Reports aan elkaar moet gaan plakken. Maar ik kan het eens testen, het gaat allicht sneller

[cowgirl]

Volgens mij gaat vorig jaar inner joinen op huidig jaar en duurt het daarom zo lang. Is het alleen een aggregate wat je nodig hebt voor vorig jaar en huidig jaar? Dan kan je het waarschijnlijk omschrijven naar 2 subselects.

[P_de_B]

Vergeet niet dat de exact database in het geheel geen indexen bevat. Het toevoegen van een aantal relevante indexen kan het heel veel sneller maken.

[coenbijlsma]

@P_de_B: Dat klopt inderdaad. We kunnen er wel indexen aan toevoegen, maar dat moet in overleg met iemand van een andere vestiging. Wat ik zo vreemd vind is dat het wel snel werkt als ik alleen de join op huidig jaar laat staan, het zijn toch dezelfde velden waar ik op join...

@cowgirl: ik dacht van niet, want dan moet je de inner join van vorig jaar direct na de inner join woorden van huidig jaar plaatsen volgens mij.

[P_de_B]

Bekijk het executieplan eens in Query Analyzer van beide situaties. Dan zie je denk ik eenvoudig waar het mis gaat.

[coenbijlsma]

Heb ik gedaan, en hij gebruikt 84% van z'n tijd op die tweede join op de tabel frhsrg. Het probleem is dat ik niet weet hoe ik het op moet lossen zonder het aanmaken van relevante indices. Dat moet toch kunnen??

[Verwijderd]

ik weet niet precies hoe het met MSSQL onder de motorkap duurt maar zou niet kunnen zijn dat deze voor elke row in SELECT_A een select, zonder index dus, doet op de hele database?!?!?!

dit gedrag heb ik onder MySQL een keer zien gebeuren door een bijna/onmogelijke query te gebruiken op een tabel zonder index. MySQL begon toen voor elke row in de SELECT de hele database af te lopen.

geen pretje. je query doet dan eigenlijk SELECT_A^{COUNT_SELECT_A} selects wat dus alleen maar erger wordt als de tabel groter wordt. grote tabellen zonder index zijn sowiezo een ramp.

[cowgirl]

cowgirl schreef op vrijdag 20 april 2007 @ 15:48:
Is het alleen een aggregate wat je nodig hebt voor vorig jaar en huidig jaar? Dan kan je het waarschijnlijk omschrijven naar 2 subselects.

Kan je hiermee niets, of heb je meerdere velden / meerdere records uit die tabellen nodig? Als je bijvoorbeeld een totaalbedrag voor die periode per jaar nodig hebt, krijg je zoiets als

SQL:

--Select alle velden die nodig zijn...,
(select sum(orderbedrag) from 
frhsrg where frhsrg.artcode = it.ItemCode and frhsrg.fakdat between @datumVan and @DatumTm)
as fhhj,
(select sum(orderbedrag) from
frhsrg where frhsrg.artcode = it.ItemCode and frhsrg.fakdat between @datumVanVJ and @datumTmVJ) 
as fhvj 

[coenbijlsma]

nee, het probleem is dat ik die velden inderdaad niet gesommeerd nodig heb (wel per artikel maar geen grand total zeg maar). Anders had ik inderdaad zoiets kunnen proberen.
Ik ga maar eens proberen of ik de query toch niet uit elkaar kan trekken in 2 aparte queries en die dan maar weer in Crystal samenvoegen...

[pistole]

coenbijlsma schreef op vrijdag 20 april 2007 @ 16:13:
nee, het probleem is dat ik die velden inderdaad niet gesommeerd nodig heb (wel per artikel maar geen grand total zeg maar). Anders had ik inderdaad zoiets kunnen proberen.
Ik ga maar eens proberen of ik de query toch niet uit elkaar kan trekken in 2 aparte queries en die dan maar weer in Crystal samenvoegen...

Kan je de twee queries niet UNION'en? Dan hoef je ze niet aan elkaar te plakken in CR.

[P_de_B]

Dit zal eenvoudig met een goede index op te lossen zijn vermoed ik. Als dat geen optie is kun je een indexed view maken, maar dan heb je afaik de Enterprise versie van MS SQL nodig.

Lukt dat ook niet, dan zou je het nog op een ander manier kunnen proberen:

pseudocode

SQL:

SELECT bla, SUM(CASE boekjaar WHEN 2006 THEN veld_met_bedrag ELSE 0 END) AS Bedrag2006, SUM(CASE boekjaar WHEN 2007 THEN veld_met_bedrag ELSE 0 END) as Bedrag2007 
FROM [///]

[ATS]

Domme vraag misschien, maar aangezien huidig en vorig jaar een continue tijdsreeks zal zijn, kan je dat toch ook in één keer binnenhalen? Zelfs als het niet continue is (maar dezelfde periode als dit jaar bijvoorbeeld) kan dat trouwens, alleen wordt de voorwaarde van je join wat ingewikkelder. Je probleem is nu denk ik dat je in je resultaat alle combinaties van de orderhistorie krijgt over dit jaar en vorig jaar. Dus:

klant	orders 2006	orders huidige jaar
bla	order 1 2006	order 1 2007
bla	order 1 2006	order 2 2007
bla	order 2 2006	order 1 2007
bla	order 2 2006	order 2 2007
bla	order 3 2006	order 1 2007
bla	order 3 2006	order 2 2007

In plaats van:

klant	orders
bla	order 1 2006
bla	order 2 2006
bla	order 3 2006
bla	order 1 2007
bla	order 2 2007

Dat wil zeggen, in dit geval 2x3 tuples in plaats van 2+3. Als de getallen wat groter zijn dan loopt dat wat meer uit de hand...

[coenbijlsma]

Beter laat dan nooit, maar ik heb het probleem inmiddels een tijdelijke oplossing gegeven: ik heb de joins verwijderd ne in plaats daarvan subqueries gemaakt voor de betreffende velden. Nu doet de query er 2 seconden over ipv 7 minuten

Ik moet nog overleggen met de systeembeheerder of ik een index kan krijgen op die tabel. Als dat gebeurd is kan ik de query weer aanpassen en de join gebruiken.

@pistole:
Ik denk het niet, want niet alle velden die ik opvraag komen in beide tabellen voor..

@ATS:
Klopt inderdaad, maar ik wil de resultaten van huidig en vorig jaar in één regel naast elkaar hebben staan. Volgens mij is er geen andere constructie die dan overblijft behalvedan een join of subqueries op de velden.

[ Voor 28% gewijzigd door coenbijlsma op 23-04-2007 14:59 . Reden: gereplied op pistole && ATS ]