none
ODBC-Timeout bei verknüpfter Abfrage aus SQL Server RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo,

    ich habe eine  Access 2013 Datenbank mit einer als Tabelle eingebundenen Abfrage aus SQL Server 2008 R2.
    Die Abfrage hat mit 4 bis 5 Minuten eine recht hohe Laufzeit, die wir  auch nicht weiter reduzieren können.
    Diese Laufzeit führt in der Access datenbank ja nun zu einem Abfrage Timeout ich habe deshalb in den Client Einstrellungen von Access das ODBC Timeout höher bzw. auf 0 gestellt, was aber leider nicht zum Erfolg führte.

    Hat jemand eine Idee, wie wir das Problem in den Griff bekommen können?

    Gruß

    cheapy

    Montag, 13. November 2017 13:41
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    Bei einer Aggregierung von 100.000den Sätzen kann es schon mal dauern, ebenso wenn bei einer ungünstigen Abfrage keine Indizes verwendbar sind.
    Man bedenke, dass je nach Satzlänge und Tabellengröße zwischen 20.000 und 50.000 Sätze je Sekunde verarbeitet werden könnten. Im Maximum wären das bei 1 Minute also ca. 3 Mio, bei 5 Minuten also ca. 15 Mio!

    Bist du sicher, dass du mit der Abfrage also ca. 10-15 Millionen Sätze aggregierst?

    Aber wenn die Abfrage auf dem Server liegt kannst du diese doch selber wiederum als Passthru-SQL abfragen an Stelle eine verknüpfte Tabelle zu verwenden.
    Der Access-Anwendung ist es letztelich egal ob eine verknüpfte Tabelle oder SQL-Passthru verwendet wird.
    Schließlich muss eine Abfrage ja nicht geändert werden also auch nicht als Tabelle vorliegen.

    • Als Antwort markiert Cheaptrick_la Dienstag, 14. November 2017 13:29
    • Tag als Antwort aufgehoben Cheaptrick_la Dienstag, 14. November 2017 13:40
    • Als Antwort markiert Cheaptrick_la Dienstag, 14. November 2017 13:40
    Montag, 13. November 2017 18:13
    |

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Wo hast du das Timeout genau eingestellt?
    Normalerweise wird das auf den Eigenschaften der Abfrage selber individuell festgelegt (Default 60).
    Dazu muss allerdings die Abfrage als "SQL-Passthru" definiert werden.

    Montag, 13. November 2017 16:21
    |
  • Question
    Anmelden
    0
    Anmelden
    ... mit einer als Tabelle eingebundenen Abfrage aus SQL Server 2008 R2. ...

    Wie ich geschrieben habe, liegt die Abfrage auf dem SQL Server, also nicht in Access und auch keine Pass Through.

    Daher ist in den Eigenschaften auch nichts über ODBC Timeout einzustellen.
    Ich habe mich jetzt erstmal des Regedit bedient und die Einstellug allgemein für Access vorgenommen.
    Da diese Datenbank aber von mehrerne Usern sowohl lokal als auch über Terminal Server genutzt wird, ist das keine wirkliche Lösung. Und an der langen Laufzeit der Abfrage ist nichts mehr zu machen. Eigentlich sit auch die nicht besonders umfangreich, greift aber auf eine Measure des Data Warehouse der SQl Server-DB zu. Da scheint mir das eigentliche Laufzeitproblem zu liegen.

    Montag, 13. November 2017 17:30
    |
  • Question
    Anmelden
    0
    Anmelden

    Bei einer Aggregierung von 100.000den Sätzen kann es schon mal dauern, ebenso wenn bei einer ungünstigen Abfrage keine Indizes verwendbar sind.
    Man bedenke, dass je nach Satzlänge und Tabellengröße zwischen 20.000 und 50.000 Sätze je Sekunde verarbeitet werden könnten. Im Maximum wären das bei 1 Minute also ca. 3 Mio, bei 5 Minuten also ca. 15 Mio!

    Bist du sicher, dass du mit der Abfrage also ca. 10-15 Millionen Sätze aggregierst?

    Aber wenn die Abfrage auf dem Server liegt kannst du diese doch selber wiederum als Passthru-SQL abfragen an Stelle eine verknüpfte Tabelle zu verwenden.
    Der Access-Anwendung ist es letztelich egal ob eine verknüpfte Tabelle oder SQL-Passthru verwendet wird.
    Schließlich muss eine Abfrage ja nicht geändert werden also auch nicht als Tabelle vorliegen.

    • Als Antwort markiert Cheaptrick_la Dienstag, 14. November 2017 13:29
    • Tag als Antwort aufgehoben Cheaptrick_la Dienstag, 14. November 2017 13:40
    • Als Antwort markiert Cheaptrick_la Dienstag, 14. November 2017 13:40
    Montag, 13. November 2017 18:13
    |
  • Question
    Anmelden
    0
    Anmelden

    Bist du sicher, dass du mit der Abfrage also ca. 10-15 Millionen Sätze aggregierst?

    Wie ich schon geschrieben habe liegt das Problem der Dauen verutlich nicht an der menge der Datensätze, sondern am Zugriff auf die Measure im Data Warehouse.

    Die Sache mit der PT anstelle des direkten Auslesens werde ich mal testen.

    Dienstag, 14. November 2017 06:42
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo,

    ich habe den Vorshclag mit der PT geprüft und es funktinier.

    Danke

    Dienstag, 14. November 2017 13:40
    |
  • Question
    Anmelden
    0
    Anmelden

    Nun bereitet mir das ganze Abfragekonstrukt auf dem SQL Server aber Kofzerbrechen.
    Die Geasmte Abfrage sieht so aus:

    WITH X1 AS
(
    SELECT     Kunde, Artikel, SUM([Umsatz]) AS [GesamtUmsatz], Kalenderjahr
    FROM          [UmsatzDetails]
    GROUP BY Kunde,Artikel, Kalenderjahr
)
SELECT TOP (10) X1_1.Kunde, X1_1.Artikel, X1_1.[GesamtUmsatz]
FROM  X1 AS X1_1 INNER JOIN [Geschäftsjahr] AS X2_1 
	ON X1_1.Kalenderjahr = X2_1.Kalenderjahr
ORDER BY X1_1.[GesamtUmsatz] DESC

    Die View 

     [UmsatzDetails]

    betsteht aus 3 Abfragen, die per Union All zusammengefasst werden:
    Das Gesamtergebnis liefert etwas 2.400.000 Datensätze.
    Durch einen weiteren Filter reduziere ich dei Datenmenge auf 80.000 Datensätze.
    Die Laufzeit für diesen Part liegt bei weniger als 20 Sekunden.

    Diese Abfrage

    SELECT     Kunde, Artikel, SUM([Umsatz]) AS [GesamtUmsatz], Kalenderjahr
FROM       [UmsatzDetails]
GROUP BY Kunde,Artikel, Kalenderjahr

    liefert etwa 800 Datensätze und die Laufzeit liegt immer noch unter einer Minute.

    Reduziere ich die gesamte Abfrage auf folgendes

    WITH X1 AS
(
    SELECT     Kunde, Artikel, SUM([Umsatz]) AS [GesamtUmsatz], Kalenderjahr
    FROM          [UmsatzDetails]
    GROUP BY Kunde,Artikel, Kalenderjahr
)
SELECT X1_1.Kunde, X1_1.Artikel, X1_1.[GesamtUmsatz]
FROM  X1
ORDER BY X1_1.[GesamtUmsatz] DESC

    Benötigt immer noch weniger als eine Minute.
    Sobald ich das Kalenderjahr als Filter benutze (integer-Wert) steigt die Laufuzeit exorbitant an.
    Wie kann es sein, dass ein Filter, der auf nicht einmal 800 Datensätze wirkt, die Laufzeit so dramatisch ansteigen lässt?

    Ein weiteres Experiment lässt mich nun völlig staunen.
    WITH X1 AS
(
    SELECT     Kunde, Artikel, SUM([Umsatz]) AS [GesamtUmsatz], Kalenderjahr
    FROM          [UmsatzDetails]
    GROUP BY Kunde,Artikel, Kalenderjahr
)
SELECT TOP (10) X1_1.Kunde, X1_1.Artikel, X1_1.[GesamtUmsatz]
FROM  X1 AS X1_1 RIGHT OUTER JOIN [Geschäftsjahr] AS X2_1 
	ON X1_1.Kalenderjahr = X2_1.Kalenderjahr
ORDER BY X1_1.[GesamtUmsatz] DESC

    Ändere ich den Inner Join nach Right Outer Join, liegt die Laufzeit des gesamten Konstrukts wieder unter 20 Sekunden.
    Was kann die Ursache sein?
    Ein Fehler im Execution Plan?
    Dienstag, 14. November 2017 13:48
    |
  • Question
    Anmelden
    0
    Anmelden

    Nein, nicht mal Plan, sondern an der Art der Kombinationen.

    Der SQL-Optimizer baut die Abfragen intern durchaus um und steuert die Zugriffe auf ganz andere Weise.
    Was eben nicht passiert ist das tatsächliche Bilden von temporären Tabellen, sondern alles passiert "on the fly".

    Den Excution-Plan kann man sich auch anzeigen lassen.

    Nun zum Join-Typ:

    Left Join heißt, alle Daten von links, alle passenden Daten von rechts, falls vorhanden.
    Right Join heißt, alle Daten von rechts, alle passenden Daten von links, falls vorhanden.

    Die Betonung liegt nun mal auf "falls vorhanden".
    Die Reihenfolge der Bearbeitung ist also zuerst die Ermittlung aller Geschäftsjahre und anschließend die Ermittlung der Gruppenwerte für jedes Geschäftsjahr, also auch für die Jahre, wo gar keine Umsätze vorhanden sind.

    Aus der Geschäftsjahrtabelle benötigst du aber selber überhaupt keine anderen Werte, daher erklärt sich mir der Zweck dieses Joins nicht.
    Sollte er nur ein Filter sein wie du sagst, da du ggf. mehr Umsatzjahre als Geschäftsjahre hast, dann solltest du dies in der Where-Klausel auch angeben:

    select ...
    from UmsatzDetails u
    where exists (select * from Geschäftsjahr g where g.kalenderjahr = u.kalenderjahr)
    group ...

    Des weiteren solltest du prüfen, ob du einen Index über Kunde, Artikel, Kalenderjahr hast, wobei durchaus das Kalenderjahr auch vorne stehen könnte.

    Dienstag, 14. November 2017 14:49
    |
  • Question
    Anmelden
    0
    Anmelden

    Nun zum Join-Typ:

    Left Join heißt, alle Daten von links, alle passenden Daten von rechts, falls vorhanden.
    Right Join heißt, alle Daten von rechts, alle passenden Daten von links, falls vorhanden.

    Aus der Geschäftsjahrtabelle benötigst du aber selber überhaupt keine anderen Werte, daher erklärt sich mir der Zweck dieses Joins nicht.
    Sollte er nur ein Filter sein wie du sagst, da du ggf. mehr Umsatzjahre als Geschäftsjahre hast, dann solltest du dies in der Where-Klausel auch angeben:


    Die Bedeutung des Join Typs ist mir schon klar, deshalb hatte ich ja ursprünglich einen INNER JOIN verwendet, weil die Tabelle [Geschaeftsjahr] nur einen Datensatz enthält und somit als Filter für die Daten aus der Abfrage dient. Versuchsweise hatte ich im INNER JOIN auch diese Tabelle weg gelassen und das Geschäftsjahr direkt eingetragen, ebenso, wie ich versuchsweise eine WHERE Klausel anstatt INNER JOIN probiert habe und ja, sämtliche benötigten Indizes wurden angelegt. Jedenfsalls die, die mir bei der Anzeige des Ausführungsplans angezeigt wurden. In allen Fällen lag die Ausführungszeit zwischen 3,5 und 4 Minuten bei Verwendung des RIGHT OUTER JOIN liegt die Dauer deutlich unter 20 Sekunden!
    Da der RIGHT OUTER JOIN ja erstmal mehr Datensätze erzeugt, die letztendlich durch die TOP xx Bedingung raus fliegen, wundert es mich schon, dass das so erneom viel schneller abgearbeitet wird. Für mich riecht das eher nach einem Fehler bei der Erstellung des Ausführungsplans.
    Mittwoch, 15. November 2017 07:36
    |
  • Question
    Anmelden
    0
    Anmelden

    Nein, das ist kein Fehler.
    Da du ja nur 1 Satz im Geschäftsjahr hast, braucht aus dem linken Teil ja nur dieses Geschäftsjahr überhaupt ermittelt werden.
    Gedanklich kannst du den Join ja auch umdrehen:

    select top 10 Kunde, Artikel, sum(Umsatz)  as Umsatz from Geschäftsjahr G
    inner join UmsatzDetail U on G.Jahr = U.Jahr
    group by Kunde, Artikel
    order by Umsatz desc

    Somit wird 1 Satz aus Geschäftsjahr und aus Umsatzdetails nur Umsätze dieses Jahres ermittelt.

    Ohne den Geschäftsjahres-Filter müssen ja erst mal die Summen aller Umsätze aggregiert und sortiert werden bevor dann die 10 Ergebnisse geliefert werden sollen. Bei 2,5Mio Sätzen kann das je nach Hardwareausstattung schon mal dauern.
    2,5Mio in 5 Minuten macht ca. 8000 Sätze pro Sekunde, was eher eine schlechte Leistung ist.
    D.h., bei ca. 20 Sekunden wurden ca. 160.000 Sätze verarbeitet, die zu dem 1 Geschäftsjahr gehören.

    Zum Vergleich: Ich arbeite mit Firebird 2.5, kein SMP-System, also nur 1 CPU, 1 Thread, schafft bei solchen Abfragen ca. 50.000 bis 250.000 / Sekunde je nach Satzlänge (also Anzahl Felder).
    Mittwoch, 15. November 2017 08:26
    |
  • Question
    Anmelden
    0
    Anmelden

    Wie ich schon geschrieben habe, liefert die Quelle aus einer 3-Teileigen Union Abfrage erstmal 2.400.000 Datensätze, die durch einen ersten Folter auf ca. 80.000 reduzeirt werden.

    Aber darum geht es doch gar nicht!

    Es geht mir darum heraus zu findne, weshalb ein 'INNER JOIN' bzw. 'WHERE =' fast 4 Minuten dauert und bei dem, objektiv gesehen, nicht ganz korrekten RIGHT OUTER JOIN weniger als 20 Sekunden.
    Nicht ganz korrekt schreibe ich deshalb, weil, wenn ich den Zusatz TOP xx weg lassen würde ja auch jeere Datensätze heraus kommen müssten, weil zu dem Jahr aus der Geschäftsjahrestabelle ja im Prinzip leer Datenfelder aus den Datensätzen der Tabelle/Abfrage UmsatzDeteils nicht passende und daher llere Datensätze dazu kommen müssten.
    Es ist diese riesige Laufzeitdifferenz, die mich wundert.

    Mittwoch, 15. November 2017 10:31
    |
  • Question
    Anmelden
    0
    Anmelden

    Ich versuche es noch mal:

    Select * from A
    left join B on ...

    ist identisch zu

    select * from B
    right join A on ...

    Da Tabelle B nur 1 Satz enthält, werden die Daten aus Tabelle A direkt mit dem Schlüsselk gefiltert und somit weniger Datensätze überhaupt verarbeitet.

    Lässt du nun die Einschränkung weg, wird der gesamte Bestand von A verwendet was dann halt erheblich länger dauert.
    Das Problem ist doch, dass bei einer Abfrage real keine echten Tabellen als Zwischenergebnis gebildet werden.
    Der SQL-Server, ebenso wie andere DB's, greift im Wesentlichen von links nach rechts auf die Daten zu und ermittelt genau die Informationen für eine Zwischenergebniszeile aus allen Tabellen.
    Danach werden im Speicher erst die Aggregat und Gruppierungsinformation gesammelt um sie dann zu verarbeiten und zurückzugeben.

    SQL besteht also aus 2 Schritten:
    1. Daten sammeln
    2. Ergebnis, falls erforderlich aggregieren und sortieren

    Je nach Optimizer kann das auch on the fly durchgerouted werden, was dann häufig den Effekt hat, dass die ersten Daten u.U. sehr schnell kommen und dann beim Weiterlesen wieder Wartezeiten entstehen.

    Beispiel:
    TabelleA (Text, Info) enthält die Daten

    A 1
    A 2
    A 3

    TabelleB (Info, Info2) enthält die Daten

    1000 Mal 1 X
    1000 Mal 2 X
    1000 Mal 3 Z

    Beide Tabellen enthalten keine Indizes!
    Bei einem

    select * from TabelleA inner join TabelleB using(Info)
    wird nun für jeden Satz aus TabelleA alle Sätze aus TabelleB durchsucht um die passende Info zu finden.
    D.h. in diesem Fall 3 * 3000 = 9000 Zugriffe.
    Mit Schlüssel über Info ist dies nun 3 * 1000.

    Sobald es also Filter und Indizes gibt, lassen sich die Informationen direkt einschränken.

    Um dies nun auf deine Ursprungsabfrage zu übertragen ist dies so, als ob du an Stelle des "Right Join" direkt eine Where Klausel noch vor der Gruppierung/Summierung verwendest.
    Dies zieht sich auch durch alle Views durch, die von einer Abfrage verwendet werden, da Views nur uns die Arbeit erleichtern, der enthaltene SQL aber letztlich in den Ursprungs-SQL eingearbeitet wird.

    Es ist nun Sache des Optimizers, den SQL so intelligent zu zerlegen um Filter/Index-Nutzung bereits beim Sammeln der Daten zu verwenden, denn hinterher ist es zu spät.

    Dazu gibt es u.U. auch ebenso einen Gedankenfehler, dass "With"-Konstrukte besser funktionieren.
    In Wirklichkeit dient dies nur der besseren Schreibweise für uns, denn die endgültige Auflösung erfolgt dann wieder mittels "Derived Table":

    With x1 as (select * from Table where ... group ... having ...)
    select * from X1 a join X1 b on a.Key = b.Key

    =>

    select * from
    (select * from Table where ... group ... having ...) a
    join (select * from Table where ... group ... having ...) b on a.key = b.key

    Wie man hier dann sieht, erfolgt der Zugriff auf die Table sogar 2x da es eben keine echten Zwischentabellen gibt.

    Übrigens kann man dies auch selber durch Prozeduren beschleunigen, wenn man die Schritte ggf. selber zerlegt:
    create global temporary HilfsTable (....)
    create index
    insert into HilfsTable select .....

    select * from HilfsTable a Join HilfsTable b on a.key = b.Key

    Auf diesem Wege habe ich Abfragen von mehreren Minuten auf z.T. enige Sekunden gedrückt.
    Global Temporary ist connectionspezifisch und daher parallel verwendbar und wird automatisch aufgeräumt.

    Mittwoch, 15. November 2017 11:13
    |