2
votes

une requête UPDATE simple sur une grande table a de mauvaises performances

Je dois faire la requête de mise à jour suivante via une procédure stockée:

UPDATE table1
SET name = @name (this is the stored procedure inputparameter)
WHERE name IS NULL

Table1 n'a pas d'index ni de clé, 5 colonnes qui sont 4 entiers et 1 varchar (la colonne modifiable 'nom' est la colonne varchar)

Les enregistrements NULL sont environ 15.000.000 lignes qui doivent être mises à jour. Cela prend environ 50 minutes, ce que je pense est trop long.

J'utilise un Azure SQL DB Standard S6 (400DTU).

Quelqu'un peut-il me donner un conseil pour améliorer les performances?

sql sql-server azure-sql-database stored-procedures

4 commentaires

cherchez-vous à mettre à jour toutes les valeurs de nom nulles vers une valeur spécifique ou à mettre à jour un ID particulier?

toutes les valeurs NULL doivent être mises à jour vers @name, soit environ 15 millions d'enregistrements, sans ID particulier

Vous n'avez pas mentionné si vous aviez réellement eu la possibilité de créer un index ou de mettre à l'échelle SQL Server (c'est-à-dire à un niveau P). Ce sont deux étapes pour améliorer les performances

Si j'ai bien fait le calcul, vous mettez à jour 5 000 lignes par seconde. Qu'est-ce qui rend cela trop lent?

3 Réponses :

2
votes

Comme vous n'avez pas de clés ou d'index, je peux suggérer l'approche suivante.

1- Créez une nouvelle table en utilisant INTO (qui copiera les données) comme la requête suivante.

WHILE EXISTS (SELECT 1 FROM table1 WHERE name is null)
BEGIN
    UPDATE TOP (10000) table1
    SET name = @name
    WHERE n ame is null
END

2- Supprimer l'ancienne table

exec sp_rename 'dbo.newtable', 'table1'

3- Renommer la nouvelle table en table1 code >

drop table table1

Une autre approche peut être d'utiliser la mise à jour par lots, parfois vous obtenez de meilleures performances par rapport à la mise à jour en masse (vous devez tester en ajustant la taille du lot).

  SELECT 
       CASE 
              WHEN NAME IS NULL THEN @name 
              ELSE NAME 
       END AS NAME, 
       <other columns > 
INTO   dbo.newtable
FROM table1

0 commentaires

0
votes

pouvez-vous faire avec la méthode suivante?

UPDATE table1
SET name = ISNULL(name,@name)

pour les valeurs nulles, il sera mis à jour avec @name et le reste sera mis à jour avec la même valeur.

0 commentaires

0
votes

Non. Vous mettez à jour 15 000 000 de lignes, ce qui va prendre beaucoup de temps. Chaque mise à jour a une surcharge pour trouver la ligne et enregistrer la valeur.

Avec autant de lignes à mettre à jour, il est peu probable que la surcharge trouve les lignes. Si vous ajoutez un index sur nom , la mise à jour devra en fait mettre à jour l'index ainsi que mettre à jour les valeurs d'origine.

Si votre souci est de verrouiller la base de données, vous peut mettre en place une boucle où vous faites quelque chose comme ça encore et encore:

UPDATE TOP (100000) table1
    SET name = @name (this is the stored procedure inputparameter)
    WHERE name IS NULL;

100 000 lignes devraient durer environ 30 secondes.

Dans ce cas , un index sur nom aide . Sinon, chaque itération de la boucle serait essentiellement la lecture de la table entière.

0 commentaires