Wie man ein T-SQL-Cursor schneller?

Hey, ich Habe einen cursor in gespeicherten Prozeduren unter SQL Server 2000 (nicht möglich auf jetzt aktualisieren), die Aktualisierung der Tabelle, aber es dauert in der Regel einige Minuten. Ich brauche, um es schneller zu machen. Hier die Beispiel Tabelle gefiltert durch einen beliebigen Produkt-id;
Beispiel-Tabelle http://img231.imageshack.us/img231/9464/75187992.jpg
In der Erwägung, dass GDEPO:Eintrag depot, CDEPO:Ausfahrt depot,Zahl: die Zahl,E_CIKAN Menge, die verwendet wird.

Datensatz Erläuterungen:

1: 20 Einheit betritt depot 01,
2: 10 Einheit verlässt, 01.
3: 5 Unit Blätter 01 (E_CIKAN für den 1. Datensatz wird 15 jetzt)
4: 10 mehr Einheit betritt depot 01.
5: 3 Einheit verlässt, 01 ab dem 1. Datensatz. Jetzt sehen Sie den 1. Datensatz hat E_CIKAN Satz zu 18.
6: Dies ist, wo das problem kommt in: 3 Einheit verlassen muss depot 01. Es dauert 2-Einheit 1. Datensatz und 1 Einheit ab 5. Datensatz. Mein SP kann damit umgehen fein wie gesehen, in Bild, außer es ist WIRKLICH langsam.

Hier ist die gespeicherte Prozedur, die ins englische übersetzt;

CREATE PROC [dbo].[UpdateProductDetails]
as
UPDATE PRODUCTDETAILS SET E_CIKAN=0;
DECLARE @ID int
DECLARE @SK varchar(50),@DP varchar(50)  --SK = STOKKODU = PRODUCTID, DP = DEPOT
DECLARE @DEMAND float     --Demand=Quantity, We'll decrease it record by record
DECLARE @SUBID int
DECLARE @SUBQTY float,@SUBCK float,@REMAINS float
DECLARE SH CURSOR FAST_FORWARD FOR
SELECT [ID],PRODUCTID,QTY,EXITDEPOT FROM PRODUCTDETAILS  WHERE (EXITDEPOT IS NOT NULL) ORDER BY [DATE] ASC
OPEN SH
FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP

WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE SA CURSOR FAST_FORWARD FOR
   SELECT [ID],QTY,E_CIKAN FROM PRODUCTDETAILS  WHERE (QTY>E_CIKAN) AND (PRODUCTID=@SK) AND (ENTRYDEPOT=@DP) ORDER BY [DATE] ASC
   OPEN SA
   FETCH NEXT FROM SA INTO @SUBID, @SUBQTY,@SUBCK
   WHILE (@@FETCH_STATUS = 0) AND (@DEMAND>0)
   BEGIN
      SET @REMAINS=@SUBQTY-@SUBCK
      IF @DEMAND>@REMAINS  --current record isnt sufficient, use it and move on
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=QTY WHERE ID=@SUBID;
         SET @DEMAND=@DEMAND-@REMAINS
      END
      ELSE
      BEGIN
         UPDATE PRODUCTDETAILS SET E_CIKAN=E_CIKAN+@DEMAND WHERE ID=@SUBID;
         SET @DEMAND=0
      END
      FETCH NEXT FROM SA INTO @SUBID, @SUBAD,@SUBCK
   END
   CLOSE SA
   DEALLOCATE SA
   FETCH NEXT FROM SH INTO @ID, @SK,@DEMAND,@DP
END
CLOSE SH
DEALLOCATE SH

Ihre Anfragen werden mit einer Reihe von Spalten, die nicht aufgeführt sind in deinem Screenshot. Es wäre wahrscheinlich besser, wenn Sie die DDL für die Tabelle zusammen mit den aktuellen Beschreibungen der Spalten.
Tom, ich habe übersetzt Abfrage in Englisch und sagte Whereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's used. nach screenshot für Sie.
E_CIKAN zeigt die Menge, die verwendet wird (indem Sie die folgenden Datensätze) aus der Menge. Überprüfen Sie die ersten 3 Einträge, 20 Einstieg,10+5 Abfahrt. E_CIKAN für den 1. Datensatz wird 15 dann. Gespeicherte Prozedur tut dies nur gut, problem ist, es ist wirklich langsam.
(Entschuldigung, ich habe das löschen meiner Kommentare, da verstehe ich mehr). OK, ich bekomme es jetzt. So E_CIKAN zeigt wie viele der ZAHL verwendet wurden. So ist es bei 0 beginnt und dann die gespeicherte Prozedur fügt es. Also dein screenshot zeigt die letzten Werte, nachdem die Prozedur ausgeführt wurde.
Sie brauchen nicht eine geschachtelte cursor oder sogar einem cursor zu tun, was Sie versuchen zu tun, Sie können schreiben ein paar update-Anweisungen oder wenn seine wirklich komplexe mithilfe einer temp-Tabelle abzuleiten, manche berechnen dann tun Sie Ihr update. Aber nichts-Logik-Weise in Ihren Cursor erfordern die Verwendung von Cursorn.

InformationsquelleAutor Ertugrul Kara | 2009-05-13

10

Basierend auf unserem Gespräch in meiner anderen Antwort auf diese Frage, ich denke ich habe einen Weg gefunden, um die Geschwindigkeit Ihrer routine.

Haben Sie zwei geschachtelte Cursorn:
- Die erste ist die Auswahl, jede Zeile, die eine exitdepot angegeben. Er nimmt das Produkt, depo und Menge, und dann:
- Der innere cursor-Schleife läuft über die Zeilen für das Produkt/depot, die haben entrydepot angegeben. Sie fügt hinzu, auf die E_CIKAN für jeden, bis er reserviert hat, alle Produkt.
Also der innere cursor-Schleife mindestens einmal ausgeführt wird für jeden exitdepot Zeile, die Sie haben. Das system ist jedoch nicht wirklich wichtig ist, welche items ging mit dem Transaktion - Sie sind nur versuchen, zu berechnen, die endgültige E_CIKAN Werte.

So ...

Ihre äußere Schleife nur benötigt um die insgesamt Menge der gelieferten Elemente für jedes Produkt/depot-combo. Daher kann man diese änderung der äußeren cursor-definition:
```
DECLARE SH CURSOR FAST_FORWARD FOR
    SELECT PRODUCTID,EXITDEPOT, Sum(Qty) as TOTALQTY
    FROM PRODUCTDETAILS  
    WHERE (EXITDEPOT IS NOT NULL) 
    GROUP BY PRODUCTID, EXITDEPOT
OPEN SH
FETCH NEXT FROM SH INTO @SK,@DP,@DEMAND
```
(und dann auch die passenden FETCH-von SH am Ende des Codes zu entsprechen, offensichtlich)

Bedeutet dies, dass Ihre äußeren cursor haben viele weniger Zeilen in einer Schleife durch, und Ihre innere cursor haben roughtly die gleiche Menge von Zeilen in einer Schleife durch.

Also das sollte schneller sein.
- Sieht toll aus. Jetzt ist es mehr als doppelt so schnell. Ich werde es weiter testen, es auch morgen zu sehen, ob es wirklich passt-vor der Wahl wie correct answer.
- Mehr als doppelt so schnell! cool.
- Für andere Leser: dies ist eine Variante des 'laufende Summe' query problem, und so könnte tatsächlich eine jener seltenen Situationen, in denen cursors bieten die Schnellste (zuverlässige) Lösung. Die andere Frage ist für Allgemeine Angaben über SqlServer Gesamtsummen: stackoverflow.com/questions/860966/...
InformationsquelleAutor codeulike
2

Cursor zu der schlechtesten Lösung für jedes problem bei der Verwendung von T-SQL.

Haben Sie zwei Möglichkeiten, je nach Komplexität dessen, was Sie wirklich versuchen zu erreichen:
1. Versuch, zu schreiben, den gesamten code zu verwenden, set-Operationen. Dies wäre die Schnellste Methode durchführen...aber manchmal kann man einfach nicht tun es mit set-Operationen.
2. Ersetzen Sie den cursor mit einer Kombination von Tabelle-variable (mit einer identity-Spalte), Zähler -, und while-Schleife. Sie können dann in einer Schleife durch jede Zeile der Tabelle variabel. Führt besser als ein cursor...auch wenn es nicht scheinen, wie es wäre.
- Ich habe keine Ahnung, wie es zu tun, die durch das UPDATE (auch mit update aus) wegen 6. record-Klausel im screenshot. Scheint, wie ich haben werde, es zu tun den 2. Weg, die Sie erwähnt.
- Dann, Wie können wir gehen Sie durch die Zeilen der Tabelle?
InformationsquelleAutor Justin Niessner
1

Entfernen Sie den cursor und machen batch-updates. Ich habe noch auf ein update, das kann nicht getan werden, in batch.

InformationsquelleAutor Slim
1

entfernen Sie den cursor und umschreiben als ein UPDATE VON einem Beitritt in die Sie den cursor der Abfrage, können Sie die IFs Fall, wenn Sie benötigen. Ich bin zu beschäftigt, heute schreiben Sie das UPDATE für Sie heute...

InformationsquelleAutor KM.
1

Ersten, wenn Sie MÜSSEN, verwenden Sie einen cursor, und Sie aktualisieren Zeug, dann erklären Sie den cursor mit der FOR UPDATE-Klausel. (Beispiel siehe unten. Beachten Sie, dass das Beispiel beruht NICHT auf Ihrem code, an alle.)

Having said that, gibt es eine Vielzahl von Möglichkeiten, um etwas anderes als Cursor, Häufig Nutzung von temporären Tabellen. Ich würde untersuchen, die Strecke statt der Cursor.
```
DECLARE LoopingCursor CURSOR LOCAL DYNAMIC
FOR
    select sortorder from customfielddefinition
    where context=@targetContext
FOR UPDATE OF sortorder
```
- Ich habe versucht, diesen einen, aber irgendwie wirft er folgenden Fehler obwohl ich identity-Spalte im select-Teil.. FOR UPDATE cannot be specified on a READ ONLY cursor.
- Ich habe entfernt SCHNELL VORWÄRTS zu, noch kein Glück. Ich denke, es ist, weil der SQL2000.. Aber die Frage ist, da bin ich mit @ID von der cursor schon, würde es wirklich Unterschied, ob ich einen WHERE CURRENT OF-SH?
InformationsquelleAutor Garrett
1

Kann ich sehen, dass das problem, das Sie versuchen zu lösen, ist ziemlich kompliziert:
- Wenn es eine Zeile mit GDEPO angegeben, es stellt Aktie geht in die depo, und Sie möchten, verwenden Sie die E_CIKAN von die Zeile zu verfolgen, wie viel von der stock wird später verwendet. E_CIKAN wird bei 0 beginnen und dann Hinzugefügt-bis der Vorrat aus geht, bis es erreicht ADET.
- So, wenn es eine weitere Zeile mit CDEPO angegeben ist, ist die Aktie geht aus, und Sie wollen zurück zu gehen, um E_CIKAN der GDEPO-Zeile und stellen Sie die E_CIKAN, indem die Menge von stock-out-es.
- Wenn es zwei separate Zeilen, die mit der Aktie geht in (GDEPO angegeben), manchmal ein überlauf vorhanden ist, wenn die E_CIKAN einer Zeile erreicht max (ADET), und Sie möchten, fügen Sie den Rest auf die nächste.
Dies ist eine Recht komplizierte Berechnung, weil Sie haben, zu vergleichen verschiedene Zeilen und gehen Sie zurück und ändern Sie die Werte in vielleicht einer oder vielleicht zwei Zeilen zu verfolgen, die einzelnen Bestände Transaktion.

Dort kann einen Weg, das zu tun, ohne einen cursor, wie andere es vorschlagen. Aber ich glaube, wenn Sie könnte, wieder ordnen Sie Ihre Tabellen und speichern Sie die Daten in eine andere Weise werden Sie in der Lage sein könnte, um das problem zu vereinfachen.

Zum Beispiel, anstatt zu halten Spur von Aktien in der gleichen Tabelle, Datensätze, die Börsengeschäfte, könnte Sie eine separate Tabelle mit "Product_id, Depo_id, Anzahl' Spalten, die Spur hält der Gesamtbetrag der jedes Produkt in jedem depo auf einmal?

Einen Datenbank-design ändern, wie das könnte die Dinge einfacher.

... Oder anstatt E_CIKAN zu verfolgen, was verwendet wird, verwenden Sie es zu verfolgen was bleibt. Und halten Sie ein E_CIKAN Wert in jeder Zeile. So, Wann auch immer Aktie geht in einen oder aus einem depo, neu zu berechnen, E_CIKAN an diesem Punkt in der Zeit und bewahren Sie Sie in der Transaktion Zeile (anstatt zu versuchen, gehen Sie zurück zu den ursprünglichen "Bestand in" Reihe und es zu aktualisieren gibt). Dann finden Sie heraus, den aktuellen Bestand, den Sie gerade Aussehen bei der letzten transcation für das Produkt/depo.

In der Zusammenfassung, was ich sage, ist, Ihre Berechnung ist langsam und umständlich, da Sie die Speicherung der Daten in einer seltsamen Art und Weise. Langfristig könnte es sich lohnen, ändern Sie Ihre Datenbank-design, um die Programmierung zu erleichtern.
- Genau, Sie beschrieb das problem besser als ich ;). Jetzt ist der Punkt, dass diese gespeicherte Prozedur wird verwendet, wenn eine Aktualisierung erfolgt, um die Datensätze in die Vergangenheit - sonst weiß ich auch nicht verwenden, es überhaupt nicht, wie ich tun, neu zu berechnen, E_CIKAN auf neue Rekorde. Ich möchte Ihnen danken für Datenbank-design-Empfehlungen, aber es ist etwas, dass ich kann nicht leisten, jetzt.
- OK, also normalerweise ist Sie neu zu berechnen, E_CIKAN da jede Transaktion passiert, aber die gespeicherte Prozedur wird gelegentlich verwendet, wenn Sie müssen gehen Sie zurück und ändern frühere Börsengeschäfte? Nun, ich verstehe den Algorithmus, ich könnte in der Lage zu kommen mit etwas ... aber Erstens, eine große Tasse Tee benötigt wird ....
- OK, ich hatte eine Tasse Tee und kommen mit einem Vorschlag - siehe meine alternative Antwort unten
InformationsquelleAutor codeulike

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.