sql-server - values - upsert sql server




Sql Server Supprimer et fusionner les performances (2)

J'ai une table qui contient des données d'achat / vente, avec environ 8 millions d'enregistrements:

CREATE TABLE [dbo].[Transactions](
[id] [int] IDENTITY(1,1) NOT NULL,
[itemId] [bigint] NOT NULL,
[dt] [datetime] NOT NULL,
[count] [int] NOT NULL,
[price] [float] NOT NULL,
[platform] [char](1) NOT NULL
) ON [PRIMARY]

Chaque X minutes mon programme obtient de nouvelles transactions pour chaque itemId et j'ai besoin de le mettre à jour. Ma première solution est DELETE + INSERT en deux étapes:

delete from Transactions where platform[email protected]platform and itemid[email protected]itemid
insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price)
[...]
insert into Transactions (platform,itemid,dt,count,price) values (@platform,@itemid,@dt,@count,@price)

Le problème est que cette instruction DELETE prend en moyenne 5 secondes. C'est beaucoup trop long.

La deuxième solution que j'ai trouvée est d'utiliser MERGE. J'ai créé une telle procédure stockée, wchich prend le paramètre Table-valued:

CREATE PROCEDURE [dbo].[sp_updateTransactions]
@Table dbo.tp_Transactions readonly,
@itemId bigint,
@platform char(1)
AS
BEGIN
MERGE Transactions AS TARGET
USING @Table AS SOURCE  
ON (    
TARGET.[itemId] = SOURCE.[itemId] AND
TARGET.[platform] = SOURCE.[platform] AND 
TARGET.[dt] = SOURCE.[dt] AND 
TARGET.[count] = SOURCE.[count] AND
TARGET.[price] = SOURCE.[price] ) 


WHEN NOT MATCHED BY TARGET THEN 
INSERT VALUES (SOURCE.[itemId], 
                SOURCE.[dt],
                SOURCE.[count],
                SOURCE.[price],
                SOURCE.[platform])

WHEN NOT MATCHED BY SOURCE AND TARGET.[itemId] = @itemId AND TARGET.[platform] = @platform THEN 
DELETE;

END

Cette procédure prend environ 7 secondes avec une table avec 70k enregistrements. Donc, avec 8M, cela prendrait probablement quelques minutes. Le goulot d'étranglement est "Quand il ne correspond pas" - quand j'ai commenté cette ligne, cette procédure dure en moyenne 0,01 seconde.

La question est donc: comment améliorer la performance de l'instruction delete?

Supprimer est nécessaire pour s'assurer que cette table ne contient pas de transaction qui a été supprimée dans l'application. Mais le vrai scénario arrive rarement, et le vrai besoin de supprimer des enregistrements est moins de 1 sur 10000 mises à jour de transaction.

Ma solution de contournement théorique est de créer une colonne supplémentaire comme "bit transactionDeleted" et d'utiliser UPDATE au lieu de DELETE, puis de nettoyer la table par travail batch toutes les X minutes ou toutes les heures et Exécuter

delete from transactions where transactionDeleted=1

Cela devrait être plus rapide, mais je devrais mettre à jour toutes les instructions SELECT dans d'autres parties de l'application, pour utiliser uniquement les enregistrements transactionDeleted = 0 et donc cela pourrait aussi affecter les performances de l'application.

Connaissez-vous une meilleure solution?

MISE À JOUR: Les index actuels:

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[Transactions] 
(
[platform] ASC,
[ItemId] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 50) ON [PRIMARY]


CONSTRAINT [IX2] UNIQUE NONCLUSTERED 
(
[ItemId] DESC,
[count] ASC,
[dt] DESC,
[platform] ASC,
[price] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

L'utilisation d'un champ BIT pour IsDeleted (ou IsActive comme le font beaucoup de personnes) est valide mais nécessite de modifier tout le code et de créer un Job SQL séparé pour passer et supprimer périodiquement les enregistrements "supprimés". Cela pourrait être la voie à suivre, mais il y a quelque chose de moins intrusif à essayer en premier.

J'ai remarqué dans votre ensemble de 2 index que ni est classé. Puis-je supposer que le champ IDENTITY est? Vous pourriez envisager de faire de l'index [IX2] UNIQUE le CLUSTERED et de changer le PK (encore une fois, je suppose que le champ IDENTITY est un PK CLUSTERED) à être NON CLUSTERED. Je voudrais également réorganiser les champs IX2 pour mettre [Platform] et [ItemID] en premier. Puisque votre opération principale recherche [Platform] et [ItemID] dans un ensemble, les classer physiquement de cette façon pourrait vous aider. Et puisque cet index est unique, c'est un bon candidat pour être classé. Il vaut certainement la peine de tester car cela aura un impact sur toutes les requêtes contre la table.

De plus, si vous modifiez les index comme je l'ai suggéré, cela vaut la peine d'essayer les deux idées et donc de faire le champ IsDeleted pour voir si cela augmente encore plus les performances.

EDIT: J'ai oublié de mentionner, en faisant l'indice IX2 CLUSTERED et en déplaçant le champ [Platform] vers le haut, vous devriez vous débarrasser de l'indice IX1.

EDIT2:

Pour être très clair, je suggère quelque chose comme:

CREATE UNIQUE CLUSTERED  INDEX [IX2]
(
[ItemId] DESC,
[platform] ASC,
[count] ASC,
[dt] DESC,
[price] ASC
)

Et pour être honnête, changer quel index est CLUSTERED pourrait également avoir un impact négatif sur les requêtes où les JOINs sont effectuées sur le champ [id], ce qui explique pourquoi vous devez effectuer un test approfondi. En fin de compte, vous devez régler le système pour vos requêtes les plus fréquentes et / ou les plus coûteuses et accepter que certaines requêtes soient plus lentes, mais cela pourrait valoir la peine d'être beaucoup plus rapide.


Voir ce https: //.com/questions/3685141/how-to-....

la mise à jour aurait-elle le même coût qu'une suppression? La mise à jour serait une opération beaucoup plus légère, surtout si vous aviez un index sur le PK (errrr, c'est un guid, pas un int). Le point étant qu'une mise à jour vers un champ de bits est beaucoup moins chère. Une suppression (de masse) entraînerait un remaniement des données.

À la lumière de cette information, votre idée d'utiliser un champ de bits est très valable.





sql-delete