[MSSQL] Query duurt lang (van 2 naar 45sec)

[jvaneijk]

Goedemorgen allemaal,

Ik heb twee queries, of beter gezegd een query vervangen voor een nieuwe. Nu wil het echter zo zijn dat de oude query maar 2 seconde duurde en de nieuwe query soms wel 45 seconde in beslag neemt.
Beide queries geven zelde uitkomst alleen heeft de nieuwe query een extra kolom welke getoont wordt. Deze wordt berekend in een subselect.

Zelf kom ik er niet echt uit waarom er zo'n groot tijdsverschil zit tussen deze 2 queries, en hoop daarom ook dat iemand hier op tweakers mij de goede richting in kan schoppen.

Hieronder de 2 queries, eerste bovenste query is oude situatie onderste query is de nieuwe situatie.

SQL:

SELECT      dbo.tbl_RTV_Sources.RTV_Source,
            dbo.tbl_RTV.RTVPO,
            dbo.tbl_RTV.RTVRMA,
            dbo.tbl_RTV.StatusID,
            COUNT(*) AS QtyRTV,
            dbo.tbl_RTV.DateRTVShipping,
            dbo.tbl_RTV_Sources.RTV_Source_ID
    FROM    dbo.tbl_RTV
            INNER JOIN dbo.tbl_RTV_Sources ON dbo.tbl_RTV.RTVLocation = dbo.tbl_RTV_Sources.RTV_Source_ID
            LEFT OUTER JOIN dbo.tbl_Orders ON dbo.tbl_RTV.BCN = dbo.tbl_Orders.BCN
    GROUP BY dbo.tbl_RTV_Sources.RTV_Source,
            dbo.tbl_RTV.StatusID,
            dbo.tbl_RTV.RTVRMA,
            dbo.tbl_RTV.RTVPO,
            dbo.tbl_RTV.DateRTVShipping,
            dbo.tbl_RTV_Sources.RTV_Source_ID
    HAVING  ( dbo.tbl_RTV.StatusID = 14 )
    ORDER BY dbo.tbl_RTV_Sources.RTV_Source,
            dbo.tbl_RTV.RTVPO

SQL:

SELECT
        dbo.tbl_RTV_Sources.RTV_Source,
        dbo.tbl_RTV.RTVPO,
        dbo.tbl_RTV.RTVRMA,
        dbo.tbl_RTV.StatusID,
        COUNT(*) AS QtyOpen,
        qtyrtv.QtyRTV,
        dbo.tbl_RTV.DateRTVShipping,
        dbo.tbl_RTV_Sources.RTV_Source_ID
FROM    dbo.tbl_RTV
        LEFT OUTER JOIN (SELECT dbo.tbl_RTV.RTVPO,
                                    COUNT(*) AS QtyRTV
                          FROM      dbo.tbl_RTV
                          GROUP BY  dbo.tbl_RTV.RTVPO
                        ) qtyrtv ON dbo.tbl_RTV.RTVPO = qtyrtv.RTVPO
        INNER JOIN dbo.tbl_RTV_Sources ON dbo.tbl_RTV.RTVLocation = dbo.tbl_RTV_Sources.RTV_Source_ID
        LEFT OUTER JOIN dbo.tbl_Orders ON dbo.tbl_RTV.BCN = dbo.tbl_Orders.BCN
GROUP BY dbo.tbl_RTV_Sources.RTV_Source,
        dbo.tbl_RTV.RTVPO,
        dbo.tbl_RTV.RTVRMA,
        dbo.tbl_RTV.StatusID,
        qtyrtv.QtyRTV,
        dbo.tbl_RTV.DateRTVShipping,
        dbo.tbl_RTV_Sources.RTV_Source_ID
        ,dbo.tbl_Orders.DateShipping
HAVING dbo.tbl_RTV.StatusID = 14
        AND dbo.tbl_Orders.DateShipping IS NULL
ORDER BY dbo.tbl_RTV_Sources.RTV_Source,
            dbo.tbl_RTV.RTVPO

Alvast bedankt.

EDIT:
Waarom ik dit tijdsverschil erg vindt is omdat ik een timeout krijg tijdens de uitvoer van de query. En ik da in mijn Access frontend allerlei meldingen krijg als #naam / #name

[ Voor 3% gewijzigd door jvaneijk op 22-06-2009 11:39 ]

[gorgi_19]

Wat zegt je execution plan waar hij op hangt?

[jvaneijk]

Ik heb nooit geweten dat dat erin zat. Bedankt voor de mededeling.
Ik heb het idee dat ik naar een soort profiler kijk?
Hier staat dat de Clustered Index Scan 30% kost 2x een van 30% en 1x een van 36% en nog een Hash Match van 3%

Nu heb ik alleen nog geen idee hoe ik die Clustered Index Scans kan versnellen.

[Woy]

Je zult inderdaad eens naar je executions plans moeten kijken. Zonder meer data valt er weinig van te zeggen. Als er een Table-scan gedaan moet worden in plaats van een index lookup zal dat enorme gevolgen voor je executie tijd kunnen hebben.

jvaneijk schreef op maandag 22 juni 2009 @ 12:29:
Ik heb nooit geweten dat dat erin zat. Bedankt voor de mededeling.
Ik heb het idee dat ik naar een soort profiler kijk?
Hier staat dat de Clustered Index Scan 30% kost 2x een van 30% en 1x een van 36% en nog een Hash Match van 3%

Nu heb ik alleen nog geen idee hoe ik die Clustered Index Scans kan versnellen.

Een clustered index scan zul je niet zoveel kunnen versnellen, behalve door hem totaal niet nodig te hebben. Je zou nog kunnen kijken of je meer onderhoud aan je database moet geven. Dus probeer je index eens te rebuilden.

Wat me overigens opvalt is je HAVING clause! Waarom doe je dat niet gewoon in het WHERE gedeelte? Het zijn immers geen geaggregeerde waardes die je wilt filteren.

[ Voor 66% gewijzigd door Woy op 22-06-2009 12:35 ]

[gorgi_19]

Eventueel kan je de boel nog iets verder versnellen met de juiste indexen (en goede volgorde) op je ORDER BY kolommen. Maar execution plan is idd het belangrijkste.

Verder met ^^^

[ Voor 5% gewijzigd door gorgi_19 op 22-06-2009 12:51 ]

[jvaneijk]

Ik heb ook een testomgeving welke een op zich staande computer welke dezelfde SPs hebben als de live omgeving deze computer is echter veel langzamer ( Pentium D 2.8Ghz 2GBram tegenover een Quad Xeon 4GBRam ) alleen de data is niet heel erg recent maar doet het ook binnen 2 seconden. ong evenveel records geeft hij terug

na een Execution plan te hebben uitgevoerd op de testserver komt hij ook met de Clustered Index Scans die meeste tijd in beslag nemen, niet helemaal dezelfde percentages maar kost wel meeste tijd.

Nu ben ik dus een beetje lost

[whoami]

Op welke velden liggen die clustered indexes (by default op je PK).
Op welke velden heb je nog andere indexen liggen ? Je kan je query drastisch versnellen als je goede indexen kiest; bv op de velden waar je op joined (wel even nadenken welke velden je in welke index stopt, en in welke volgorde).

Kijk ook eens hoe je tabel gefragmenteerd is. (dbcc showcontig)

en als laatste zou je ook eens kunnen denken of het nuttig kan zijn om wat te gaan denormaliseren ...

[jvaneijk]

whoami schreef op maandag 22 juni 2009 @ 13:08:
Op welke velden liggen die clustered indexes (by default op je PK).
Op welke velden heb je nog andere indexen liggen ? Je kan je query drastisch versnellen als je goede indexen kiest; bv op de velden waar je op joined (wel even nadenken welke velden je in welke index stopt, en in welke volgorde).

Kijk ook eens hoe je tabel gefragmenteerd is. (dbcc showcontig)

en als laatste zou je ook eens kunnen denken of het nuttig kan zijn om wat te gaan denormaliseren ...

hehe, deze database is zo slecht dat danormalisatie niet noodzakelijk is, kan beter zeggen normalisatie is een groot begrip hier binnen de database.

Ook moet ik zeggen dat er nergens Indexen op staan of iig niet zelf aangebrachte indexen dus dan is alles op de PKs voor iedere tabel neem ik aan?

En hoe bedoel je nadenken over welke velden ik in welke index stop n in welke volgorde?

[Meekoh]

Misschien handig om je even in te lezen in Database beheer en query optimalisatie.
Goede plek om te beginnen is: http://www.sql-server-performance.com/tips/all_main.aspx

[jvaneijk]

Hieronder de showcontig van de 3 aangedane tabellen

code:

DBCC SHOWCONTIG scanning 'tbl_RTV' table...
Table: 'tbl_RTV' (965018619); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 11072
- Extents Scanned..............................: 1399
- Extent Switches..............................: 8911
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 15.53% [1384:8912]
- Logical Scan Fragmentation ..................: 86.29%
- Extent Scan Fragmentation ...................: 75.70%
- Avg. Bytes Free per Page.....................: 3006.1
- Avg. Page Density (full).....................: 62.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tbl_RTV_Sources' table...
Table: 'tbl_RTV_Sources' (1530540586); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 66.67%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 1818.3
- Avg. Page Density (full).....................: 77.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tbl_Orders' table...
Table: 'tbl_Orders' (1557788807); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 25089
- Extents Scanned..............................: 3156
- Extent Switches..............................: 19590
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.01% [3137:19591]
- Logical Scan Fragmentation ..................: 79.74%
- Extent Scan Fragmentation ...................: 69.96%
- Avg. Bytes Free per Page.....................: 3152.7
- Avg. Page Density (full).....................: 61.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Eerlijk gezegd heb ik niet echt een goed idee waarnaar ik kijk. Ik heb wel gekeken bij de help van showcontig en daar staat wel netjes omschreven wat de verschillende waardes betekenen maar dat snap ik nog niet helemaal, dus hoop dat iemand mij hier wat meer opheldering over kan geven welke van belang zijn en welke niet?

[Woy]

Kom op, je kan zelf ook wel wat onderzoek doen voordat je alles hier neer gooit. Als ik [google=showcontig], dan is de 2e hit http://www.sql-server-per...t_dbcc_showcontig_p1.aspx

Daar moet je toch het een en ander kunnen vinden.

[d00d]

Welke versie van SQL Server gebruik je? Als het 2005 of hoger is zou je het volgende kunnen proberen:

SQL:

;WITH qtyrtv AS
(
    SELECT dbo.tbl_RTV.RTVPO,
    COUNT(1) AS QtyRTV
    FROM      dbo.tbl_RTV
    GROUP BY  dbo.tbl_RTV.RTVPO
)
SELECT
    dbo.tbl_RTV_Sources.RTV_Source,
    dbo.tbl_RTV.RTVPO,
    dbo.tbl_RTV.RTVRMA,
    dbo.tbl_RTV.StatusID,
    COUNT(1) AS QtyOpen,
    qtyrtv.QtyRTV,
    dbo.tbl_RTV.DateRTVShipping,
    dbo.tbl_RTV_Sources.RTV_Source_ID
FROM    dbo.tbl_RTV
        LEFT OUTER JOIN qtyrtv ON dbo.tbl_RTV.RTVPO = qtyrtv.RTVPO
        INNER JOIN dbo.tbl_RTV_Sources ON dbo.tbl_RTV.RTVLocation = dbo.tbl_RTV_Sources.RTV_Source_ID
        LEFT OUTER JOIN dbo.tbl_Orders ON dbo.tbl_RTV.BCN = dbo.tbl_Orders.BCN
WHERE dbo.tbl_RTV.StatusID = 14
        AND dbo.tbl_Orders.DateShipping IS NULL
GROUP BY dbo.tbl_RTV_Sources.RTV_Source,
        dbo.tbl_RTV.RTVPO,
        dbo.tbl_RTV.RTVRMA,
        dbo.tbl_RTV.StatusID,
        qtyrtv.QtyRTV,
        dbo.tbl_RTV.DateRTVShipping,
        dbo.tbl_RTV_Sources.RTV_Source_ID
        ,dbo.tbl_Orders.DateShipping
ORDER BY dbo.tbl_RTV_Sources.RTV_Source,
            dbo.tbl_RTV.RTVPO

[whoami]

Zowiezo: begin bij het begin.
Normaliseer je database (indien mogelijk).
Zorg voor goede indexen. Lees desnoods eens eea of wat een index is, en hoe je een goede index definieert. (bv een indx op het RTVPO veld in tbl_RTV zou al kunnen helpen). Idem voor de velden BCN, RTV_Source_ID ...

Met de output van DBCC showcontig kan je zien over hoeveel extents je data in een tabel verspreid is. Hoe optimaler deze opgeslagen zijn, hoe minder reads er zullen moeten gebeuren. In jouw geval is het niet echt optimaal te noemen. Dat kan zijn omdat je clustered index op een veld ligt dat kan veranderen, of dat niet sequentieel 'aangroeit', waardoor er veel page-splits gebeuren.

[winkbrace]

Als het veel data is die je wilt joinen kan het juist sneller zijn om indexen te ontwijken en gewoon full table scans te gebruiken. Je query is an sich namelijk niet inefficiënt. Het hangt van de data af.

[whoami]

Hmm, sorteren en joinen zal mi toch altijd sneller gaan met een index.
Filteren zal in sommige gevallen idd sneller zijn zonder index, zeker als de index niet selectief is.
Trouwens, een beetje DBMS zal wel bepalen of hij in dat geval beter de index gebruikt, of toch maar beter een table scan doet (adhv de statistics waarover hij beschikt). Iig, MSSQL doet dat.

[Confusion]

Hoeveel unieke (of juist overlappende) dbo.tbl_RTV.RTVPO zitten er in dbo.tbl_RTV? Een outer join van die table op zichzelf, via de subquery in de join, kan de hoeveelheid rows behoorlijk doen ontploffen. Levert die tweede query niet veel meer rows op dan die eerste?

[ Voor 18% gewijzigd door Confusion op 22-06-2009 21:39 ]

[Verwijderd]

met een subquery ga je m.i. sneller af zijn dan met de extra join:

SQL:

SELECT 
        dbo.tbl_RTV_Sources.RTV_Source, 
        dbo.nieuwenaam.RTVPO, 
        dbo.nieuwenaam.RTVRMA, 
        dbo.nieuwenaam.StatusID, 
        COUNT(*) AS QtyOpen, 
        (select COUNT(*)  from dbo.tbl_rtv where dbo.tbl_rtv.rtvpo = nieuwenaam.rtvpo) AS QtyRTV, 
        dbo.nieuwenaam.DateRTVShipping, 
        dbo.tbl_RTV_Sources.RTV_Source_ID 
FROM    dbo.tbl_RTV nieuwenaam
                INNER JOIN dbo.tbl_RTV_Sources ON dbo.tbl_RTV.RTVLocation = dbo.tbl_RTV_Sources.RTV_Source_ID 
        LEFT OUTER JOIN dbo.tbl_Orders ON dbo.tbl_RTV.BCN = dbo.tbl_Orders.BCN 
GROUP BY dbo.tbl_RTV_Sources.RTV_Source, 
        dbo.tbl_RTV.RTVPO, 
        dbo.tbl_RTV.RTVRMA, 
        dbo.tbl_RTV.StatusID, 
        qtyrtv.QtyRTV, 
        dbo.tbl_RTV.DateRTVShipping, 
        dbo.tbl_RTV_Sources.RTV_Source_ID 
        ,dbo.tbl_Orders.DateShipping 
HAVING dbo.tbl_RTV.StatusID = 14 
        AND dbo.tbl_Orders.DateShipping IS NULL 
ORDER BY dbo.tbl_RTV_Sources.RTV_Source, 
            dbo.tbl_RTV.RTVPO

(query is niet getest)

[whoami]

Verwijderd schreef op dinsdag 23 juni 2009 @ 12:33:
met een subquery ga je m.i. sneller af zijn dan met de extra join:

SQL:

SELECT 
        dbo.tbl_RTV_Sources.RTV_Source, 
        dbo.nieuwenaam.RTVPO, 
        dbo.nieuwenaam.RTVRMA, 
        dbo.nieuwenaam.StatusID, 
        COUNT(*) AS QtyOpen, 
        (select COUNT(*)  from dbo.tbl_rtv where dbo.tbl_rtv.rtvpo = nieuwenaam.rtvpo) AS QtyRTV, 
        dbo.nieuwenaam.DateRTVShipping, 
        dbo.tbl_RTV_Sources.RTV_Source_ID 
FROM    dbo.tbl_RTV nieuwenaam
                INNER JOIN dbo.tbl_RTV_Sources ON dbo.tbl_RTV.RTVLocation = dbo.tbl_RTV_Sources.RTV_Source_ID 
        LEFT OUTER JOIN dbo.tbl_Orders ON dbo.tbl_RTV.BCN = dbo.tbl_Orders.BCN 
GROUP BY dbo.tbl_RTV_Sources.RTV_Source, 
        dbo.tbl_RTV.RTVPO, 
        dbo.tbl_RTV.RTVRMA, 
        dbo.tbl_RTV.StatusID, 
        qtyrtv.QtyRTV, 
        dbo.tbl_RTV.DateRTVShipping, 
        dbo.tbl_RTV_Sources.RTV_Source_ID 
        ,dbo.tbl_Orders.DateShipping 
HAVING dbo.tbl_RTV.StatusID = 14 
        AND dbo.tbl_Orders.DateShipping IS NULL 
ORDER BY dbo.tbl_RTV_Sources.RTV_Source, 
            dbo.tbl_RTV.RTVPO

(query is niet getest)

Ik zou dergelijke statements enkel maar maken als je het ook daadwerkelijk getest hebt ....
Goeie kans dat de execution plans in beide gevallen gewoon identiek zijn.

[Confusion]

Mag ik trouwens even offtopic adviseren om in de toekomst die nutteloze prefix tbl_ weg te laten en iets te doen aan het inconsistente mengelmoesje van StatusID, RTV_Source_ID, RTVPO en rtvpo? Een beetje consistentie, zoals 'status_id', 'rtv_source_id' en rtv_po zou het een stuk begrijpelijker maken, als je daarnaast ook gewoon rtv_post_office (of WTF het dan ook mag zijn) uit zou schrijven, dan zou iemand die er verder niets van weet misschien ook nog begrijpen waar het eigenllijk over gaat. Afkortingen in code, inclusief sql, zijn de vijand van leesbaarheid en begrijpelijkheid.