WITH-Abfrage - wer erkennt Optimierungsbedarf?

[ny_unity]

Moinsen,

ich habe mir ein neues Thema angenommen und wollte gleich mein frisch erworbenes Wissen anwenden, an sich läuft die Abfrage, aber dauert mir zu lange... Wollte es gern live haben und nicht - wie bisher - zwischenspeichern.

Was ich auswerten möchte:
Den Nettoumsatz pro Monat/Jahr pro Disponent.

Mein aktueller Code:

Spoiler

WITH parameter AS 
    (
   SELECT   CAST('01.01.'+CAST(DATEPART(YEAR,GETDATE())-3 AS NVARCHAR(MAX)) AS DATE) AS startdatum, DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST('01.01.'+CAST(DATEPART(YEAR,GETDATE())-3 AS NVARCHAR(MAX)) AS DATE)))  AS endedatum
   UNION ALL
   SELECT   DATEADD(MONTH, 1, startdatum) AS startdatum,  DATEADD(DAY, -1, DATEADD(MONTH, 2, startdatum)) AS endedatum
   FROM   parameter
   WHERE   DATEADD(MONTH, 1, startdatum) <= CAST('31.12.'+CAST(DATEPART(YEAR, GETDATE())-3 AS NVARCHAR(MAX)) AS DATE)
   ) 
, thirdyear AS
    (
    SELECT disponent, buchdatum, umsatz FROM OPENQUERY([L1_MAND3_RO], 
        '
        SELECT k.disponent, r.buchdatum, CASE WHEN r.dnetto IS NULL THEN 0 ELSE r.dnetto - (r.dbrutto * (r.skonto1PR/100)) END AS umsatz
        FROM rechnung r, kunden k
        WHERE r.kundennr = k.kundennr
        '), parameter
    WHERE buchdatum >= startdatum AND buchdatum <= endedatum 
    )
SELECT t1.startdatum, t2.disponent, SUM(t2.umsatz) AS umsatz
FROM parameter t1, thirdyear t2 
WHERE t2.buchdatum >= t1.startdatum AND t2.buchdatum <= t1.endedatum
GROUP BY t1.startdatum, t2.disponent
ORDER BY t1.startdatum ASC

Die Daten liegen wieder auf der Firebird_Datenbank, deswegen die Abfrage via OPENQUERY auf den linked Server.

Erkennt jemand Finanzierungsspielraum?

Vielen Dank!

Erik

 

[akretschmer]

ein grundlegendes Problem bei WITH-Abfragen ist (zumindest bei PG, vermutlich aber generell), daß die via WITH definierten Tabellen immer erst einmal materialisiert werden, spricht, spätere WHERE-Conditions auf diese Tabellen schlagen nicht auf die WITH-Definition zu. Ein Umschreiben auf 'normale' Subselects kann Wunder bewirken ...

test=*# create table demo as (select s, random() as r from generate_series(1,10000000) s);
SELECT 10000000
test=*# create index demo_r on demo(r);
CREATE INDEX
test=*# commit;
COMMIT
test=# analyse demo;
ANALYZE
test=*# explain analyse with foo as (select * from demo) select * from foo where r = 0.5;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 CTE Scan on foo  (cost=154053.60..379050.45 rows=49999 width=12) (actual time=4809.753..4809.753 rows=0 loops=1)
   Filter: (r = '0.5'::double precision)
   Rows Removed by Filter: 10000000
   CTE foo
     ->  Seq Scan on demo  (cost=0.00..154053.60 rows=9999860 width=12) (actual time=0.080..2456.797 rows=10000000 loops=1)
 Planning Time: 0.342 ms
 Execution Time: 4857.610 ms
(7 rows)

test=*# explain analyse select * from (select * from demo) foo where r = 0.5;
                                                  QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------------
 Index Scan using demo_r on demo  (cost=0.43..8.45 rows=1 width=12) (actual time=0.066..0.066 rows=0 loops=1)
   Index Cond: (r = '0.5'::double precision)
 Planning Time: 0.146 ms
 Execution Time: 0.089 ms
(4 rows)

test=*#

Ich sehe sowas immer mal wieder ...

 

[ny_unity]

hm, ich schreib es mal als subselect, ich dachte immer, das WITH das Heilmittel aller Wunden ist... ich melde mich nachmittags

 

[castorp]

ein grundlegendes Problem bei WITH-Abfragen ist (zumindest bei PG, vermutlich aber generell), daß die via WITH definierten Tabellen immer erst einmal materialisiert werden

Das stimmt tatsächlich nur für Postgres - andere Optimizer (incl. der von SQL Server) sind da schlauer und flexibler. Die optimieren CTEs "gesamtheitlich" - also zusammen mit dem Rest der Abfrage - nicht getrennt voneinander, so wie es Postgres leider immer noch macht. In der Regel macht es bei SQL Server (oder Oracle) keinen Unterschied - ob ein Sub-Query als "derived table" oder CTE geschrieben wird.

Speziell in Deinem Bespiel sind die Optimizer von SQL Server und Oracle schlau genug die äussere Bedingung in die CTE zu "pushen" - da würde bei beiden Abfragen der gleiche Plan herauskommen (und ich glaube sogar MySQL 8.0 macht das auch "richtig").

 

[akretschmer]

soweit ich die Diskussion verfolgt habe, will die Community dies bei PG expliziet so lassen, aber es gibt die Überlegung, via Config-Variable auch eine Optimierung zuzulassen. Vielleicht in 12.

 

[ny_unity]

bedeutet, ein Umschreiben auf Subquerys würde keine Verbesserung der Zeit bringen?

 

[castorp]

bedeutet, ein Umschreiben auf Subquerys würde keine Verbesserung der Zeit bringen?

Mit hoher Warscheinlichkeit würde es keine Änderung bringen.
Aber das kannst Du leicht erkennen, wenn Du Dir die Execution Pläne beider Varianten anzeigen lässt.

 

[ny_unity]

Mit hoher Warscheinlichkeit würde es keine Änderung bringen.
Aber das kannst Du leicht erkennen, wenn Du Dir die Execution Pläne beider Varianten anzeigen lässt.

also am längsten scheint es beim Remote zu dauern. Kann ich dort eine Zeitraumsbeschränkung gleich mit schicken, damit nicht alle Werte abgefragt werden?

Also im OPENQUERY ein WHERE aus der parameter Abfrage.

Ist dies möglich?

 

[ukulele]

Ja du kannst im OPENEOWQUERY auf jedenfall einen WHERE-Teil einbauen. Die Geschwindigkeit hängt dann aber schon davon ab ob für die Spalten ein Index auf dem Remote-System vorhanden ist. Du "sparst" aber theoretisch schon dadurch Laufzeit das die Datenmenge die übers Netzwerk geschickt wird sinkt.

 

[akretschmer]

wird denn die Where-Condition an die entfernte Datenbank durchgereicht? Falls ja, dann ja ;-)

 

[ukulele]

Sagen wir mal so, ich nutze es und es funktioniert. Auch habe ich einen Zeitraum eingegrenzt und dadurch Perfomance gewonnen durch einen Index. Also ich denke schon das die Remote-DB da profitiert.

 

[ny_unity]

Sagen wir mal so, ich nutze es und es funktioniert. Auch habe ich einen Zeitraum eingegrenzt und dadurch Perfomance gewonnen durch einen Index. Also ich denke schon das die Remote-DB da profitiert.

kannst du kurz ein code-schnipsel zeigen? Ich bin mir noch nicht sicher an welcher Stelle das WHERE für die Remote-DB richtig gesetzt ist.

 

[ukulele]

SELECT t.*
    FROM   OPENQUERY([VerbindungsserverDB],'
   SELECT   m.Id,
           m.DatumVon,
           m.DatumBis,
           m.Nummer
   FROM   [Datenbankname].[Schema].[Tabelle] m
   WHERE   m.Nummer BETWEEN 10000 AND 79999') t
   LEFT JOIN [...]

Alles was im OPENQUERY steht geht 1:1 an die Remote DB. Das funktioniert genauso mit Where-Teil oder Join oder Case oder oder.

 

[ny_unity]

läuft, jetzt muss ichs im PHP noch ordentlich umsetzen... meine Abfrage ist:

Spoiler

WITH parameter AS
    (
    SELECT        CAST('01.01.'+CAST((DATEPART(YEAR, GETDATE())-3) AS VARCHAR(MAX)) AS DATE) AS startdatum, 
                DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST('01.01.'+CAST((DATEPART(YEAR, GETDATE())-3) AS VARCHAR(MAX)) AS DATE))) AS endedatum
    UNION ALL
    SELECT   dateadd(month,1,startdatum) AS startdatum, DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(dateadd(month,1,startdatum)),MONTH(dateadd(month,1,startdatum)), 1))) AS endedatum
    FROM   parameter
    WHERE   dateadd(month,1,startdatum) <= getdate()
    )
, umsatz AS
    (
    SELECT startdatum, endedatum, disponent, umsatz
    FROM OPENQUERY([L1_MAND3_RO],    '
                                    SELECT CASE WHEN SUM(r.dnetto) IS NULL THEN 0 ELSE SUM(r.dnetto) - sum(r.dbrutto * (r.skonto1PR/100)) END AS UMSATZ, 
                                            CASE WHEN k.disponent = '''' THEN ''XX'' 
                                                WHEN k.disponent in (''SC'', ''SL'', ''BJ'') THEN ''PFLEGE''
                                                WHEN k.disponent in (''TC'', ''GT'') THEN ''METALL'' ELSE k.disponent END AS disponent, 
                                            EXTRACT(YEAR FROM buchdatum) AS jahr, EXTRACT(MONTH FROM buchdatum) AS monat
                                    FROM rechnung r, kunden k
                                    WHERE r.kundennr = k.kundennr
                                    GROUP BY k.disponent, EXTRACT(YEAR FROM buchdatum), EXTRACT(MONTH FROM buchdatum)                   
                                    '
                    ), parameter
    WHERE DATEPART(YEAR, startdatum) = jahr AND DATEPART(MONTH,startdatum) = monat
    )
SELECT FORMAT(CAST(startdatum AS date), 'yyyy-MM') AS datum, disponent, SUM(umsatz) AS umsatz
FROM umsatz 
GROUP BY startdatum, disponent