SQL Server 2008 Existe-t-il un moyen plus efficace de réaliser cette boucle de mise à jour ?

Question

Première question postée, je m'excuse par avance pour les éventuelles maladresses. La table contient des enregistrements qui sont affectés à une équipe, les affectations initiales sont effectuées avec un autre processus. Il arrive fréquemment que nous devions réaffecter les enregistrements d'un agent et les répartir équitablement entre les autres membres de l'équipe. Nous le faisions à la main, un par un, ce qui était fastidieux. J'ai donc trouvé cette solution :

    DECLARE @UpdtAgt TABLE (ID INT, Name varchar(25))
    INSERT INTO @UpdtAgt
    VALUES (1, 'Gandalf')
          ,(2,'Hank')
          ,(3,'Icarus')

   CREATE TABLE #UpdtQry (TblID varchar(25))
   INSERT INTO #UpdtQry
   SELECT ShtID
   FROM TestUpdate

   DECLARE @RowID INT
   DECLARE @AgtID INT
   DECLARE @Agt varchar(25)
   DECLARE @MaxID INT
   SET @MaxID = (SELECT COUNT(*) FROM @UpdtAgt)
   SET @AgtID = 1

   --WHILE ((SELECT COUNT(*) FROM #UpdtQry) > 0)
   WHILE EXISTS (SELECT TblID FROM #UpdtQry)

   BEGIN
    SET @RowID = (SELECT TOP 1 TblID FROM #UpdtQry)
    SET @Agt = (SELECT Name FROM @UpdtAgt WHERE ID = @AgtID)

    UPDATE TestUpdate
    SET Assignment = @Agt
    WHERE ShtID = @RowID

    DELETE #UpdtQry WHERE TblID = @RowID

    IF @AgtID < @MaxID
        SET @AgtID = @AgtID + 1
    ELSE 
        SET @AgtID = 1

   END

   DROP TABLE #UpdtQry

C'est vraiment ma première tentative de faire quelque chose d'aussi approfondi. Une mise à jour de 100 lignes prend environ 30 secondes. La table UPDATE, TestUpdate, ne possède que l'index CLUSTERED. Comment puis-je rendre cette opération plus efficace ?

EDIT : Je n'ai pas très bien défini les tables @UpdtAgt et #UpdtQry dans mon explication. @UpdtAgt contiendra les agents à qui sont réassignés les enregistrements, et changera probablement à chaque fois que cela sera utilisé. La table #UpdtQry contiendra une clause WHERE pour définir les agents dont les enregistrements seront réaffectés, et là encore, elle changera à chaque utilisation. J'espère que cela rend les choses un peu plus claires. Encore une fois, je m'excuse de ne pas avoir bien compris du premier coup.

EDIT 2 : J'ai commenté l'ancienne clause WHILE et inséré celle que HABO a suggérée. Merci encore à HABO.

Answer 1

Je pense que c'est ce que vous cherchez :

DECLARE @UpdtAgt TABLE 
(
  ID INT, 
  Name VARCHAR(25)
)

INSERT @UpdtAgt
VALUES (1, 'Gandalf')
      ,(2, 'Hank')
      ,(3, 'Icarus')

UPDATE t
SET t.Assignment = a.Name
FROM TestUpdate AS t
INNER JOIN @UpdtAgt AS a
    ON t.ShtID = a.ID

Cela devrait faire les 4 rangs en même temps.

P.S...

Si vous créez à l'avenir des tableaux comme dans votre message original, essayez de garder le nom de vos colonnes et variables cohérent avec leur but !

Dans votre exemple, vous avez utilisé ID , AgtID y ShtID et (le plus déroutant) TblID (et je pense qu'il s'agit de la même chose [merci de me corriger si je me trompe !]). Si vous l'avez appelé AgtID partout (et @AgtID pour la variable [Il n'y a pas vraiment besoin de @RowID ), il serait alors beaucoup plus facile de voir d'un coup d'œil ce qui se passe ! Il en va de même avec Assignment y Name .

Answer 2

Puisqu'il s'agit de votre première tentative pour quelque chose de ce genre, je tiens à vous féliciter pour quelque chose qui fonctionne. Même si ce n'est pas idéal (et qu'est-ce qui l'est ?), cela répond à l'objectif principal : cela fonctionne. Il existe une meilleure façon de procéder en utilisant ce que l'on appelle un curseur. Je me rappelle la syntaxe correcte en utilisant la page suivante de Microsoft : Cliquez ici pour des instructions complètes sur les curseurs

Ceci dit, le code à la fin de ce post montre ma solution rapide à votre situation. Notez ce qui suit :

1. Le site @TestUpdate est définie afin que la requête s'exécute dans MSSQL sans utiliser de tables permanentes.
2. Seul le @UpdtAgt doit être configurée comme une table temporaire. Cependant, si elle est utilisée régulièrement, il serait préférable d'en faire une table permanente.
3. Le site CLOSE y DEALLOCATE Les déclarations à la fin sont IMPORTANT - L'oubli de ces derniers aura des conséquences plutôt désagréables.

DECLARE @TestUpdate TABLE (ShtID int, Assignment varchar(25))

INSERT INTO @TestUpdate
   VALUES (1,'Fred')
  ,(2,'Barney')
  ,(3,'Fred')
  ,(4,'Wilma')
  ,(5,'Betty'),(6,'Leopold'),(7,'Frank'),(8,'Fred')

DECLARE @UpdtAgt TABLE (ID INT, Name varchar(25))

INSERT INTO @UpdtAgt
VALUES (1, 'Gandalf')
      ,(2,'Hank')
      ,(3,'Icarus')

DECLARE @recid int
DECLARE @AgtID int SET @AgtID=0
DECLARE @MaxID int SET @MaxID = (SELECT COUNT(*) FROM @UpdtAgt)

DECLARE assignment_cursor CURSOR
FOR SELECT ShtID FROM @TestUpdate

OPEN assignment_cursor
FETCH NEXT FROM assignment_cursor
INTO @recid

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @AgtID = @AgtID + 1
  IF @AgtID > @MaxID SET @AgtID = 1

  UPDATE @TestUpdate
  SET Assignment = (SELECT TOP 1 Name FROM @UpdtAgt WHERE ID=@AgtID)
  FROM @TestUpdate TU   
  WHERE ShtID=@recid

  FETCH NEXT FROM assignment_cursor INTO @recid
END

CLOSE assignment_cursor
DEALLOCATE assignment_cursor

SELECT * FROM @TestUpdate