Comment faire un T-SQL Curseur plus rapidement?

Hey, j'Ai un curseur dans une procédure stockée sous SQL Server 2000 (pas possible de mettre à jour maintenant) que toutes les mises à jour de la table, mais il prend généralement que quelques minutes à remplir. J'ai besoin pour le rendre plus rapide. Ici l'exemple du tableau filtré par l'arbitraire d'un id de produit;
Exemple de table http://img231.imageshack.us/img231/9464/75187992.jpg
Alors que GDEPO d'Entrée:dépôt, CDEPO:Sortie de depot,d'Adet: quantité,E_CIKAN quantité utilisée.

Enregistrement explications:
1: 20 unité entre depot 01,
2: unité de 10 feuilles de 01.
3: 5 Unité de feuilles 01 (E_CIKAN pour le 1er record de 15 maintenant)
4: 10 plus d'unité entre depot 01.
5: 3 de l'unité de feuilles de 01 à partir du 1er enregistrement. Notez maintenant le 1er enregistrement a E_CIKAN fixé à 18 ans.
6: C'est là que le problème vient de: 3 unité doit quitter depot 01. Il faut que les 2 unité à partir du 1er enregistrement et de 1 unité à partir de la 5ème record. Ma SP peut gérer cette amende comme on le voit dans l'image, sauf que c'est VRAIMENT lent.

Voici la procédure stockée traduits en anglais;

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

Vos requêtes sont à l'aide d'un tas de colonnes qui ne sont pas répertoriés dans votre capture d'écran. Il serait probablement mieux si vous avez inclus le DDL pour la table avec de véritables descriptions des colonnes.
Tom, j'ai traduit la requête en anglais et dit Whereas GDEPO:Entry depot, CDEPO:Exit depot,Adet: quantity,E_CIKAN quantity that's used. après la capture d'écran.
E_CIKAN indique la quantité utilisée (par les documents ci-après) de la quantité. Vérifier d'abord les enregistrements de 3, 20 entrée,10+5 de départ. E_CIKAN pour le 1er record de 15. Une procédure stockée est-ce que cela amende, le problème est qu'il est vraiment lent.
(toutes mes excuses, j'ai été la suppression de mes commentaires que je comprends de plus). OK, je comprends maintenant. Donc E_CIKAN montre comment de nombreux de la ADET ont été utilisés. Donc, il commence à 0 et de votre procédure stockée ajoute à elle. Par conséquent, votre capture d'écran montre les valeurs finales après la procédure.
Vous n'avez pas besoin d'un curseur imbriqué ou même n'importe quel curseur pour faire ce que vous essayez de faire, vous pouvez écrire un peu de mise à jour des déclarations ou si c'est vraiment complexe de l'utilisation d'une table temporaire pour obtenir un calcul, puis faire votre mise à jour. Mais rien de logique-sage dans vos curseurs nécessitent l'utilisation de curseurs.

OriginalL'auteur Ertugrul Kara | 2009-05-13

10

Basé sur notre conversation dans mon autre réponse à cette question, je pense avoir trouvé un moyen d'accélérer la vitesse de votre routine.

Vous avez deux curseurs imbriqués:
- La première est de sélectionner chaque ligne a un exitdepot spécifié. Il prend le produit, depo et la quantité, puis:
- Le curseur interne de la boucle s'exécute à travers les lignes de ce produit/depot qui ont entrydepot spécifié. Il ajoute sur le E_CIKAN pour chacun d'eux, jusqu'à ce qu'il a alloué tous les produits.
De sorte que le curseur interne de la boucle s'exécute au moins une fois pour chaque exitdepot ligne que vous avez. Cependant, votre système ne peut pas vraiment les éléments qui sortait avec qui la transaction, vous êtes seulement en train de calculer la finale E_CIKAN valeurs.

Donc ...

Votre boucle externe n'a besoin que de la total montant des articles expédiés pour chaque produit/dépôt de liste déroulante. Donc vous pouvez changer le curseur extérieur définition:
```
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
```
(et puis aussi modifier la correspondance de CHERCHER le POISSON à la fin du code de match, évidemment)

Cela signifie que votre curseur extérieur aura beaucoup moins de lignes à boucle à travers, et à l'intérieur de votre curseur aura roughtly la même quantité de lignes à boucle à travers.

Donc, cela devrait être plus rapide.

Ressemble beaucoup. Maintenant, c'est plus de deux fois plus rapide. Je vais continuer à le tester demain trop -pour voir s'il convient vraiment-, avant de choisir comme correct answer.
Deux fois plus rapide! cool.
Pour les autres lecteurs: c'est une variante d'un 'total' requête problème, et donc, en fait, pourrait être un de ces rares situations où les curseurs de fournir le plus rapide (fiable) de la solution. Voir cette autre question pour les détails d'ordre général sur sql server en cours d'exécution totaux: stackoverflow.com/questions/860966/...

OriginalL'auteur codeulike
2

Curseurs doivent être les moins performants solution à tout problème lors de l'utilisation de T-SQL.

Vous avez deux options, selon la complexité de ce que vous êtes vraiment essayer de l'accomplir:
1. Tentative de réécrire l'ensemble du code pour utiliser les opérations sur les ensembles. Ce serait le plus rapide à l'exécution de la méthode...mais parfois vous ne pouvez pas le faire à l'aide de jeu activités.
2. Remplacer le curseur avec une combinaison d'une variable de table (avec une colonne identity), le compteur, et le tout en boucle. Vous pouvez ensuite parcourir chaque ligne du tableau de la variable. Plus performant que d'un curseur...même si cela peut ne pas sembler comme il l'aurait fait.
Je n'ai aucune idée de comment le faire par mise à JOUR (même avec la mise à jour à partir de) en raison de 6ème de l'enregistrement de la clause dans la capture d'écran. On dirait que je vais devoir le faire de la 2ème façon dont vous l'avez mentionné.
Alors, Comment pouvons-nous passer à travers les lignes de la table?

OriginalL'auteur Justin Niessner
1

Supprimer le curseur et faire des mises à jour par lot. Je n'ai pas encore trouvé une mise à jour qui ne peut pas être fait dans le lot.

OriginalL'auteur Slim
1

supprimer le curseur et de réécriture que comme une mise à JOUR DE se joindre à la le curseur de la requête, vous pouvez faire de l'IFs cas, si vous en avez besoin. Je suis trop occupé à écrire aujourd'hui la mise à JOUR pour vous aujourd'hui...

OriginalL'auteur KM.
1

Tout d'abord, si vous DEVEZ utiliser un curseur, et vous êtes à la mise à jour des trucs, puis de déclarer le curseur avec la clause FOR UPDATE. (Voir l'exemple ci-dessous. Notez que l'exemple n'est PAS basé sur votre code.)

Cela dit, il existe une multitude de façons d'utiliser autre chose que les curseurs, souvent en tirant parti des tables temporaires. Je voudrais d'enquêter sur la route au lieu de curseurs.
```
DECLARE LoopingCursor CURSOR LOCAL DYNAMIC
FOR
    select sortorder from customfielddefinition
    where context=@targetContext
FOR UPDATE OF sortorder
```
J'ai essayé celui-ci, mais en quelque sorte elle soulève d'erreur suivant bien que j'ai de la colonne d'identité en sélectionner une partie.. FOR UPDATE cannot be specified on a READ ONLY cursor.
J'ai enlevé RAPIDEMENT en AVANT aussi, toujours pas de chance. Je pense que c'est parce que de SQL2000.. Mais la question est, depuis que je suis en utilisant @ID à partir du curseur déjà, serait-il vraiment faire une différence si j'utilise ACTUELLES DE SH?

OriginalL'auteur Garrett
1

Je peux voir que le problème que vous essayez de résoudre est assez compliqué:
- Quand il y a une ligne avec GDEPO spécifié, il représente l'action va dans le depo, et que vous voulez utiliser la E_CIKAN de ligne pour suivre la façon dont beaucoup de stock est utilisé plus tard. E_CIKAN va commencer à 0 et ensuite ajoutés à des stocks qui s'en va jusqu'à ce qu'il atteigne ADET.
- Donc quand il y a une rangée suivante avec CDEPO spécifié, il représente le stock de sortir, et que vous voulez revenir à E_CIKAN de la GDEPO de la ligne et de régler la E_CIKAN, en ajoutant la quantité de rupture de stock.
- Quand il y a eu deux lignes séparées avec des actions allant dans (GDEPO spécifié), parfois il y a un dépassement de capacité lors de la E_CIKAN d'une ligne atteint max (ADET) et que vous souhaitez ajouter le reste à la suivante.
C'est assez difficile de calcul parce que vous avez à comparer les différentes lignes et de revenir en arrière et modifier les valeurs dans peut-être un ou peut-être deux lignes à suivre chaque transaction de stock.

Il peut être un moyen de le faire sans un curseur, alors que d'autres sont en train de suggérer. Mais je pense que si vous pouviez re-organiser vos tables et de stocker les données d'une manière différente, vous pourriez être en mesure de faire plus facilement le problème.

Par exemple, au lieu de garder une trace de stock dans la même table qui enregistre les transactions boursières, pourriez-vous avoir une table séparée avec 'Produit_id, Depo_id, le montant' des colonnes qui garde une trace de la quantité totale de chaque produit dans chaque depo à la fois?

Un changement de conception de base de données telle que celle qui pourrait rendre les choses plus faciles.

Ou ... au lieu d'utiliser E_CIKAN de garder une trace de ce que est utilisé, utiliser pour garder une trace de ce qui reste. Et de garder un E_CIKAN valeur dans chaque ligne. Donc, chaque fois que le stock va dans ou hors d'un depo, re-calculer E_CIKAN à ce point dans le temps et de le stocker dans la transaction de ligne (au lieu d'essayer de revenir en arrière à l'original " de stock en ligne et mise à jour). Ensuite pour trouver le stock actuel, il suffit de regarder les plus récentes transcation pour ce produit/depo.

En résumé, ce que je dis est, votre calcul est lente et lourde, parce que vous êtes de stocker les données dans une étrange manière. Dans le long terme, il pourrait être intéressant de changer votre conception de base de données pour faciliter la programmation.

Exactement, vous avez décrit le problème mieux que moi ;). Maintenant, le point est, cette procédure stockée est utilisée lorsqu'une mise à jour est faite pour les enregistrements au passé - sinon, je n'ai même pas l'utiliser du tout comme je le fais re-calculer E_CIKAN à de nouveaux records. Je tiens à vous remercier pour la conception de la base de recommandations, mais c'est quelque chose que je ne peux pas le permettre maintenant.
OK, donc, normalement, vous re-calculer E_CIKAN chaque transaction se produit, mais votre procédure stockée est utilisée de temps en temps quand vous devez revenir en arrière et modifier les précédentes transactions boursières? Maintenant que je comprends l'algorithme, je pourrais peut-être arriver à quelque chose ... mais d'abord, une grande tasse de thé ....
OK, j'ai eu une tasse de thé et de venir avec une suggestion: voir ma réponse alternative ci-dessous

OriginalL'auteur codeulike

Vous devez vous connecter pour publier un commentaire.