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

SQL-Code:

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. :zwinker:

[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:

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

[himitsu]

schließt sich das nicht gegenseitig aus?

Code:

AND dbo.V_MEASURE.[b]MW_NAME[/b] = 'Kriterium'
AND dbo.V_MEASURE.[b]MW_NAME[/b] = 'Temperatur T1'

[alzaimar]

Zitat:

Zitat von himitsu

schließt sich das nicht gegenseitig aus?

:gruebel: Irgendwie schon. Habs einfach von oben übernommen.

[hoika]

Hallo,

dein Hauptproblem ist der SubSelect (IN)
Für jede Zeile der Tabelle wird er ausgeführt.
DB's optimieren meist von links nach rechts (MSSQL-2000 ist je schon etwas älter)

Ausserdem wid die Tabelle hier 2mal benutzt -> Table Aliases

1. Mit subselect
================

Als erstes würde ich also die Where-Klausel umstellen
(testen kann man es ja mal)

SQL-Code:

SELECT COUNT(V1.IST_WERT)
FROM dbo.V_MEASURE V1
WHERE 
V1.MW_NAME = 'Temperatur T1'
AND V1.IST_WERT >= (0.000)
AND V1.IST_WERT <= (0.025)

and V1.PRUEFID IN
  (
  SELECT V2.PRUEFID FROM dbo.V_MEASURE V2
  where V2.AUFTRAG = '1291626-92' AND
        V2.MW_NAME = 'Kriterium' AND
       (V2.IST_WERT >= (V2.SOLL_WERT - 0.05)) AND
       (V2.IST_WERT <= (V2.SOLL_WERT + 0.05))
  )

Nächster Schritt wäre das IN durch EXISTS zu ersetzen.
Das sollte schneller sein (unter Firebird ist es es auf jeden Fall).


Schwieriger ist es den Subselect loszuwerden.
Aber hier ginge doch ein Join ?

Jetzt mal aus der kalten getippert

SQL-Code:

SELECT COUNT(V1.IST_WERT)
FROM dbo.V_MEASURE V1

INNER JOIN dbo.V_MEASURE V2
ON V1.PRUEFID=V2.PRUEFID

WHERE 
V1.MW_NAME = 'Temperatur T1'
AND V1.IST_WERT >= (0.000)
AND V1.IST_WERT <= (0.025)

AND V2.AUFTRAG = '1291626-92' AND
    V2.MW_NAME = 'Kriterium' AND
    (V2.IST_WERT >= (V2.SOLL_WERT - 0.05)) AND
    (V2.IST_WERT <= (V2.SOLL_WERT + 0.05))

Ich würde das per SELECT V1.IST_WERT mit wenigen Werten einfach mal ausprobieren.


Heiko

[DoktorD]

Ahhh. Jetzt verstehe ich es auch endlich. ;-)

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

Das von "alzaimar" werde ich aber auch nochmal probieren. Dank Dir.


Gibts denn auch eine Lösung für das erstellen einer temporären Tabelle mit den Klassen?
Ich kann hier im Forum und im restlichen Internet einfach nichts drüber finden






_

[hoika]

Hallo,

also wenn ich in google "sql server temp table" eingebe,
bekomme ich ne Menge Input ...

Das IN wird in eine Temp-Table (Create Table #X)
geschoben und dann per Join mit der Ursprungstabelle verknüpft.

Aber:
1. schickes Zitat

Zitat:

most of the time this will be slower than a few good joins, but not always

2. temp tables (local also mit einem #) sind sichtbar innerhalb der
eigenen Connection, nicht nur dieser Abfrage und werden beim Close (oder Drop Table) gelöscht.

Warum temp tables, wenn es über einen Join auch geht
und der Weg mit der temp table auch über einen Join geht ?


Heiko