Datenbankabfrage dauert sehr lange

  • C#

Es gibt 12 Antworten in diesem Thema. Der letzte Beitrag () ist von Yanbel.

    Datenbankabfrage dauert sehr lange

    Hallo zusammen,

    ich logge bei uns im Werk verschiedene Anlagenparameter von mehreren Steuerungsmaschinen mit.
    Es werden etwas mehr als 300 Parameter aller 10s, manche auch nur aller 10 Minuten mitgeloggt und in eine SQL-Datenbank gespeichert.
    Als SQL-Anwendung benutzte ich SQL-Express.
    Alle Abfragen der Datenbank habe ich über gespeicherte Prozeduren realisiert.
    Das System läuft nun seit ca. 1 gutem Jahr recht stabil.

    Ich bin heute dabei eine neue Funktion zu integrieren und rufe dabei per gespeicherter Prozedur 6 Messwerte für 2 Anlagen über einen Zeitraum von z.B. 8 Tagen ab.
    Es handelt sich dabei um Produktions- bzw. Stillstandszeiten.
    Die Abfrage für die 2 Anlagen sind jeweils 2 getrennte Abfragen. An Messwerte kommt bei einem Logzyklus von 10s natürlich einiges zusammen.
    Was jetzt aber sehr merkwürdig ist, ist das die Abfrage der einen Tabelle so rund 0.05 s dauert. Die der anderen Tabelle jedoch > 11 s.
    So richtig kann ich mir da keinen Reim drauf machen.

    Hier mal die Funktion, die beim Betätigen des Abfrage-Buttons ausgeführt wird
    Spoiler anzeigen

    C#-Quellcode

    1. // Daten nach Auswahl der Anlage erfassen
    2. tAbfrageStart = DateTime.Now;
    3. switch (DropDownListAnlageTest.Text)
    4. {
    5. case "Konti1": dsStatistik = GetMesswerteZeitraumKonti1(strStartDatumZeit, strEndDatumZeit);
    6. break;
    7. case "Konti2": dsStatistik = GetStatistikZeitraumKonti2(strStartDatumZeit, strEndDatumZeit);
    8. break;
    9. default:
    10. break;
    11. }
    12. tAbfrageEnde = DateTime.Now;
    13. tAbfrageDauer = tAbfrageEnde - tAbfrageStart;
    14. tAbfrageZeiten.Add(tAbfrageDauer.ToString());


    Die gespeicherte Prozedur sieht so aus:

    Quellcode

    1. BEGIN
    2. -- SET NOCOUNT ON added to prevent extra result sets from
    3. -- interfering with SELECT statements.
    4. SET NOCOUNT ON;
    5. -- Insert statements for procedure here
    6. SELECT id, Zeitstempel, aktuelleLaufmeter, RakelLaminiereinheitPumpeGeschwindigkeit, RandschnittMesserBS, RandschnittMesserAS FROM Konti2
    7. WHERE Zeitstempel BETWEEN @Startzeitpunkt AND @Endzeitpunkt
    8. ORDER BY id ASC
    9. END


    Und aufgerufen wird diese über das Programm folgendermaßen:
    Spoiler anzeigen

    C#-Quellcode

    1. DataSet GetStatistikZeitraumKonti2(string Startzeitpunkt, string Endzeitpunkt)
    2. {
    3. DataSet ds = new DataSet();
    4. DataTable dt = new DataTable();
    5. SqlConnection conn = new SqlConnection(connectionString);
    6. SqlDataAdapter DA = new SqlDataAdapter();
    7. SqlCommand cmd = new SqlCommand();
    8. cmd.Connection = conn;
    9. cmd.CommandType = CommandType.StoredProcedure;
    10. cmd.CommandText = "Get_StatistikZeitraumKonti2";
    11. cmd.Parameters.AddWithValue("@Startzeitpunkt", Startzeitpunkt);
    12. cmd.Parameters.AddWithValue("@Endzeitpunkt", Endzeitpunkt);
    13. DA.SelectCommand = cmd;
    14. conn.Open();
    15. ds.Reset();
    16. DA.Fill(ds);
    17. DA.Dispose();
    18. conn.Close();
    19. conn.Dispose();
    20. return ds;
    21. }


    Die Aufrufe für die andere Anlage (Konti1) sind identisch.

    Hat jemand vielleicht einen Tipp, wo mein Fehler liegt das die eine Abfrage soviel länger dauert als die andere?
    Wo könnte ich nach Fehlern suchen?

    Habt schon mal vielen Dank!

    [Update:] ich habe es jetzt mehrmals im Einzelschritt debugged und kann noch sagen, dass die Zeitverzögerung beim Befehl "DA.Fill(ds)" auftritt.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „Grimsey“ ()

    Verbinde dich mal mit SQL Management Studio und teste die Stored Procedures mal direkt dort.
    Sind die Zeiten dort auch so?
    Wenn ja dann könnte man SQL Seitig eventuell was machen. Stichwort Parameter Sniffing/Index etc
    Das ist meine Signatur und sie wird wunderbar sein!
    Danke für Deinen Tipp,

    ich habe die Abfragen jetzt mal übers SQL Management Studio laufen lassen.
    Bei der Anlagen sind es 35111 Zeilen die für den gewählten Zeitraum in 0.339 s ausgegeben werden.
    Für die andere sind es 51222 Zeilen die in 11.4 s ausgegeben werden.

    Wo und wie kann man da noch was machen? Ich bin da auf dem Gebiet noch recht unbedarft.

    [Update:] habe mich mal hier belesen. Und auch die Änderungen vorgenommen an einer der Prozeduren.
    Hat aber leider an der Zeit rein gar nichts geändert.

    Habe die Prozedure auch mal komplett gelöscht und neu angelegt....ohne sichtbaren Erfolg.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Grimsey“ ()

    Ok sorry. Das habe ich etwas ungenau beschrieben.
    Das ganze System besteht aus einem Log-Programm, welches diverse Werte alle 10 Sekunden und andere Werte nur aller 10 Minuten erfasst.

    Um die Daten anzuzeigen habe ich eine ASP.Net-Anwendung/Website erstellt.
    Und in dieser nutze ich die oben genannten Prozeduren um verschiedene Werte aus der Datenbank zu holen.

    Ich konnte habe jetzt den Zeitraum für die Abfrage auf 2 Tage begrenzt und mal ganz normale Abfragen im SQL-Management-Studio benutzt

    SQL-Abfrage

    1. SELECT [id]
    2. ,[Zeitstempel]
    3. ,[aktuelleLaufmeter]
    4. ,[RakelLaminiereinheitPumpeGeschwindigkeit]
    5. ,[RandschnittMesserBS]
    6. ,[RandschnittMesserAS]
    7. FROM [MDE].[dbo].Konti2
    8. WHERE Zeitstempel BETWEEN '03.02.2020 00:01' AND '04.02.2020 23:59'
    9. ORDER BY id ASC


    => liefert 14576 Zeilen und benötigt dafür 11.4s

    SQL-Abfrage

    1. SELECT [id]
    2. ,[Zeitstempel]
    3. ,[Zaehlwert1]
    4. ,[Zaehlwert2]
    5. ,[Geschwindigkeit]
    6. ,[aktuelleLaufmeter]
    7. FROM [MDE].[dbo].[Konti1]
    8. WHERE Zeitstempel BETWEEN '03.02.2020 00:01' AND '04.02.2020 23:59'
    9. ORDER BY id ASC


    => liefert 12090 Zeilen in 0.29 s

    Selbst wenn ich den Zeitraum auf 1 Tage schrumpfe, dauert das Zurückliefern der dann rund 7300 Zeilen wieder 11.4 s.

    Also irgendwo ist da doch was faul....es dauert immer 11.4 s egal welcher Zeitraum abgefragt wird.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Grimsey“ ()

    Kann es ein das auf der einen Tabelle ein Index auf Zeitspempel liegt und auf der anderen nicht ?
    Vergleich mal folgendes im SQL Management Studio und aktiviere Execution Plan estimated (Ctrl + M).
    Dann hast du im Result auch einen Tab "Exectionplan. Etwa so wie im Anhang. Poste dann mal das Ergebnis bzw vll. ist es für dich auch selbsterklärend
    Bilder
    • Execution Plan.png

      48,96 kB, 513×404, 130 mal angesehen
    Das ist meine Signatur und sie wird wunderbar sein!
    Guten morgen zusammen,

    anbei mal das Bild vom Executen-Plan.
    Da steht auch eine Meldung, wenn ich es denn richtig erkenne, dass ein Index fehlen würde (?).
    Demnach habe ich wohl etwas falsch gemacht. Wo kann ich das korrigieren?

    Ich danke Euch für Eure Tipps! Habe schon jetzt wieder einiges dazugelernt!
    Danke
    Bilder
    • ExecutenPlan.png

      100,31 kB, 2.128×1.103, 113 mal angesehen
    Jo zunächst mal die fehlenden Indexdetails per Rechtsklick auf den Grünen Schriftzug anzeigen.

    Dann öffnet sich ein weiteres Abfragefenster, in welchem du die Query erhältst mit der du den Index nachpflegen kannst. Wenn ich das richtig in Erinnerung habe, dann musst du hier nur einen eindeutigen Bezeichner für den Index einsetzen da wo <Missing ... > steht.

    Ich weiß aus dem Kopf den genauen Wortlaut leider nicht mehr. Dann markierst du alles und drückst Strg+ U im die Kommentar-Zeichen zu entfernen. Query abfeuern und kurz warten. Dauert circa 5-10 Sekunden bis der Index registriert ist. Und dann nochmal deine Abfrage testen. Eventuell benötigt er noch weitere Indices.

    Benamung von Indices würde ich im Übrigen immer so wählen, dass der Tabellenname und die indexierten Felder enthalten sind. Das sorgt in der Regel für eine eindeutige Bezeichnung und macht die Struktur nachvollziehbarer.


    Ein Computer wird das tun, was du programmierst - nicht das, was du willst.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Yanbel“ ()

    Super!
    Das hat es jetzt gebracht!
    Dank Deiner Erklärung konnte ich den Index erstellen und die Abfrage läuft jetzt super schnell durch.

    Was ich nur noch nicht ganz nachvollziehen kann ist, weshalb bei dieser einen Tabelle von vielleicht nicht mal 20 notwendig ist, diesen Index zu erstellen.
    Da würde ich gerne noch etwas dazulernen.
    Hängt von verschiedenen Faktoren ab. Die wichtigsten sind hierbei folgende:

    - Ist das Feld id in der Tabelle Primary Key
    - Welche Constraints liegen sonst auf der Tabelle (Foreign Keys, Unique, etc.)
    - Die allgemeine Verarbeitung der Abfrage in SQL (Microsoft-seitig)

    Primary Keys werden automatisch in die Indexierung aufgenommen, da sie im Regelfall in Abfragen als eindeutiger Bezeichner dienen. Gleiches gilt für Unique Constraint und mit Einschränkung auch für sauber gepflegte Foreign Keys. Wenn du die Daten anders abfragst, wie in deinem Fall durch einen Zeitrahmen, dann greifst du nicht auf den PK Wert und damit auch nicht auf eine vorhandene Indexierung zurück. Bei zwei unterschiedlichen Tabellen die gleich aufgebaut sind kann der SQL-Server dennoch zwei völlig verschiedenen Abfragpläne verwenden. Das meine ich mit der allgemeinen Verarbeitung. Die können wir nur durch das manuelle nachpflegen von Indeces beeinflussen. So kommt es dass bei einer Tabelle ein Index benötigt wird und bei einer anderen ähnlichen Tabelle nicht. Hierbei geht es schlichtweg um die Art und Weise wie der SQL-Server die Daten ablegt und verwaltet. Im Grunde genommen eine Blackbox.


    Ein Computer wird das tun, was du programmierst - nicht das, was du willst.
    Hi,

    als Hinweis für die Zukunft. Beginne nicht damit die Stored Procedure direkt auszuführen und dir den Plan anzuschauen sondern beginne einfach mit dem Select und schreib die Werte direkt in die Where Klausel.
    Wenn das mal passt dann arbeite dich weiter "raus".
    Allein beim Aufruf einer Stored Procedure mit Parametern welche dann in einem Statement direkt in der Where Klausel verwendet werden können Probleme auftreten bei verschiedenen Aufrufen.
    Das sollte in deinem Fall vermutlich nicht so eine große Rolle spielen, aber damit du es mal gehört hast.
    Für Stored Procedures werden nach erstmaligem Aufruf solche Executionpläne gecached am SQL Server. Wenn dein erster Aufruf mit Parametern stattfindet die sich stark unterscheiden vom 10. Aufruf, dann kann auch das erhebliche Performanceprobleme verursachen.

    Und mit den Hinweisen welcher Index fehlt, muss man teilweise sehr vorsichtig sein. Jeder Index verlangsamt Inserts/Updates/Deletes. Ein Index über nahezu alle Spalten bringt auch sehr wenig.

    LG
    Das ist meine Signatur und sie wird wunderbar sein!
    Und mit den Hinweisen welcher Index fehlt, muss man teilweise sehr vorsichtig sein. Jeder Index verlangsamt Inserts/Updates/Deletes. Ein Index über nahezu alle Spalten bringt auch sehr wenig.


    Danke für die Nachbesserung. Da die Tabelle nur 6 Spalten hat und das hier nicht weiter von Belang ist, habe ich vergessen darauf hinzuweisen :whistling: :saint:


    Ein Computer wird das tun, was du programmierst - nicht das, was du willst.