MISE À JOUR du CTE

Je suis en train de mettre à jour une table à partir du résultat d'une auto-jointure via un CTE. Lorsque vous effectuez une sélection pour afficher les résultats attendus de la mise à jour, il est capable de renvoyer les résultats en 16 secondes.

Cependant, lorsque j'utilise la même syntaxe pour essayer de mettre à jour la table sous-jacente, elle est extrêmement lente.

Pour la vie de moi, je ne peux pas comprendre pourquoi un tel ralentissement dramatique. Je mets fréquemment à jour les tables via CTE et il est habituellement adequetely rapide et raisonnable par rapport à la sélection équivalente.

J'ai essayé avec et sans un PrimaryKey / Clustered index sur la table sous-jacente et cela ne fait aucune différence.

La jointure est sur une colonne calculée et ne peut donc pas être indexée.

Si la différence de time entre SELECT et UPDATE était double, cela ne serait pas un problème. Le problème ici est l'ampleur de l'augmentation du time lors du passage de la sélection à la mise à jour. 16 secondes pour la sélection avec un résultat de 1323 lignes, pour mettre à jour seulement 2 de ces lignes prend 59 secondes, pour mettre à jour 4 prend 1 min 19 secondes et mettre à jour 6 prend 1 min 39 secondes (donc il semble que chaque ligne supplémentaire ajoute 10 secondes ).

Quelqu'un peut-il nous éclairer là-dessus et suggérer un moyen d'accélérer cela?

Voici l'exemple de code:

;WITH CTE AS (SELECT DENSE_RANK() OVER (Order by col1, col2, col3) SetID, COUNT(*) OVER (partition by col1, col2, col3) DupsInSet, row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY col4 desc) RowInSet, COUNT(col4) OVER (partition by col1, col2, col3) NonNull, * FROM mytable)

–Le suivant se termine en 16 secondes et renvoie 1323 lignes

  select b.col4,a.* from cte a join cte b on b.SetID=a.SetID where a.DupsInSet>1 and a.NonNull>0 and b.RowInSet=1 and a.RowInSet>1 and b.col4 is not null and a.col4 is null

–Mise à jour de cette opération pendant si longtime que je ne l'ai même pas laissé finir – Comme un test j'ai limité la mise à jour au Top 2. Cela a pris alors 59 secondes juste pour mettre à jour 2 lignes

  UPDATE TOP(2) a SET a.col4=b.col4 from cte a join cte b on b.SetID=a.SetID where a.DupsInSet>1 and a.NonNull>0 and b.RowInSet=1 and a.RowInSet>1 and b.col4 is not null and a.col4 is null

Plan d'exécution réel pour SELECT https://www.brentozar.com/pastetheplan/?id=ByItSnhuW

Plan d'exécution réel pour UPDATE https://www.brentozar.com/pastetheplan/?id=Ske_In3_Z

METTRE À JOUR:

Prenant la suggestion de SqlZim il a couru pendant plus de 4 minutes sans finir à quel point je l'ai arrêté.

Cependant, avec

un changement dans le schéma (merci SqlZim) de plusieurs colonnes VARCHAR (MAX) jusqu'à VARCHAR (?) où? est le maximum (len (colonne))

quelques modifications à SqlZims suggestion de requête, la mise à jour a pu fonctionner en 23 secondes! … ce qui est environ 3,300 fois plus rapide 🙂

Voici la dernière requête (à less que quelqu'un puisse la faire fonctionner sans avoir à listr toutes les colonnes darn, par exemple en se joignant au SetID):

(Notez que le CTE est toujours utilisé pour filterr la table d'origine de ses 600k + lignes)

 ;WITH CTE AS (SELECT COUNT(*) OVER (partition by [col1] ,[col2] ,[col3] ,[col4] ,[col5] ,[col6] ,[col7] ,[col8] ,[col9] ,[col10] ,[col11] ,[col12] ,[col13] ,[col14] ,[col15] ,[col16] ,[col17] ,[col18] ,[col19] ,[col20] ,[col21] ,[col22] ,[col23]) DupsInSet, COUNT(col24) OVER (partition by [col1] ,[col2] ,[col3] ,[col4] ,[col5] ,[col6] ,[col7] ,[col8] ,[col9] ,[col10] ,[col11] ,[col12] ,[col13] ,[col14] ,[col15] ,[col16] ,[col17] ,[col18] ,[col19] ,[col20] ,[col21] ,[col22] ,[col23]) NonNull, * FROM mytable ) update a set a.col24 = b.col24 from cte a cross apply ( select top 1 i.col24 from cte i where (i.col1=a.col1 OR (i.col1 is null AND a.col1 is null)) and (i.col2=a.col2 OR (i.col2 is null AND a.col2 is null)) and (i.col3=a.col3 OR (i.col3 is null AND a.col3 is null)) and (i.col4=a.col4 OR (i.col4 is null AND a.col4 is null)) and (i.col5=a.col5 OR (i.col5 is null AND a.col5 is null)) and (i.col6=a.col6 OR (i.col6 is null AND a.col6 is null)) and (i.col7=a.col7 OR (i.col7 is null AND a.col7 is null)) and (i.col8=a.col8 OR (i.col8 is null AND a.col8 is null)) and (i.col9=a.col9 OR (i.col9 is null AND a.col9 is null)) and (i.col10=a.col10 OR (i.col10 is null AND a.col10 is null)) and (i.col11=a.col11 OR (i.col11 is null AND a.col11 is null)) and (i.col12=a.col12 OR (i.col12 is null AND a.col12 is null)) and (i.col13=a.col13 OR (i.col13 is null AND a.col13 is null)) and (i.col14=a.col14 OR (i.col14 is null AND a.col14 is null)) and (i.col15=a.col15 OR (i.col15 is null AND a.col15 is null)) and (i.col16=a.col16 OR (i.col16 is null AND a.col16 is null)) and (i.col17=a.col17 OR (i.col17 is null AND a.col17 is null)) and (i.col18=a.col18 OR (i.col18 is null AND a.col18 is null)) and (i.col19=a.col19 OR (i.col19 is null AND a.col19 is null)) and (i.col20=a.col20 OR (i.col20 is null AND a.col20 is null)) and (i.col21=a.col21 OR (i.col21 is null AND a.col21 is null)) and (i.col22=a.col22 OR (i.col22 is null AND a.col22 is null)) and (i.col23=a.col23 OR (i.col23 is null AND a.col23 is null)) and i.col24 is not null order by col24 desc ) b where a.col24 is null and a.DupsInSet>1 and a.NonNull>0

D'après ce que je peux dire, vous voulez mettre à jour col4 où il est null partir des lignes qui correspondent sur col1, col2, col3 , et vous voulez utiliser la première valeur non null de col4 basée sur col4 desc .

Vous pourriez le faire comme ça:

 update a set a.col4 = b.col4 from mytable a cross apply ( select top 1 i.col4 from mytable i where i.col1 = a.col1 and i.col2 = a.col2 and i.col3 = a.col3 and i.col4 is not null order by col4 desc ) b where a.col4 is null

Vous pouvez également prendre en charge cette opération avec un index de support, par exemple:

 create nonclustered index ix_mytable_col1_col2_col3_inc_col4 on dbo.mytable (col1,col2,col3) include (col4);