Il y a assez souvent une situation où vous devez exécuter l'instruction INSERT, UPDATE ou DELETE en fonction d'une condition. Et ma question est de savoir si l'effet sur les performances de la requête ajoute IF EXISTS avant la commande.
Exemple
IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1)
UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1
Qu'en est-il des insertions ou suppressions?
Je ne suis pas complètement sûr, mais j'ai l'impression que cette question est vraiment upsert, qui est la suivante opération atomique:
UPDATE de la cible;INSERT de la ligne dans la cible;DELETE de la ligne de la cible.Les développeurs de-tourner-Administrateurs de bases de données souvent naïvement à écrire ligne par ligne, comme ceci:
-- For each row in source
IF EXISTS(<target_expression>)
IF @delete_flag = 1
DELETE <target_expression>
ELSE
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
ELSE
INSERT target (<target_columns>)
VALUES (<source_values>)
C'est à peu près la pire chose que vous pouvez faire, et ce pour plusieurs raisons:
Il a une course à condition. La ligne peut disparaître entre IF EXISTS et ultérieure DELETE ou UPDATE.
C'est un gaspillage. Pour chaque opération, vous avez un supplément opération en cours d'exécution; c'est peut-être trivial, mais cela dépend de comment vous vous êtes bien indexé.
Le pire de tout c'est à la suite d'un modèle itératif, une réflexion sur ces problèmes au niveau d'une seule ligne. Ce sera la plus grande (la pire) impact sur la performance globale.
Un très mineur (et j'insiste sur le mineur), l'optimisation est de simplement tenter de l' UPDATE de toute façon; si la ligne n'existe pas, @@ROWCOUNT sera de 0, et vous pouvez alors "en toute sécurité" insérer:
-- For each row in source
BEGIN TRAN
UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>
IF (@@ROWCOUNT = 0)
INSERT target (<target_columns>)
VALUES (<source_values>)
COMMIT
Le pire des cas, ce sera toujours effectuer deux opérations pour chaque transaction, mais au moins il ya une chance de ne la faire qu'un, et il élimine également la condition de la course (genre de).
Mais le vrai problème est que c'est toujours effectué pour chaque ligne dans la source.
Avant de SQL Server 2008, vous avez eu à utiliser un maladroit 3-modèle des étapes à traiter au niveau du set (encore mieux que ligne par ligne):
BEGIN TRAN
INSERT target (<target_columns>)
SELECT <source_columns> FROM source s
WHERE s.id NOT IN (SELECT id FROM target)
UPDATE t SET <target_columns> = <source_columns>
FROM target t
INNER JOIN source s ON t.d = s.id
DELETE t
FROM target t
WHERE t.id NOT IN (SELECT id FROM source)
COMMIT
Comme je l'ai dit, la performance est assez moche, mais encore beaucoup mieux que celui-ligne-à-un-temps approche. SQL Server 2008, cependant, introduit enfin de FUSION de la syntaxe, alors maintenant, tout ce que vous avez à faire est ceci:
MERGE target
USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE <target_columns> = <source_columns>
WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
C'est tout. Une déclaration. Si vous utilisez SQL Server 2008 et besoin d'effectuer une séquence de INSERT, UPDATE et DELETE selon si oui ou non la ligne existe déjà - même si c'est juste une ligne - il n'y a aucune excuse pour ne pas être l'aide d' MERGE.
Vous pouvez même OUTPUT lignes affectées par une MERGE dans une variable de table si vous avez besoin de savoir par la suite ce qui a été fait. Simple, rapide et sans risque. Le faire.
Vous ne devriez pas le faire pour UPDATE et DELETE , car s'il y a un impact sur les performances, ce n'est pas positif .
Pour INSERT il peut y avoir des situations où votre INSERT lèvera une exception ( UNIQUE CONSTRAINT violation, etc.), auquel cas vous voudrez peut-être l'empêcher avec le IF EXISTS et gérez-le plus gracieusement.
Ni
UPDATE … IF (@@ROWCOUNT = 0) INSERT
ni
IF EXISTS(...) UPDATE ELSE INSERT
les modèles fonctionnent comme prévu sous haute simultanéité. Les deux peuvent échouer. Les deux peuvent échouer très fréquemment. FUSION est le roi - il tient beaucoup mieux.Nous permettre de faire des tests de stress et de voir par nous-mêmes.
Voici le tableau que nous allons utiliser:
CREATE TABLE dbo.TwoINTs
(
ID INT NOT NULL PRIMARY KEY,
i1 INT NOT NULL ,
i2 INT NOT NULL ,
version ROWVERSION
) ;
GO
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( 1, 0, 0 ) ;
SI il EXISTE(...) ALORS patron, souvent, ne sous haute simultanéité.
Laissez-nous insérer ou mettre à jour les lignes d'une boucle à l'aide de la suite logique simple: si une ligne avec le code existe, mettre à jour, et sinon, insérez-en une nouvelle. La boucle suivante implémente cette logique. Couper et coller dans deux onglets, basculez en mode texte dans les deux onglets, et les exécuter simultanément.
-- hit Ctrl+T to execute in text mode
SET NOCOUNT ON ;
DECLARE @ID INT ;
SET @ID = 0 ;
WHILE @ID > -100000
BEGIN ;
SET @ID = ( SELECT MIN(ID)
FROM dbo.TwoINTs
) - 1 ;
BEGIN TRY ;
BEGIN TRANSACTION ;
IF EXISTS ( SELECT *
FROM dbo.TwoINTs
WHERE ID = @ID )
BEGIN ;
UPDATE dbo.TwoINTs
SET i1 = 1
WHERE ID = @ID ;
END ;
ELSE
BEGIN ;
INSERT INTO dbo.TwoINTs
( ID, i1, i2 )
VALUES ( @ID, 0, 0 ) ;
END ;
COMMIT ;
END TRY
BEGIN CATCH ;
ROLLBACK ;
SELECT error_message() ;
END CATCH ;
END ;
Lors de l'exécution de ce script simultanément dans deux onglets, nous allons tout de suite une énorme quantité de violations de clé primaire dans les deux onglets. Il montre comment peu fiables les S'il EXISTE un motif, c'est quand il s'exécute sous haute simultanéité.
Remarque: cet exemple montre également qu'il n'est pas sûr d'utiliser SELECT MAX(ID)+1 ou SELECT MIN(ID)-1 pour la prochaine valeur unique si nous le faisons, en vertu de la simultanéité.
Vous ne devriez pas faire cela dans la plupart des cas. Selon votre niveau de transaction, vous avez créé une condition de concurrence, maintenant dans votre exemple ici, cela n'aurait pas trop d'importance, mais les données peuvent être modifiées de la première sélection à la mise à jour. Et tout ce que vous avez fait est de forcer SQL à faire plus de travail
La meilleure façon de savoir avec certitude est de tester les deux différences et de voir laquelle vous donne les performances appropriées.
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
