sql server - Langsam ausgeführte SQL-Abfrage mit dreifachem Self-Join

Translate

Ich habe einErbeDatenbank mit folgender Tabelle (Hinweis: kein Primärschlüssel)

Es definiert jeweils einen Datensatz für jede "Einheit" und jedes Datum der Unterkunft sowie den Preis für dieses Datum.

CREATE TABLE [single_date_availability](
    [accommodation_id] [int],
    [accommodation_unit_id] [int],
    [arrival_date] [datetime],
    [price] [decimal](18, 0),
    [offer_discount] [decimal](18, 0),
    [num_pax] [int],
    [rooms_remaining] [int],
    [eta_available] [int],
    [date_correct] [datetime],
    [max_occupancy] [int],
    [max_adults] [int],
    [min_stay_nights] [int],
    [max_stay_nights] [int],
    [nights_remaining_count] [numeric](2, 0)
) ON [PRIMARY]

Die Tabelle enthält ungefähr 16.500 Datensätze.

Aber ich muss die Daten in einem völlig anderen Format multiplizieren, wie zum Beispiel:

  • Unterkunft
  • Datum
  • Dauer
  • Gesamtpreis

Bis zu einer maximalen Dauer für jedes Ankunftsdatum.

Ich verwende die folgende Abfrage, um dies zu erreichen:

SELECT
    MIN(units.MaxAccommodationAvailabilityPax) AS MaxAccommodationAvailabilityPax,
    MIN(units.MaxAccommodationAvailabilityAdults) AS MaxAccommodationAvailabilityAdults,
    StartDate AS DepartureDate,
    EndDate AS ReturnDate,
    DATEDIFF(DAY, StartDate, EndDate) AS Duration,
    MIN(units.accommodation_id) AS AccommodationID, 
    x.accommodation_unit_id AS AccommodationUnitID,
    SUM(Price) AS Price,
    MAX(num_pax) AS Occupancy,
    SUM(offer_discount) AS OfferSaving,
    MIN(date_correct) AS DateTimeCorrect,
    MIN(rooms_remaining) AS RoomsRemaining,
    MIN(CONVERT(int, dbo.IsGreaterThan(ISNULL(eta_available, 0)+ISNULL(nights_remaining_count, 0), 0))) AS EtaAvailable
FROM single_date_availability fp
INNER JOIN (
    /* This gets max availability for the whole accommodation on the arrival date */
    SELECT accommodation_id, arrival_date,
        CASE EtaAvailable WHEN 1 THEN 99 ELSE MaxAccommodationAvailabilityPax END AS MaxAccommodationAvailabilityPax,
        CASE EtaAvailable WHEN 1 THEN 99 ELSE MaxAccommodationAvailabilityAdults END AS MaxAccommodationAvailabilityAdults
    FROM (SELECT accommodation_id, arrival_date, SUM(MaximumOccupancy) MaxAccommodationAvailabilityPax, SUM(MaximumAdults) MaxAccommodationAvailabilityAdults,
            CONVERT(int, WebData.dbo.IsGreaterThan(SUM(EtaAvailable), -1)) AS EtaAvailable                 
            FROM (SELECT accommodation_id, arrival_date, MIN(rooms_remaining*max_occupancy) as MaximumOccupancy,
                    MIN(rooms_remaining*max_adults) as MaximumAdults, MIN(ISNULL(eta_available, 0) + ISNULL(nights_remaining_count, 0) - 1) as EtaAvailable
                    FROM single_date_availability
                    GROUP BY accommodation_id, accommodation_unit_id, arrival_date) a 
            GROUP BY accommodation_id, arrival_date) b
) units ON fp.accommodation_id = units.accommodation_id AND fp.arrival_date = units.arrival_date
INNER JOIN (
    /* This gets every combination of StartDate and EndDate for each Unit/Occupancy */
    SELECT DISTINCT a.accommodation_unit_id, StartDate = a.arrival_date,
        EndDate = b.arrival_date+1, Duration = DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1
        FROM single_date_availability AS a
        INNER JOIN (SELECT accommodation_unit_id, arrival_date FROM single_date_availability) AS b
        ON a.accommodation_unit_id = b.accommodation_unit_id
            AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 >= a.min_stay_nights
            AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 <= (CASE a.max_stay_nights WHEN 0 THEN 28 ELSE a.max_stay_nights END)
) x ON fp.accommodation_unit_id = x.accommodation_unit_id AND fp.arrival_date >= x.StartDate AND fp.arrival_date < x.EndDate
GROUP BY x.accommodation_unit_id, StartDate, EndDate
/* This ensures that all dates between StartDate and EndDate are actually available */
HAVING COUNT(*) = DATEDIFF(DAY, StartDate, EndDate)

Das funktioniert und gibt mir ungefähr 413.000 Datensätze. Die Ergebnisse dieser Abfrage verwende ich, um eine andere Tabelle zu aktualisieren.

Die Abfrage funktioniert jedoch ziemlich schlecht, wie Sie es bei so vielen Self-Joins erwarten können. Die lokale Ausführung dauert ungefähr 15 Sekunden, auf unserem Testserver jedoch mehr als 1:30 Minuten und auf unserem Live-SQL-Server mehr als 30 Sekunden. In allen Fällen wird die CPU maximal genutzt, während der größere der Joins ausgeführt wird.

Es greifen nicht gleichzeitig andere Prozesse auf die Tabelle zu, und das kann angenommen werden.

Die Länge der Abfrage stört mich nicht so sehr wie die Anforderungen an die CPU, was zu Problemen bei anderen Abfragen führen kann, die gleichzeitig versuchen, auf andere Datenbanken / Tabellen zuzugreifen.

Ich habe die Abfrage über das Abfrageoptimierungsprogramm ausgeführt und alle Empfehlungen für Indizes und Statistiken befolgt.

Jede Hilfe, um diese Abfrage schneller oder zumindest weniger CPU-intensiv zu machen, wäre sehr dankbar. Wenn es in verschiedene Phasen unterteilt werden muss, ist das akzeptabel.

Um ehrlich zu sein, ist Geschwindigkeit nicht so wichtig, da es sich um eine Massenoperation handelt, die an einem Tisch ausgeführt wird, der nicht von anderen Prozessen berührt wird.

Ich bin nicht besonders auf der Suche nach Kommentaren darüber, wie schrecklich und nicht normalisiert diese Struktur ist ... das weiß ich bereits :-)

This question and all comments follow the "Attribution Required."

Alle Antworten

Translate

Diese Seite ist für professionelle Programmierer, richtig.

Es ist schwierig zu versuchen, eine "Tabelle" ohne Primärschlüssel zu bearbeiten. Gut, es ist ein Arbeitsbereich, keine echte Tabelle (aber es ist groß und Sie versuchen, relationale Tabellenoperationen darauf auszuführen). Gut, Sie wissen, dass es nicht normalisiert ist. Tatsächlich ist die Datenbank nicht normalisiert, und diese "Tabelle" ist ein Produkt davon: ein exponentielles nicht normalisiertes Produkt.

Das funktioniert und gibt mir ungefähr 413.000 Datensätze. Die Ergebnisse dieser Abfrage verwende ich, um eine andere Tabelle zu aktualisieren.

Das ist noch verrückter. All diese (a) temporären Arbeitstische und (b) temporären Arbeitstische für das Geschäft mit temporären Arbeitstischen sind klassische Symptome einer nicht normalisierten Datenbank. ODER Unfähigkeit, die Daten so zu verstehen, wie sie sind, wie man die Daten herausholt und unnötige Arbeitstische erstellt, um Ihren Bedarf zu decken. Ich versuche nicht, Sie dazu zu bringen, das zu ändern, was das wäreerste Wahlund das würde die Notwendigkeit für dieses ganze Durcheinander beseitigen.

Daszweite OptionSehen Sie, ob Sie das Endergebnis aus den Originaltabellen erstellen können:
- ohne Arbeitstische
- mit einem Arbeitstisch
anstelle der beiden Arbeitstische (16.500 und 413.000 "Datensätze"; das sind zwei Ebenen exponentieller Unnormalisierung)

Dasdritte Optionist, verbessern Sie das Chaos, das Sie haben ... aber zuerst müssen Sie verstehen, wo die Leistungsschweine sind ...

Die Abfrage funktioniert jedoch ziemlich schlecht, wie Sie es bei so vielen Self-Joins erwarten können

Unsinn, Joins und Self-Joins kosten nichts. Die Probleme sind, die Kosten sind in:

  • Sie arbeiten auf einem Heap

  • ohne PK

    • Diese beiden Punkte allein bedeuten, dass die Leistung nicht berücksichtigt wurde und nicht erwartet werden kann
  • Die Verwendung von Operatoren und Funktionen (anstelle von reinem "=") in Verknüpfungen bedeutet, dass der Server keine vernünftigen Entscheidungen über die Suchwerte treffen kann, sodass Sie ständig Tabellen scannen

  • Tabellengröße (möglicherweise anders bei Dev / Test / Prod)

  • gültige, verwendbare Indizes (oder nicht)

  • Die Kosten sind in diesen vier Elementen enthalten, wobei die Haufen in jeder Hinsicht brutal langsam sind und die Betreiber nichts identifizieren, um die Suche einzugrenzen. nicht die Tatsache, dass es eine Join-Operation gibt oder nicht.

Dasnächste Serievon Problemen ist die Art und Weise, wie Sie es tun.

  • Ist Ihnen NICHT klar, dass die "Joins" materialisierte Tabellen sind? du bist nicht "beitritt" du materialisierst TABELLEN im laufenden Betrieb ??? Nichts ist kostenlos: Materialisierung hat enorme Kosten. Sie konzentrieren sich so darauf, ohne eine Vorstellung von den Kosten zu materialisieren, dass Sie denken, dass die Verknüpfungen das Problem sind. Warum ist das so ?

  • Bevor Sie vernünftige Codierungsentscheidungen treffen können, müssen Sie SHOWPLAN und STATISTICS IO ON einstellen. Tun Sie dies während der Entwicklung (es ist bei weitem nicht bereit zum "Testen"). Das gibt Ihnen eine Vorstellung von den Tabellen; die Verknüpfungen (was Sie erwarten und was es bestimmt, aus dem Durcheinander); die Arbeitstische (materialisiert). Die hohe CPU-Auslastung ist nichts. Warten Sie, bis Sie die verrückte E / A sehen, die Ihr Code verwendet. Wenn Sie über die Kosten für das Materialisieren im laufenden Betrieb streiten möchten, seien Sie mein Gast, aber veröffentlichen Sie zuerst das SHOWPLAN.

  • Notiere dass derMaterialisierte Tabellen haben keine Indizes, daher wird die Tabelle jedes Mal gescannt, für die um "verbindet".

  • Die Auswahl wie sie ist, erledigt zehnmal (vielleicht Hunderte) mehr Arbeit als nötig. Da der Tisch da ist und sich nicht bewegt hat, ist es sehr dumm, eine andere Version davon zu materialisieren. Die wahre Frage ist also:

Wie kommt es, dass meine SQL-Abfrage mit einer Tabelle und sechs materialisierten Versionen von sich selbst langsam ist?

.
Wenn Sie sich nicht sicher sind, bedeutet dieseliminiere die sechs materialisierten Tabellenund ersetzen Sie sie durch reine Verknüpfungen zur Haupttabelle.

  • Wenn Sie es akzeptieren können, es aufzubrechen, dann tun Sie dies. Erstellen und laden Sie temporäre Tabellen, für die diese Abfrage FIRST verwenden soll (dh 3 temporäre Tabellen nur für Aggregate). Stellen Sie sicher, dass Sie Indizes in den richtigen Spalten platzieren.

  • Die 6 materialisierten Tabellen müssen also durch 3 Verknüpfungen zur Haupttabelle und 3 Verknüpfungen zu temporären Aggregattabellen ersetzt werden.

  • Irgendwann haben Sie festgestellt, dass Sie kartesische Produkte und Duplikate haben. Anstatt die Ursache zu beheben (Code zu entwickeln, der den von Ihnen benötigten Satz erzeugt), haben Sie all das vermieden, ihn voller Dupes belassen und die DISTINCT-Zeilen herausgezogen. Das verursacht einen zusätzlichen Arbeitstisch. Repariere das. Sie müssen jede der temporären Tabellen (Arbeitstabellen, materialisierte Tabellen, was auch immer) ZUERST korrekt machen, bevor die Auswahl, die sie verwendet, vernünftigerweise als korrekt angesehen werden kann.

  • DANN versuchen Sie die Auswahl.

  • Ich gehe davon aus, dass dies alles in WebData läuft. Wenn nicht, platzieren Sie IsGreaterThan () in dieser Datenbank.


  1. Bitte geben Sie DDL für UDF IsGreaterThan an. Wenn das Tabellen verwendet, müssen wir darüber Bescheid wissen.

  2. Bitte geben Sie die angeblichen Indizes mit der Anweisung CREATE TABLE an. Sie könnten falsch oder schlimmer sein, verdoppelt und nicht erforderlich.

  3. Vergessen Sie die Identität oder erzwungene Werte. Was ist die tatsächliche, reale, natürliche, logische PK für diesen Haufen eines Arbeitstisches?

  4. Stellen Sie sicher, dass die Verknüpfungsspalten keine Datentypinkongruenzen aufweisen

  5. Persönlich würde ich mich zu schämen, um eine Postleitzahl wie Sie zu posten. Es ist völlig unlesbar. Alles, was ich getan habe, um die Probleme hier zu identifizieren, ist es zu formatieren und lesbar zu machen. Es gibt Gründe, Code lesbar zu machen, z. B. können Sie Probleme schnell erkennen. Es spielt keine Rolle, welche Formatierung Sie verwenden, aber Sie müssen formatieren und dies konsequent tun. Bitte bereinigen Sie es, bevor Sie erneut posten, zusammen mit ALLEN zugehörigen DDL.

Es ist kein Wunder, dass Sie keine Antworten erhalten haben. Sie müssen zuerst einige grundlegende Arbeiten ausführen (Showplan usw.) und den Code vorbereiten, damit die Menschen ihn lesen und Antworten geben können.

SELECT
        MIN(units.MaxAccommodationAvailabilityPax) AS MaxAccommodationAvailabilityPax,
        MIN(units.MaxAccommodationAvailabilityAdults) AS MaxAccommodationAvailabilityAdults,
        StartDate AS DepartureDate,
        EndDate AS ReturnDate,
        DATEDIFF(DAY, StartDate, EndDate) AS Duration,
        MIN(units.accommodation_id) AS AccommodationID, 
        x.accommodation_unit_id AS AccommodationUnitID,
        SUM(Price) AS Price,
        MAX(num_pax) AS Occupancy,
        SUM(offer_discount) AS OfferSaving,
        MIN(date_correct) AS DateTimeCorrect,
        MIN(rooms_remaining) AS RoomsRemaining,
        MIN(CONVERT(int, dbo.IsGreaterThan(ISNULL(eta_available, 0)+ISNULL(nights_remaining_count, 0), 0))) 
            AS EtaAvailable
    FROM single_date_availability fp INNER JOIN (
        -- This gets max availability for the whole accommodation on the arrival date
        SELECT  accommodation_id, arrival_date,
                CASE EtaAvailable 
                    WHEN 1 THEN 99
                    ELSE MaxAccommodationAvailabilityPax 
                    END AS MaxAccommodationAvailabilityPax,
                CASE EtaAvailable
                    WHEN 1 THEN 99
                    ELSE MaxAccommodationAvailabilityAdults
                    END AS MaxAccommodationAvailabilityAdults
            FROM ( 
                SELECT  accommodation_id, arrival_date,
                        SUM(MaximumOccupancy) 
                        MaxAccommodationAvailabilityPax,
                        SUM(MaximumAdults) MaxAccommodationAvailabilityAdults,
                        CONVERT(int, WebData.dbo.IsGreaterThan(SUM(EtaAvailable), -1))
                            AS EtaAvailable                 
                    FROM ( 
                        SELECT  accommodation_id,
                                arrival_date,
                                MIN(rooms_remaining*max_occupancy) as MaximumOccupancy,
                                MIN(rooms_remaining*max_adults) as MaximumAdults, 
                                MIN(ISNULL(eta_available, 0) + ISNULL(nights_remaining_count, 0) - 1)
                                    as EtaAvailable
                            FROM single_date_availability
                            GROUP BY accommodation_id, accommodation_unit_id, arrival_date
                            ) a 
                    GROUP BY accommodation_id, arrival_date
                    ) b
            ) units 
        ON fp.accommodation_id = units.accommodation_id 
        AND fp.arrival_date = units.arrival_date INNER JOIN (
            -- This gets every combination of StartDate and EndDate for each Unit/Occupancy
            SELECT  D.I.S.T.I.N.C.T a.accommodation_unit_id,
                    StartDate = a.arrival_date,
                    EndDate = b.arrival_date+1,
                    Duration = DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1
                FROM single_date_availability AS a INNER JOIN ( 
                    SELECT  accommodation_unit_id,
                            arrival_date 
                        FROM single_date_availability
                        ) AS b
                ON a.accommodation_unit_id = b.accommodation_unit_id
                AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 >= a.min_stay_nights
                AND DATEDIFF(DAY, a.arrival_date, b.arrival_date)+1 <= (
                    CASE a.max_stay_nights 
                        WHEN 0 THEN 28 
                        ELSE a.max_stay_nights 
                        END
                )
        ) x ON fp.accommodation_unit_id = x.accommodation_unit_id 
        AND fp.arrival_date >= x.StartDate 
        AND fp.arrival_date < x.EndDate
    GROUP BY x.accommodation_unit_id, StartDate, EndDate
    -- This ensures that all dates between StartDate and EndDate are actually available
    HAVING COUNT(*) = DATEDIFF(DAY, StartDate, EndDate)

Quelle
Translate

Dies wird höchstwahrscheinlich nicht alle Ihre Probleme beheben, aber versuchen Sie es mit einem Wechsel

AND DATEDIFF(DAY , a.arrival_date , b.arrival_date) + 1 >= a.min_stay_nights
AND DATEDIFF(DAY , a.arrival_date , b.arrival_date) + 1 <= (CASE a.max_stay_nights WHEN 0 THEN 28 ELSE a.max_stay_nights END)

to

and a.min_stay_nights<=DATEDIFF(DAY , a.arrival_date , b.arrival_date)
and (CASE a.max_stay_nights WHEN 0 THEN 28 ELSE a.max_stay_nights END)>=DATEDIFF(DAY , a.arrival_date , b.arrival_date) + 1

Der Grund dafür ist, dass SQL Server, soweit ich mich erinnern kann, keine Funktionen auf der linken Seite der Klauseln = sign in where-Klauseln mag

Quelle
Translate

Da Sie sagten, Sie haben das Abfrageoptimierungsprogramm bereits ausgeführt, kann ich nur davon ausgehen, dass alle Ihre Indizes korrekt sind. Mein nächster Ansatz besteht darin, den Join in der Anwendung durchzuführen. Was meine ich damit? Anstatt DB die Verknüpfungen von 100.000 Zeilen ausführen zu lassen. Rufen Sie alle einmal in Ihrer Anwendung ab und dann Schleifen und Logik, um das zu tun, was Sie stattdessen in SQL getan hätten.

Grund dafür ist, dass viele zB Anwendungen wie Facebook, Yahoo, Aol Stirnrunzeln bei Joins. Joins sind nicht das Beste, es sei denn, Sie wissen, dass es schnell gehen wird. In diesem Fall möchten Sie die Join-In-Anwendung für zukünftige Anforderungen zwischenspeichern.

Quelle
Über den Autor