Abfrage optimieren

[Nike77]

Hallo,
ich habe eine Abfrage erstellt die viele "Blöcke" enthält.
Jeder Block sind mehrere select Abfragen mit Funktionen usw.
Ich habe diese Abfragen immer optimiert (probiert) und die Ergebnisse (bei 50000 Datensätzen) waren hervorragend. > 1 Sek

Auch alle Blöcke untereinander funktionieren echt gut, sol lange ich das ganze begrenze auf TOP 1000. > 5 Sek.

Bei TOP 2000 bin ich dann gleich bei 100 Sek

Kann es sein das der RAM Servers zu ist?
Wie finde ich herraus wo das Problem ist?

Anbei mal ein Ausschnitt eines "Blocks" (hier ohne Funktionen) der sich immer wieder mit anderen Merkmalen und Bedingungen wiederholt:


[highlight=sql](select String from specs where (ItemID = (SELECT Item FROM Specs s WHERE s.text is NULL and (s.ItemID=i.ID or s.ItemID=i.ParentID or s.ItemID=(SELECT ParentID FROM Items i2 WHERE i2.ID=i.ParentID)
or s.ItemID=(SELECT i3.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID WHERE i2.ID=i.ParentID) or s.ItemID=(SELECT i4.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID LEFT JOIN Items i4
on i4.ID=i3.ParentID WHERE i2.ID=i.ParentID)) AND s.SpecType=428)) and (Spectype = '2') and (Language = 'DE')) as i_MerkmalEinheit11,

(select String from specs where (ItemID = (SELECT Item FROM Specs s WHERE s.text is NULL and (s.ItemID=i.ID or s.ItemID=i.ParentID or s.ItemID=(SELECT ParentID FROM Items i2 WHERE i2.ID=i.ParentID) or s.ItemID=
(SELECT i3.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID WHERE i2.ID=i.ParentID) or s.ItemID=(SELECT i4.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID LEFT JOIN Items i4
on i4.ID=i3.ParentID WHERE i2.ID=i.ParentID)) AND s.SpecType=431)) and (Spectype = '2') and (Language = 'DE')) as i_MerkmalEinheit21,

(select String from specs where (ItemID = (SELECT Item FROM Specs s WHERE s.text is NULL and (s.ItemID=i.ID or s.ItemID=i.ParentID or s.ItemID=(SELECT ParentID FROM Items i2 WHERE i2.ID=i.ParentID) or s.ItemID=
(SELECT i3.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID WHERE i2.ID=i.ParentID) or s.ItemID=(SELECT i4.ParentID FROM Items i2 LEFT JOIN Items i3 on i3.ID=i2.ParentID LEFT JOIN Items i4
on i4.ID=i3.ParentID WHERE i2.ID=i.ParentID)) AND s.SpecType=434)) and (Spectype = '2') and (Language = 'DE')) as i_MerkmalEinheit31,[/highlight]

[Falk Prüfer]

Hallo,

bitte das SQL formatieren, dass kann doch keiner lesen...

Gruß Falk

[Nike77]

erledigt

[Falk Prüfer]

... wenn du vlt. noch ein paar Zeilenumbrüche ... und Einrückungen ...

[Nike77]

na klar Ich dachte, dass das sql nicht so wichtig ist bei meiner Frage.

[Falk Prüfer]

Also um hier ohne Kenntnis der weiteren Zshg. und nur mit deinen dürftigen Angaben überhaupt eine Aussage bzgl. Performance treffen zu können, wäre für mich ein Blick auf das SQL und seine Struktur schon wichtig. Aber bei deinen "Blöcken" - oder sollte ich besser Klumpen sagen - kann ich da nichts erkennen. Da ich mir nicht selbst die Mühe machen werde, das in eine lesbare Form zu bringen und zweimalige Bitte um Besserung nichts gebracht hat, bin ich hier raus!
Vlt. geht es nur mir so und deshalb hoffe ich du findest jemand mit mehr Geduld...

Gruß Falk

[Nike77]

@Falk
beide Verbesserungen habe ich doch bestens umgesetzt :-)
Spaß bei Seite, ich kann deine Aussage verstehen. Ich wollte nur nicht das gesamte Projekt vorstellen da ich allein dafür 2 Tage benötigen würde.

Für mich ist es halt nur ein Rätsel warum die Abfrage ab einer bestimmten Menge "zusammenbricht".
Da ich kein Profi bin weiß ich nicht wo ich anfangen könnte zu suchen....

Leider habe ich alle Abfragen immer nur bei TOP 1000 getestet und mir immer die schnellste Variante raus gesucht und nun das... mmmmhhh

[hwoess]

Hole dir mal das dbForge Studio Professional Trial von Devart , damit kannst du 30 Tage lang mit dem Profiler deine Queries testen. Wenn du dir den Ausführungsplan mit TOP 1000 ansiehst und im Vergleich auch mit TOP 2000 (vielleicht immer in 100er-Schritten erhöhen), dann solltest du sehen können, wo die Zeit bleibt. Vielleicht ändert der Query Optimizer plötzlich die Umsetzung, dann könntest du eventuell mit Abfragehinweisen arbeiten. Aber es kann auch daran liegen, dass die Abfragezeit exponentiell abhängig von der Anzahl der Records ist, dann müsstest du deine Abfragen komplett anders gestalten und vielleicht andere/zusätzliche Indizes einsetzen.

bye,
Helmut

[defo]

Also um hier ohne Kenntnis der weiteren Zshg. und nur mit deinen dürftigen Angaben überhaupt eine Aussage bzgl. Performance treffen zu können, wäre für mich ein Blick auf das SQL und seine Struktur schon wichtig. Aber bei deinen "Blöcken" - oder sollte ich besser Klumpen sagen - kann ich da nichts erkennen. Da ich mir nicht selbst die Mühe machen werde, das in eine lesbare Form zu bringen und zweimalige Bitte um Besserung nichts gebracht hat, bin ich hier raus!
Vlt. geht es nur mir so und deshalb hoffe ich du findest jemand mit mehr Geduld...
Gruß Falk

@Falk
beide Verbesserungen habe ich doch bestens umgesetzt :-)
Spaß bei Seite, ich kann deine Aussage verstehen. Ich wollte nur nicht das gesamte Projekt vorstellen da ich allein dafür 2 Tage benötigen würde.

@Nike77
Ich kann Falks Aussage auch verstehen. Und die "Entschuldigung" von Dir, Nike77, nicht so ganz. Es geht ja nicht darum hier komplette Projekte darzustellen, sondern darum, das Problem darzustellen.

Ich habe folgendes gemacht:
Deine "Blöcke" durch einen Online SQL Formatter geschickt, das Ergebnis durch ein Diff Tool.
Es ergeben sich 3 nahezu identische SQL Statements, die gemäß Klammerung usw offenbar aus dem From Teil einer größeren Abfrage stammen.
Das hat nicht mal eine Minute gedauert und ich musste weder mein Touchpad noch mein Hirn quälen, um festzustellen, das es in dem ganzen Klump nur um 3 verschiedene Typisierungen geht.
Du hättest das vermutlich noch schneller geschafft, weil Du es schon wusstest.

Leider hast du die Where Kritierien mit denen Du deine "Blöcke" joinst nicht mitgeliefert. Schwierig zu optimieren.

Ich kann Dir nur ganz allgemein sagen, dass Datenbanken ab einer gewissen Ergebnismenge, Sortieroperationen nicht mehr im "Kopf", sondern auf der Platte machen. Entsprechende Hinweise dürfte Dir schon ein Ausführungsplan zu den verschiedenen Statements liefern. Mit Konkreten Angaben kann ich Dir bei MSSQL mangels Erfahrung da aber nicht dienen.

@FALK:
Vielleicht kannst Du die Formatierungshinweise mal durch einen Link zu einem Online SQL Formatierer anreichern.


Ach ganz vergessen, nach der Formatierung sieht es so aus, sag bitte einer, dass es keinen Unterschied macht:
[HIGHLIGHT=SQL]
(SELECT String
FROM specs
WHERE (ItemID =
(SELECT Item
FROM Specs s
WHERE s.text IS NULL
AND (s.ItemID=i.ID
OR s.ItemID=i.ParentID
OR s.ItemID=
(SELECT ParentID
FROM Items i2
WHERE i2.ID=i.ParentID)
OR s.ItemID=
(SELECT i3.ParentID
FROM Items i2
LEFT JOIN Items i3 ON i3.ID=i2.ParentID
WHERE i2.ID=i.ParentID)
OR s.ItemID=
(SELECT i4.ParentID
FROM Items i2
LEFT JOIN Items i3 ON i3.ID=i2.ParentID
LEFT JOIN Items i4 ON i4.ID=i3.ParentID
WHERE i2.ID=i.ParentID))
AND s.SpecType=[a|b|c]))
AND (Spectype = '2')
AND (LANGUAGE = 'DE')) AS i_MerkmalEinheit[a|b|c],
[/HIGHLIGHT]
Die Unterschiede der 3 Blöcke habe ich mit [a|b|c] markiert.

[knoxyz]

N'abend
Das ist ja ein Trümmer(haufen) SQL-Statement

Du versucht soetwas, oder?
[highlight=sql]
SELECT
a.ArtNr
,a.ArtBez1
,Preis = (SELECT TOP 1 p.zusatztext
FROM Preis p
WHERE a.id_preis = p.id
)
FROM Artikelstamm a
[/highlight]

Solche Abfragen sind zwar in Ausnahmefällen nützlich, aber sehr langsam und sollten deshalb möglichst nie verwendet werden.

In deiner "OR"-Bedingung versuchst du eine Rekursion durchzuführen und so mehrere Stufen zu durchlaufen.
Versuch dazu selbst eine Lösung zu finden.
Falls du keine findest gebe ich dir eine Mustervorlage.

Mit deiner aktuellen Methode geht's auch, dauert nur länger.
Fest steht, dass du immer nur die ParentID der untersten Stufe vergleichen möchtest. Deshalb stollte aus den "LEFT" ein "INNER" Join gemacht werden.
[highlight=sql]
...
(SELECT String
FROM specs
WHERE
Spectype = '2'
AND LANGUAGE = 'DE'
AND (ItemID = (SELECT Item
FROM Specs s
WHERE s.text IS NULL
AND ( s.ItemID = i.ID
OR s.ItemID = i.ParentID
OR s.ItemID = (SELECT ParentID
FROM Items i2
WHERE i2.ID = i.ParentID
)
OR s.ItemID = (SELECT i3.ParentID
FROM Items i2
INNER JOIN Items i3
ON i3.ID = i2.ParentID
WHERE i2.ID = i.ParentID
)
OR s.ItemID = (SELECT i4.ParentID
FROM Items i2
INNER JOIN Items i3
ON i3.ID = i2.ParentID
INNER JOIN Items i4
ON i4.ID = i3.ParentID
WHERE i2.ID = i.ParentID
)
)
AND s.SpecType=428
)
)

) AS i_MerkmalEinheit11,
...
[/highlight]