query result cache

[HHick123]

Hallo Leute,
ich stehe vor folgendem Problem (MS SQL Server 2005):

Hohe Anzahl aufeinander aufbauender Views mit UNIONS, CLR-Aggregatfunktionen, verschiedenen Joins. Etwa 40 Views bilden das "Resultat". Einen hab' ich mir mal inkl. seiner Abhängigkeiten exemplatisch ausgedruckt, das waren 11 Seiten A3 Querformat.

Ganz so schlimm ist es nicht. Die meisten Views laufen akzeptabel schnell. Lediglich etwa 8 Views müssten dringend schneller sein. Wobei die Antwortzeit bei etwa 15 Sekunden liegt. Vielleicht sogar wäre das noch akzeptabel, wenn nicht jeder User - die SQL-Queries sind völlig identisch - so lange darauf warten müsste, obwohl sich die zugrundeliegenden Tabellendaten nicht geändert haben!!!

Zunächst dachte ich daran, unique clustered indices für die views zu vergeben. Aber das war eine Sackgasse, weil daran derart viele Bedingungen geknüpft sind, dass ich damit höchstens in einfachen Spezialfällen beim Refactoring durchkommen würde. Und das sind genau die Views, die eh kein Problem machen.

Momentan bin ich ein bischen ratlos.

"Query Result Cache" gibt es anscheinend nur bei Oracle.

Vielleicht könnte ich mit Triggern die Views in Tabellen schreiben. Aber das wird vermutlich sehr aufwendig. Ausserdem weiss ich nicht genau,wie. Es sollen auch nicht alle Views, bei Änderung der zugrundeliegenden Tabellendaten neuberechnet werden, denn das würde viel zu lange dauern. Sondern ein View soll erst dann neu berechnet werden, wenn der erste User auf den View zugreift. Für die weiteren User soll es dann schnell gehen...

Wie macht man so etwas?

Viele Grüße,
Helmut

[ebis]

Abfrageoptimierung mit SQL Server

[HHick123]

Im Prinzip ein interessanter link. Hab' ihn kurz im Schnellauf angesehen.
Wenn ich zwei bis drei Mannjahre Budget hätte, würde ich die Datenbankstruktur umbauen, wobei ich auch mittlerweile sehr konkrete Ideen hätte. Ich bin mir ziemlich sicher, zu wissen, wo der stukturelle Fehler ist. Ist aber leider nicht drin, wie es aussieht.

Tja Query Result Cache gibt es anscheinend nicht beim Microsoft SQL Server.
Sieht so aus, als müsste ich mir irgend einen schrägen Workaround einfallen lassen:

Z.B. in der Art: Er erste Client, der den View abfragt, schreibt die Ergebnis-Datensätze in eine Tabelle zurück und setzt dann in irgendeiner Art und weise einen Mutex. Aufgrund des Mutex wissen die anderen Clients, dass die Daten in der Tabelle gültig sind und lesen sie von dort (was dann ziemlich schnell geht). Bevor ein Client einen Datensatz einer dem View zugrundeliegenden Tabellen ändert, wird der Mutex wieder rückgesetzt und es geht von vorne los (der View wird wieder ausgeführt). Aber da gibt's sicher eine Menge Haken...

Oder ich sende die View-Ergebnisse auf anderem Weg von Client zu Client, z.B. mit Mailslots, oder einfach per TCP-Sockets, sodass nur selten auf die Datenbank zugegriffen werden muss.

Aber irgendwie möchte ich mir das alles ersparen.

Ich kann mir nicht vorstellen, dass ich der einzige bin, der vor der Schwierigkeit steht, dass die Datenbank für jeden Client die Views neu ausführt, obwohl sich die zugrundeliegenden Daten gar nicht geändert haben...

Das eigentliche Problem besteht darin, dass zuviel Funktionalität in der Datenbank liegt (die Daten werden relativ kompliziert berechnet). Im Prinzip könnten auch die Clients die komplizierten Views lokal berechnen... Wenn die Datenbank eh' keinen Cache hat. Wär vermutlich besser bei gleichzeitigen Zugriffen...

Viele Grüße, Helmut

[ebis]

Lediglich etwa 8 Views müssten dringend schneller sein. Wobei die Antwortzeit bei etwa 15 Sekunden liegt.

Du meinst ja schon zu wissen, welche Views zu lang brauchen...

bei diesen einfach mal den Ausführungsplan anschauen, woran das liegt...

(Auch das erklärt Steffen in seinem Video)

[hwoess]

Ich hatte ein ähnliches Problem, dass ich mit Hilfstabellen gelöst habe. Die Abfragen verwenden jetzt diese Tabellen statt der Views.
Die Erstellung dieser Hilfstabellen erfolgt auf zwei Arten:
- bei den nicht zeitkritischen Sachen macht das ein Job per Server-Agent jede volle Stunde (egal ob eine Neuberechnung nun erforderlich wäre oder nicht) und die Leute wissen, das die Ergebnisse immer nur stundenaktuell sind.
- bei Sachen, die so aktuell wie möglich sein sollen habe ich an den entsprechenden Tabellen Trigger hängen, die ein Kennzeichen in einer Hilfstabelle setzen, wenn eine Änderung eine Neuerstellung erforderlich macht. Dazu gibt es eine stored proc als Endlosschleife, die mit dem Server mitgestartet wird und die im Sekundenabstand nachschaut, ob es ein Kennzeichen gibt und wenn ja dann löscht es das Kennzeichen und macht die Neuberechnung. Erst wenn die Berechnung fertig und das Ergebnis in den Hilfstabellen gespeichert ist, wird wieder nach dem Kennzeichen geschaut. Damit wird verhindert, dass ein mehrmaliges Setzen des Kennzeichens während eines Berechnungslaufes (dauert bei mir derzeit knapp zwei Minuten) ein mehrmaliges Neuberechnen auslöst, was ja keinen Sinn hätte.
Weiters wird hier während der Dauer der Berechnung ein Flag gesetzt und der Aufrufer bekommt in diesem Fall eine Nachricht, dass gerade eine Berechnung läuft und er wird gefragt ob er warten oder den vielleicht nicht ganz aktuellen Datenstand haben oder die Abfrage stornieren will.


bye,
Helmut

[HHick123]

Du meinst ja schon zu wissen, welche Views zu lang brauchen...

bei diesen einfach mal den Ausführungsplan anschauen, woran das liegt...

(Auch das erklärt Steffen in seinem Video)

Hallo ebis,
ja den Ausführungsplan hab' ich freilich schon angesehen. Der ist ziemlich gigantisch. Eventuell könnte man ihn am A1-Plotter sogar ausdrucken ;-)

Im Grunde genommen ist die für die langsamen Views relevante Datenbank-Struktur eine Schneeflocken-Struktur, wobei allerdings jeweils die "Faktentabellen" im Zentrum selbst keine Tabelle, sondern relativ kompliziert berechnete Views sind: Jeweils Union aus vier anderen Views, dann verschiedene Felder berechnet und gruppiert. Diese Faktentabellen werden dann noch mit Detaildaten gejoint und anhand der Detaildaten gefilter.

Ein Redesign wäre sicher möglich, fällt aber eventuell eher in die Kategorie "Lebensaufgabe"...

Daher hoffte ich auf eine einfache Cache Lösung....

Viele Grüße,
H

[knoxyz]

Hallo HHick123,

sind Indizes bereits erstellt?

Falls der Aufruf der einzelnen Views sehr schnell und nur die komplette Abfrage langsam ist,
könntest du die einzelnen Views in temporäre Tabellen laden
und diese dann zur endgültigen Abfrage verwenden.

Hatte das Mal zum kumulieren von Werten benötigt.

IF object_id('#temp') IS NOT NULL DROP TABLE #temp;

SELECT Zeile, Bezeichnung, Wert
INTO #temp
From tabName

SELECT b.Bezeichnung, b.Wert, Sum(a.Wert) AS kumWert
FROM #temp a
INNER JOIN #temp b
            ON a.zeile >= b.zeile
GROUP BY b.Bezeichnung, b.Wert 

Andernfalls empfehle ich wie hwoess,
die Ergebnisse der sich am wenigsten ändernden und langsamsten Views
regelmäßig in Tabellen zu speichern.

[HHick123]

Hallo Leute,
mein Weltbild ist gerade etwas erschüttert.

Also ich hab einen der problematischen Views (mit Ausführungsplan im A1-Format) mal mit einem SELECT * INTO in eine Tabelle kopiert.

Ja, was soll ich sagen... Der Zugriff mit Management-Studio SELECT * FROM ... ist schon schneller, anstatt 8 Sekunden etwa 5 Sekunden. Aber die Welt ist das nicht!!

Wenn ich nur die Hälfte der Spalten selektiere, dauert es etwa halb so lang. Anscheinend geht die Zeit etwa linear mit den Spalten.

Ich habe etwa 25000 Zeilen mit etwa 400 Spalten.

Was kann ich tun?!

pls help!!

[knoxyz]

Was kann ich tun?!

1) Ein "Select *" ist bei 400 Spalten sicher nicht nötig.
Nimm nur das was unbedingt benötigt wird.

2) Für die Tabelle in der du die Daten der View speicherst, einen Index erstellen.

3) Hast du den Weg mit den temporären Tabellen (Name beginnt mit "#") versucht?

Wenn du alle 3 Punkte berücksichtigst, benötigt die Abfrage sicher weniger als eine Sekunde.

[HHick123]

1) Ein "Select *" ist bei 400 Spalten sicher nicht nötig.
Nimm nur das was unbedingt benötigt wird.

Daran hab' ich auch schon gedacht,
jede Zeile ist ein Bauteil in einem CAD-System. Etwa 2/3 der Spalten sind eben die Attribute des Bauteils im CAD-System (sehr dünn befüllt, sehr viele NULL). Der Rest sind Informationen zu Bestellabwicklung und Transport. Ich könnte mit vorstellen, z.B. die CAD-Attribute als XML-Zeichenkette in einem einzigen Datenbank-Feld zusammenzufassen, allerdings könnte es dann wieder bei anderen Views und beim Eintragen eines Bauteils in die Datenbank haken, daher bin ich bis jetzt davor zurückgeschreckt...

2) Für die Tabelle in der du die Daten der View speicherst, einen Index erstellen.

Hab' es probiert mit dem Datenbankoptimierungsratgeber: Der hat einen Index erstellt, war aber genau so langsam.

3) Hast du den Weg mit den temporären Tabellen (Name beginnt mit "#") versucht?

Wie wäre das mit den temporären Tabellen? der problematische View basiert ja, wie gesagt auf anderen Views, die wieder auf anderen Views basieren, usw.
Würde das bedeuten, ich solle ein sql function erstellen, in die ich die selects der views hineinkopiere und das zwischenergebnis jeweils in temporäre tabellen speichere?! Wäre das schneller?

Viele Grüße, Helmut