none
Performance bei Abfragen RRS feed

  • Frage

  • Guten Tag,

    ich suche eine möglichst perfomante Abfrage, die Kosten in Abhängigkeit der Tage auf Jahre umrechnet.

    Was ist günstiger? Erst ein Jahresvergleich, und nur bei nicht übereinstimmenden Jahren vvon / vbis Berechnungen per UNION hinzufügen? Oder gleich alle Datensätze berechnen? Oder viellleicht eine ganz andere Lösung?

    Danke schon mal.

    IF OBJECT_ID('tempdb..#zahlungen') IS NOT NULL
    BEGIN
        DROP TABLE #zahlungen;
    END
    GO
    
    
    CREATE TABLE #zahlungen(
    Typ_ID INT,
    vvon DATETIME,
    vbis DATETIME,
    hifanr INT,
    Betrag money
    );
    GO 
    
    INSERT INTO #zahlungen VALUES (1,'2008-19-12','2009-04-01',203, 509.20);
    INSERT INTO #zahlungen VALUES (2,'2009-02-01','2009-16-01',203, 12.60);
    INSERT INTO #zahlungen VALUES (3,'2009-12-01','2009-05-04',203, 45.38);
    INSERT INTO #zahlungen VALUES (4,'2009-03-01','2009-04-02',222, 20.58);
    INSERT INTO #zahlungen VALUES (5,'2009-07-01','2009-09-01',222, 100);
    INSERT INTO #zahlungen VALUES (6,'2002-20-01','2009-25-01',222, 5258.55);
    INSERT INTO #zahlungen VALUES (7,'2009-01-01','2009-31-01',222, 456.22);
    INSERT INTO #zahlungen VALUES (8,'2009-01-01','2009-19-02',222, 456.21);
    INSERT INTO #zahlungen VALUES (9,'2008-22-05','2008-19-02',203, 11.11);
    INSERT INTO #zahlungen VALUES (10,'2008-20-07','2009-25-01',222, 44.11);
    INSERT INTO #zahlungen VALUES (11,'2009-12-01','2009-31-01',222, 0.05);
    INSERT INTO #zahlungen VALUES (12,'2009-12-02','2009-19-02',222, 45.23);
    INSERT INTO #zahlungen VALUES (13,'2003-14-05','2006-30-06',203, 45.25);
    INSERT INTO #zahlungen VALUES (14,'2004-19-05','2006-22-05',203, 41.11);
    
    
    DECLARE @DateMax DATE, @DateMin DATE ;
      
    SELECT @DateMax = MAX(vbis), 
        @DateMin = MIN(vvon) 
    FROM  #zahlungen ;  
    
    WITH 
     Calendar AS 
     (
      SELECT @DateMin AS DateDay
      UNION ALL
      SELECT DATEADD(DAY, 1, DateDay)
      FROM  Calendar
      WHERE DateDay < @DateMax
     )
    
    SELECT T.Typ_ID, 
        T.hifanr,
        Year (C.DateDay)AS jahr,
        DATEDIFF (DD, vvon, vbis) + 1 AS [Zeit_gesamt],
        DATEDIFF (DD, vvon, vbis) + 1 AS [Zeit_anteil],
        betrag AS [Ausgaben_gesamt],
        betrag AS [Ausgaben_anteil]
    FROM  Calendar C
        INNER JOIN #zahlungen T ON C.DateDay BETWEEN T.vvon AND T.vbis
    WHERE Year (C.DateDay) = 2009
        AND YEAR (vvon) = Year (vbis)
    GROUP BY T.Typ_ID, 
             T.hifanr,
             Year (C.DateDay),
             vvon,
             vbis,
             Betrag
    
    UNION
    
    SELECT T.Typ_ID, 
        T.hifanr,
        Year (C.DateDay)AS jahr,
        DATEDIFF (DD, vvon, vbis) + 1 AS [Zeit_gesamt],
        COUNT(C.DateDay) AS [Zeit_anteil],
        betrag AS [Ausgaben_gesamt],
        betrag / (DATEDIFF (DD, vvon, vbis) + 1) * (COUNT(C.DateDay)) AS [Ausgaben_anteil]
    FROM  Calendar C
        INNER JOIN #zahlungen T ON C.DateDay BETWEEN T.vvon AND T.vbis
    WHERE Year (C.DateDay) = 2009
        AND YEAR (vvon) != Year (vbis)
    GROUP BY T.Typ_ID, 
             T.hifanr,
             Year (C.DateDay),
             vvon,
             vbis,
             Betrag
    ORDER BY T.Typ_ID,
            jahr
    
    OPTION ( MAXRECURSION 0 )
    ;
    
    

     

    Montag, 24. Oktober 2011 19:07

Antworten

  • Das größteProblem dürfte bei der Verwendung von YEAR() liegen, da es hier immer zu Operatoren kommt, welche keinen Index nutzen können, falls vorhanden (non-sargable conditions). Z.B. kann WHERE YEAR(C.DateDay) = 2009 im Gegensatz zu WHERE C.DateDay >= '20090101' AND C.DateDay < '20100101' keinen Index nutzen.

    Ich würde erstmal mit einer materialisierten Kalendartabelle beginnen:

    CREATE TABLE CalendarTally 
    (
      DateDay DATETIME NOT NULL,
      MonthDay INT NOT NULL,
      YearDay INT NOT NULL 
    ) ;
    
    ALTER TABLE CalendarTally
    	  ADD CONSTRAINT PK_CalendarTally PRIMARY KEY CLUSTERED (DateDay) WITH FILLFACTOR = 100;   
    
    WITH Calendar AS 
    (
      SELECT CAST('20010101' AS DATETIME) AS DateDay
      UNION ALL
      SELECT DATEADD(DAY, 1, DateDay)
      FROM   Calendar
      WHERE  DateDay < '20371231'
    )
    INSERT INTO CalendarTally 
    SELECT DateDay , 
           MONTH(DateDay) AS MonthDay ,
           YEAR(DateDay) AS YearDay 
    FROM   Calendar 
    OPTION ( MAXRECURSION 0 ) ;
    
    

    Ansonsten sollte ein kombinierter Index über (vvon, vbis) nützlich sein. Gegebenfalls mit entsprechenden INCLUDE Spalten, bzw. ein entsprechender CLUSTERED INDEX.

    Wichtig wäre auch zu wissen, wieviele Datensätz in deiner Zahlungstabelle vorhanden sind.

     

    • Als Antwort vorgeschlagen Falk Krahl Montag, 31. Oktober 2011 20:11
    • Als Antwort markiert Joerg_x Donnerstag, 3. November 2011 19:11
    Montag, 31. Oktober 2011 12:54
    Moderator
  • Rein von der Berechnung her sind bei ~500K Rows eigentlich keine solchen Probleme zu erwarten. Um hier zu Optimieren braucht man schon mehr und tiefer Einsicht in die Daten und die Speicherstruktur.

    Kannst du die hier angewandte Berechnung mal sauber in Worten formulieren?

    In einer CTE kannst du nicht mit zusätzlichen Indices arbeiten, allerdings kann diese entsprechend vorhandene nutzen. Eine andere Möglichkeit ist die  Nutzung einer temporären Tabelle. Hier kannst du zusätzliche Indices definieren.

    Für den Anfang kannst du den EPA aus dem SSMS Tool Pack nutzen, dieser gibt dir ungefähre Informationen, woran es liegen kann.

    Du kannst auch den Database Tuning Advisor nutzen (lies die Englischen Seiten, die Übersetzungen sind naja).

    • Als Antwort markiert Joerg_x Donnerstag, 3. November 2011 19:11
    Mittwoch, 2. November 2011 18:29
    Moderator

Alle Antworten

  • Das größteProblem dürfte bei der Verwendung von YEAR() liegen, da es hier immer zu Operatoren kommt, welche keinen Index nutzen können, falls vorhanden (non-sargable conditions). Z.B. kann WHERE YEAR(C.DateDay) = 2009 im Gegensatz zu WHERE C.DateDay >= '20090101' AND C.DateDay < '20100101' keinen Index nutzen.

    Ich würde erstmal mit einer materialisierten Kalendartabelle beginnen:

    CREATE TABLE CalendarTally 
    (
      DateDay DATETIME NOT NULL,
      MonthDay INT NOT NULL,
      YearDay INT NOT NULL 
    ) ;
    
    ALTER TABLE CalendarTally
    	  ADD CONSTRAINT PK_CalendarTally PRIMARY KEY CLUSTERED (DateDay) WITH FILLFACTOR = 100;   
    
    WITH Calendar AS 
    (
      SELECT CAST('20010101' AS DATETIME) AS DateDay
      UNION ALL
      SELECT DATEADD(DAY, 1, DateDay)
      FROM   Calendar
      WHERE  DateDay < '20371231'
    )
    INSERT INTO CalendarTally 
    SELECT DateDay , 
           MONTH(DateDay) AS MonthDay ,
           YEAR(DateDay) AS YearDay 
    FROM   Calendar 
    OPTION ( MAXRECURSION 0 ) ;
    
    

    Ansonsten sollte ein kombinierter Index über (vvon, vbis) nützlich sein. Gegebenfalls mit entsprechenden INCLUDE Spalten, bzw. ein entsprechender CLUSTERED INDEX.

    Wichtig wäre auch zu wissen, wieviele Datensätz in deiner Zahlungstabelle vorhanden sind.

     

    • Als Antwort vorgeschlagen Falk Krahl Montag, 31. Oktober 2011 20:11
    • Als Antwort markiert Joerg_x Donnerstag, 3. November 2011 19:11
    Montag, 31. Oktober 2011 12:54
    Moderator
  • Das größteProblem dürfte bei der Verwendung von YEAR() liegen, da es hier immer zu Operatoren kommt, welche keinen Index nutzen können, falls vorhanden (non-sargable conditions). Z.B. kann WHERE YEAR(C.DateDay) = 2009 im Gegensatz zu WHERE C.DateDay >= '20090101' AND C.DateDay < '20100101' keinen Index nutzen.

    Ich würde erstmal mit einer materialisierten Kalendartabelle beginnen:

     

    CREATE TABLE CalendarTally 
    (
      DateDay DATETIME NOT NULL,
      MonthDay INT NOT NULL,
      YearDay INT NOT NULL 
    ) ;
    
    ALTER TABLE CalendarTally
    	  ADD CONSTRAINT PK_CalendarTally PRIMARY KEY CLUSTERED (DateDay) WITH FILLFACTOR = 100;   
    
    WITH Calendar AS 
    (
      SELECT CAST('20010101' AS DATETIME) AS DateDay
      UNION ALL
      SELECT DATEADD(DAY, 1, DateDay)
      FROM   Calendar
      WHERE  DateDay < '20371231'
    )
    INSERT INTO CalendarTally 
    SELECT DateDay , 
           MONTH(DateDay) AS MonthDay ,
           YEAR(DateDay) AS YearDay 
    FROM   Calendar 
    OPTION ( MAXRECURSION 0 ) ;
    
    

     

    Ansonsten sollte ein kombinierter Index über (vvon, vbis) nützlich sein. Gegebenfalls mit entsprechenden INCLUDE Spalten, bzw. ein entsprechender CLUSTERED INDEX.

    Wichtig wäre auch zu wissen, wieviele Datensätz in deiner Zahlungstabelle vorhanden sind.

     


    Hallo Stefan,

    wir reden so über ~ 500.000 Datensätze; ohne Index gibt es überhaupt keine vertretbaren Antwortzeiten, weil noch einige weitere Berechnungen auf der Grundlage dieser Ergebnisse erfolgen (> 5 Minuten). Und davon verteilen sich nur < 500 DS auf mehr als 1 Jahr; daher meine Frage, ob es nicht besser ist, die Abfrage mit UNION auseinderzuziehen, weil Kosten ja nur bei den ~ 500 DS aufzuteilen sind.

    Innerhalb einer CTE kann man wohl keinen Index setzen, oder?

    Kannst Du mir einen Link nennen, der erläutert, wie man die angezeigten Ausführungspläne in Bezug auf die erwartenden Laufzeiten interpretieren muss und worauf man besonders zu achten hat?

    Danke und schöne Grüsse.


    • Bearbeitet Joerg_x Mittwoch, 2. November 2011 18:01
    Mittwoch, 2. November 2011 17:59
  • Rein von der Berechnung her sind bei ~500K Rows eigentlich keine solchen Probleme zu erwarten. Um hier zu Optimieren braucht man schon mehr und tiefer Einsicht in die Daten und die Speicherstruktur.

    Kannst du die hier angewandte Berechnung mal sauber in Worten formulieren?

    In einer CTE kannst du nicht mit zusätzlichen Indices arbeiten, allerdings kann diese entsprechend vorhandene nutzen. Eine andere Möglichkeit ist die  Nutzung einer temporären Tabelle. Hier kannst du zusätzliche Indices definieren.

    Für den Anfang kannst du den EPA aus dem SSMS Tool Pack nutzen, dieser gibt dir ungefähre Informationen, woran es liegen kann.

    Du kannst auch den Database Tuning Advisor nutzen (lies die Englischen Seiten, die Übersetzungen sind naja).

    • Als Antwort markiert Joerg_x Donnerstag, 3. November 2011 19:11
    Mittwoch, 2. November 2011 18:29
    Moderator
  • Rein von der Berechnung her sind bei ~500K Rows eigentlich keine solchen Probleme zu erwarten. Um hier zu Optimieren braucht man schon mehr und tiefer Einsicht in die Daten und die Speicherstruktur.

    Kannst du die hier angewandte Berechnung mal sauber in Worten formulieren?

    In einer CTE kannst du nicht mit zusätzlichen Indices arbeiten, allerdings kann diese entsprechend vorhandene nutzen. Eine andere Möglichkeit ist die  Nutzung einer temporären Tabelle. Hier kannst du zusätzliche Indices definieren.

    Für den Anfang kannst du den EPA aus dem SSMS Tool Pack nutzen, dieser gibt dir ungefähre Informationen, woran es liegen kann.

    Du kannst auch den Database Tuning Advisor nutzen (lies die Englischen Seiten, die Übersetzungen sind naja).


    Hallo Stefan,

    das mit der tieferen Einsicht ist ein Problem. Die darf ich leider nicht geben.

    Eine Beschreibung der gesamten angewandten Berechnungen ohne Kenntnis der Datenstruktur wird vermutlich auch nicht zielführend sein.

    Den genannten Database Tuning Advisor als Lösungsvorschlag kann ich so ebenfalls nicht anwenden; sollte danach ein Problem auftreten, wird man das kaum rekonstruieren können, was alles geändert wurde, noch wird sich hiervon der Support verständlicherweise etwas annehmen.

    Somit bleibt nur der EPA aus den SMSS. Nun habe ich aber zu wenig Ahnung, wie man den zu interpretieren hat, um den Flaschenhals zu lokalisieren. Gibt es da irgendwo Erläuterungen, die Du empfehlen kannst?

    Weil Du meine Ausgangsfrage bereits beantwortet hast, markiere ich das Thema als erledigt.

    Schöne Grüsse.

    Donnerstag, 3. November 2011 19:09
  • Also Ausgangspunkt: http://lmgtfy.com/?q=sql+server+reading+execution+plan

    Donnerstag, 3. November 2011 20:14
    Moderator