Gespeicherte SQL-Prozedur temporäre Tabelle Speicher problem
Wir haben die folgenden einfache Gespeicherte Prozedur, die ausgeführt wird als eine über-Nacht-SQL server-agent-Auftrag. In der Regel läuft es in 20 Minuten, aber in letzter Zeit MatchEvent und MatchResult-Tabellen wird mittlerweile auf über 9 Millionen Zeilen jeweils. Dies führte in der Prozedur speichern unter über 2 Stunden zu laufen, mit allen 8 GB Speicher auf unseren SQL-box verwendet wird. Das macht die Datenbank nicht verfügbar ist, um die regelmäßige Abfrage, die versuchen, darauf zuzugreifen.
Ich nehme an, das problem ist, dass die temp-Tabelle ist zu groß und verursacht die Speicher-und Datenbank-unavailablity Probleme.
Wie kann ich schreiben Sie die gespeicherte Prozedur, um es effizienter und weniger speicherintensiv?
Hinweis: ich bearbeitet die SQL zu zeigen, dass es kommen Krankheit, die die erste SELECT-Anweisung. Ich hatte vorher Links das für die Einfachheit. Auch, wenn die Abfrage ausgeführt wird die CPU-Auslastung ist bei 1-2%, aber memoery, wie bereits erwähnt, ist ausgereizt
CREATE TABLE #tempMatchResult
(
matchId VARCHAR(50)
)
INSERT INTO #tempMatchResult SELECT MatchId FROM MatchResult WHERE SOME_CONDITION
DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM #tempMatchResult)
DELETE FROM MatchResult WHERE MatchId In (SELECT MatchId FROM #tempMatchResult)
DROP TABLE #tempMatchResult
InformationsquelleAutor Robin Weston | 2008-10-23
Du musst angemeldet sein, um einen Kommentar abzugeben.
Gibt es wahrscheinlich eine Menge Dinge hier, und es ist nicht alle Ihre Abfrage.
Erstens, ich Stimme mit den anderen Plakaten. Versuchen Sie zu umschreiben, dass diese ohne eine temp-Tabelle, wenn überhaupt möglich.
Aber davon aus, dass Sie eine temp-Tabelle hier haben Sie ein GROßES problem, dass Sie keine PK definiert. Es ist gewaltig erweitert die Menge der Zeit, die Ihre Anfragen nehmen zu führen. Erstellen Sie Ihre Tabelle wie so anstelle:
Stellen Sie außerdem sicher, dass Ihre TempDB-Größe ist korrekt. Ihre SQL server-können sehr gut sein, erweitern Sie die Datenbank-Datei, die dynamisch auf Sie, wodurch Sie Ihre Abfrage zu saugen, CPU-und Datenträger-Zeit. Auch, stellen Sie sicher, dass Ihr Transaktions-log ist richtig dimensioniert, und es ist nicht auto-Anbau auf Sie. Viel Glück.
Versuchen Sie auch, um inner join temp-Tabelle in die echte Tabelle über die PK für löschen, anstatt die
WHERE [..] IN ([..])
so haben SieDELETE me FROM MatchEvent me JOIN #tempMatchResult tmpmr ON tmpmr.MatchId = me.MatchId
.InformationsquelleAutor Dave Markle
Blick auf den code oben, warum brauchst du eine temp-Tabelle?
InformationsquelleAutor shahkalpesh
Werden Sie wahrscheinlich wollen, um diesen stückweise in gewisser Weise. (Ich nehme an, Anfragen sind viel komplizierter, dass Sie zeigte?) In diesem Fall würden Sie wollen, versuchen Sie eine dieser:
LIMIT 100
und verarbeiten diese.In Postgres, ich habe einige Erfolge mit der Verwendung bedingter Indizes. Sie arbeiten Magie, die durch die Anwendung eines index, wenn bestimmte Bedingungen erfüllt sind. Dies bedeutet, dass Sie können, um die vielen 'aufgelöst' und die wenigen ungelösten Zeilen in der gleichen Tabelle, aber immer noch dieses speziellen index nur über die ungelösten lieben. Ymmv.
Sollte darauf hingewiesen werden, dass dies ist, wo die Verwendung von Datenbanken bekommt interessante. Sie müssen achten Sie auf Ihre Indizes und verwenden
EXPLAIN
auf Ihre Fragen eine Menge.(Oh, und denken Sie daran, interessante ist eine gute Sache in Ihre Hobbys, aber nicht bei der Arbeit.)
Dies kann hilfreich sein, wenn die anderen Optionen nicht beheben, die Verriegelung Probleme. Wenn Ihr exklusive Sperre von der DELETE-Anweisung ist immer noch zu lange, versuchen Sie die Ausführung in ansehnliche Stücke (das ist, wenn es erfüllt die Anforderungen der Unternehmen, die nicht angegeben sind, die in Ihrer Frage). Ich habe dazu bei E-Commerce-Geschäfte, wo der nächtlichen Wartung wurde durchgeführt, ohne Unterbrechung der Fahrtrichtung user experience. Art der Priorisierung der Schleusen durch das query-Strategie.
InformationsquelleAutor Anders Eurenius
Ersten, Indizes sind hier ein MUSS sehen, Dave M ' s Antwort.
Anderen Ansatz, den ich irgendwann verwenden beim löschen von sehr großen Datenmengen, ist eine shadow-Tabelle mit allen Daten, Neuerstellung der Indizes und dann mithilfe von sp_rename zu wechseln. Sie müssen vorsichtig sein mit Transaktionen hier, aber abhängig von der Menge der Daten, die gelöscht werden, kann dies schneller sein.
Hinweis, Wenn es Druck auf tempdb sollten Sie mithilfe von joins und nicht das kopieren aller Daten in die temp Tabelle.
So zum Beispiel
InformationsquelleAutor Sam Saffron
Vermeiden Sie die temp-Tabelle, wenn möglich
Ist es nur mit dem Speicher.
Sie könnten versuchen, diese:
Wenn Sie nicht vermeiden können, in eine temp-Tabelle
Ich werde bleiben mein Hals raus und sagen: Sie nicht brauchen, einen index für die temporäre Tabelle, weil Sie wollen, dass die temp-Tabelle zu den kleinsten Tisch in der Gleichung, und Sie möchten, um die Tabelle zu Scannen (da alle Zeilen relevant sind). Ein index wird nicht helfen, Sie hier.
Kleine bits der Arbeit
Arbeit auf ein paar Zeilen zu einer Zeit.
Dies wird wahrscheinlich verlangsamen die Ausführung, aber es sollten mehr Ressourcen frei.
- Eine Zeile zu einem Zeitpunkt
- Ein paar Zeilen zu einem Zeitpunkt
Diese eine löscht bis zu 1000 Zeilen gleichzeitig.
Je mehr Zeilen, die Sie löschen zu einer Zeit, die mehr Ressourcen, die Sie verwenden, aber desto schneller wird es laufen neigen (bis Sie die Ressourcen ausgehen!). Sie können Experimentieren, um einen optimalen Wert als 1000.
InformationsquelleAutor AJ.
ersetzt werden kann, mit
SOME_CONDITION
Teil zweimal. Wenn es eine große Abfrage, diese wird in weniger performant.InformationsquelleAutor Philip Wade
Können Sie einfach drehen Sie löschweitergaben zwischen matchresult und matchevent? Dann müssen Sie nur sorgen über die Identifizierung einer Reihe von Daten zu löschen, und lassen Sie SQL kümmern sich um die anderen.
Die alternative wäre, nutzen Sie die OUTPUT-Klausel, aber das ist definitiv mehr Geige.
Beiden würde lassen Sie Sie löschen aus beiden Tabellen, sondern nur für Staat (und ausführen), Ihre filter-Prädikat einmal. Dies kann noch nicht so performant wie eine Dosier-Ansatz, wie vorgeschlagen, von den anderen Postern, aber eine überlegung Wert. YMMV
InformationsquelleAutor piers7