Lange Wartezeit für Datenabfrage für Gauss-Graphen

[DoktorD]

Servus.

Ich habe ein Probelm mit der Ausfürzeit einer bzw. mehrerer Abfragen.
Ich will mir eine Gaussverteilung graphisch erzeugen (Temperaturverteilung). Dafür unterteile ich mir einen Bereich (der der angezeigt werden soll), in 80 Klassen.

Beispiel:
0 - 2 Grad Celcius
Klasse 1 geht dann von 0.000 - 0.025
Klasse 2 geht dann von 0.025 - 0.050
usw.

Meine Abfrage sieht wie folgt aus (um Counts der jeweiligen Klassen zu bekommen):

SQL-Code:

SELECT COUNT(dbo.V_MEASURE.IST_WERT)
FROM dbo.V_MEASURE
WHERE dbo.V_MEASURE.PRUEFID IN
  (
  SELECT dbo.V_MEASURE.PRUEFID FROM dbo.V_MEASURE
  where dbo.V_MEASURE.AUFTRAG = '1291626-92' AND
        dbo.V_MEASURE.MW_NAME = 'Kriterium' AND
       (dbo.V_MEASURE.IST_WERT >= (dbo.V_MEASURE.SOLL_WERT - 0.05)) AND
       (dbo.V_MEASURE.IST_WERT <= (dbo.V_MEASURE.SOLL_WERT + 0.05))
  ) 
AND dbo.V_MEASURE.MW_NAME = 'Temperatur T1'
AND dbo.V_MEASURE.IST_WERT >= (0.000) 
AND dbo.V_MEASURE.IST_WERT <= (0.025)

In der Datenbank sind sehr viele Messwerte (ca. 1 Millionen). Jeder Prüfling (für den die Messwerte anfallen) hat unterschiedliche Messwerte. In der Abfrage ist das z.B. "Kriterium", nach dem in der Abfrage mit einem Subselect vorselektiert wird.

An sich funktioniert alles super nur die Abfragezeit ist höllisch lange. Es muss ja für den Graphen 80 Mal diese Abfrage in der großen Datenbank ausgeführt werden (insgesamt dauert das dann ca. 2 Minuten).

Mache ich da etwas falsch, oder muss ich mich damit abfinden? Hat jemand von euch schonmal diesbezüglich eine Gaussverteilung erstellt? Wenn ja wie?


Danke für Eure Mühe

[himitsu]

Würde es gehn, wenn du IST_WERT bei der Abfrage ignorierst, allso alles zusammen ausließt
und es dann erst in die 80 Klassen aufteilst?
bzw. den Gesamtbereich (z.B. 0-2 °C) auf einmal abfragst.

Wäre dann ja nur noch eine etwas größere Abfrage.
Oder alles zum aktuellen AUFTRAG+MW_NAME+Anzeigebereich erstmal in eine temporäre Tabelle und die genauen IST_WERT-Abfragen dann in dieser.

[sx2008]

Du könntest eine zusätzliche Tabelle "TblTempClass" anlegen:

Code:

TempClass | MinTemp | MaxTemp
===============================
0         | -1000   | 0
1         | 0.000   | 0.025
2         | 0.025   | 0.05
...
81        | ??      | 999999

Man beachte die Randeinträge für T0 und T81.
Damit gehen Temperaturen ausserhalb deines geplanten Tempbereichs nicht verloren.
Auf dem Feld TempClass liegt der Primärschlüssel und auf MinTemp und MaxTemp
ein gemeinsamer Index.
Den Wert ?? bitte selber ausrechnen.
Falls du andere Temperatur Intervalle haben möchtest, die Tabelle leeren
TRUNCATE TABLE TblTempClass und vom Programm neu befüllen.

Diese Tabelle wird mit V_MEASURE verjoint:

SQL-Code:

SELECT TblTempClass.TempClass, Count(*) AS Anzahl FROM
V_MEASURE INNER JON TblTempClass ON
V_MEASURE.IST_WERT >= TblTempClass.MinTemp AND V_MEASURE.IST_WERT < TblTempClass.MaxTemp
GROUP BY TblTempClass.TempClass
HAVING dbo.V_MEASURE.AUFTRAG = '1291626-92' AND
        dbo.V_MEASURE.MW_NAME = 'Kriterium'

Diese Abfrage dürfte unter einer Sekunde benötigen, vorausgesetzt
die Felder AUFTRAG und MW_NAME haben jeweils einen Index.

Wer Syntaxfehler findet darf sie behalten.

[DoktorD]

Also der Lösungsansatz von sx2008 finde ich ist der Königsweg. Aber darauf wäre ich nie alleine gekommen.
Ich will den nun also umsetzen (bin noch SQL Anfänger)

SQL-Code:

select dbo.TemporaereKlassen.Klasse, Count(*) AS Anzahl FROM
  dbo.V_Measure INNER JOIN dbo.TemporaereKlassen ON
  (dbo.V_Measure.IST_WERT >= dbo.TemporaereKlassen.MinValue) AND
  (dbo.V_Measure.IST_WERT <  dbo.TemporaereKlassen.MaxValue)
GROUP BY dbo.TemporaereKlassen.Klasse HAVING dbo.V_Measure.Auftrag = '1291626-92'

nun kommt beim ausführen die Meldung

SQL-Code:

Server: Nachr.-Nr. 8121, Schweregrad 16, Status 1, Zeile 1
Die dbo.V_Measure.AUFTRAG-Spalte ist in der HAVING-Klausel ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Was hat das zu bedeuten?

Die Klasse mit den Grenzen habe ich mir mal statisch angelegt. Wobei das aber auch noch ein Problem für mich darstellt. Denn es kann sein, das von mehreren Rechnern gleichzeitig eine Abfrage gestartet wird, die aber unterschiedliche Klassen haben kann.
Somit würden beide auf die selbe Tabelle zugreifen
Kann man sich in der Abfrage auch so eine Art Temporäre Tabelle erzeugen, die nach der Abfrage dann wieder weg ist?
Wie man temporäre Tabellen erzeugt habe ich schon ausprobiert, allerdings existieren diese dann für die komplette Sitzung bis man sich wieder abmeldet.

[generic]

Wenn deine SQL zu langsam ist, hast du mehrere Möglichkeiten.

- Das SQL optimieren z.B. durch geschicktere Formulierung (der QueryAnalysor hilft dabei)
- du kannst Indizes anlegen
- du kannst die Tabellenstruktur überdenken
- du kannst DatenCachen / Vorberechnen
- du kann auch einfach einen größeren Server kaufen.

[mkinzler]

Bei Gruppierungen müssen die Spalten entweder als Aggegate ( SUM, MIN, MAX, AVG, ...) vorliegen oder in der Gruppierung sein

[DoktorD]

Zitat von mkinzler:

Bei Gruppierungen müssen die Spalten entweder als Aggegate ( SUM, MIN, MAX, AVG, ...) vorliegen oder in der Gruppierung sein

Das verstehen ich irgendwie nicht. Wie kann denn eine Spalte in einer Gruppierung sein? Habt ihr mal ein beispiel dazu. Ich habe hier ein buch neben mir liegen. Aber laut dem müsste das so gehen

[mkinzler]

SQL-Code:

select
    <Felder ohne Aggregat>,
    <Felder mit Aggegat>
from
    <Tabelle>
group by
    <Felder ohne Aggregat>;

also z.B.

SQL-Code:

select
    datum,
    sum(umsatz) as Tagesumsatz
from
    umsaetze
group by
    datum;

[sx2008]

Ähhm, die HAVING Klausel ist natürlich falsch.
HAVING greift NACH der Gruppierung, während WHERE vorher greift:

SQL-Code:

select dbo.TemporaereKlassen.Klasse, Count(*) AS Anzahl FROM
  dbo.V_Measure INNER JOIN dbo.TemporaereKlassen ON
  (dbo.V_Measure.IST_WERT >= dbo.TemporaereKlassen.MinValue) AND
  (dbo.V_Measure.IST_WERT <  dbo.TemporaereKlassen.MaxValue)
WHERE dbo.V_Measure.Auftrag = '1291626-92'
GROUP BY dbo.TemporaereKlassen.Klasse

[alzaimar]

Sofern die einzelnen Klassen gleich groß (breit) sind, kann man die Klasse auch direkt aus dem Ist-Wert berechnen und danach gruppieren. Diese Lösung ist aber nicht so flexibel wie die von Sx2008, benötigt dafür aber keine zusätzliche Tabelle.
Also so:

SQL-Code:

SELECT PRUEFID, 
       TRUNC (CASE 
         WHEN IST_WERT < 0 THEN 0
         WHEN IST_WERT > 2 THEN 2
         ELSE IST_WERT END /0.025) as Klasse,
       Count (*) as Anzahl
FROM dbo.V_MEASURE
WHERE dbo.V_MEASURE.AUFTRAG = '1291626-92' 
  AND dbo.V_MEASURE.MW_NAME = 'Kriterium' 
  AND dbo.V_MEASURE.MW_NAME = 'Temperatur T1'
GROUP BY 
  PRUEFID, 
  TRUNC (CASE 
         WHEN IST_WERT < 0 THEN 0
         WHEN IST_WERT > 2 THEN 2
         ELSE IST_WERT END /0.025
  )

Oder so:

SQL-Code:

Select PruefID, 
      Klasse, 
      Count (*) as Anzahl
From (
  SELECT PRUEFID, 
         TRUNC (CASE 
           WHEN IST_WERT < 0 THEN 0
           WHEN IST_WERT > 2 THEN 2
           ELSE IST_WERT END /0.025) as Klasse
   FROM dbo.V_MEASURE
  WHERE dbo.V_MEASURE.AUFTRAG = '1291626-92' 
    AND dbo.V_MEASURE.MW_NAME = 'Kriterium' 
    AND dbo.V_MEASURE.MW_NAME = 'Temperatur T1'
  ) X
Group By PruefID, Klasse