SQL Server: performances MERGE

J'ai une table de database avec 5 millions de lignes. L'index clusterisé est une colonne d'identité auto-incrémentée. PK est un code généré 256 octets VARCHAR qui est un hachage SHA256 d'une URL, c'est un index non-clustered sur la table.

Le tableau est le suivant:

 CREATE TABLE [dbo].[store_image]( [imageSHAID] [nvarchar](256) NOT NULL, [imageGUID] [uniqueidentifier] NOT NULL, [imageURL] [nvarchar](2000) NOT NULL, [showCount] [bigint] NOT NULL, [imageURLIndex] AS (CONVERT([nvarchar](450),[imageURL],(0))), [autoIncID] [bigint] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_imageSHAID] PRIMARY KEY NONCLUSTERED ( [imageSHAID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [autoIncPK] ON [dbo].[store_image] ( [autoIncID] 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) ON [PRIMARY] GO 

• imageSHAID est un hachage SHA256 d'une URL d'image par exemple " http://blah.com/image1.jpg ", il est haché dans un varchar de longueur 256.

• imageGUID est un guid généré par code dans lequel imageGUID l'image (il sera utilisé comme index plus tard, mais pour l'instant j'ai omis cette colonne comme index)

  • Instructions pour l'utilisation de la tâche de fusion dans SSIS
  • Comment créer une table personnalisée à partir de deux tables différentes
  • SSIS 14 – Staging Area – Fusionner deux sources prend beaucoup de time
  • Instruction de mise à jour multiple dans SQL Server MERGE
  • MERGE – Plusieurs cas QUAND MATCHED avec mise à jour

• imageURL est l'URL complète de l'image (jusqu'à 2000 caractères)

• showCount est le nombre de fois que l'image est montrée, ceci est incrémenté chaque fois que cette image particulière est montrée.

• imageURLIndex est une colonne calculée limitée à 450 caractères, ce qui me permet de faire des searchs de text sur imageURL si je le souhaite, elle est indexable (encore une fois l'index est omis par souci de brièveté)

• autoIncID est l'index cluster, devrait permettre une insertion plus rapide des données.

Périodiquement, je fusionne à partir d'une table temporaire dans la table store_image . La structure de la table temporaire est la suivante (très similaire à la table store_image):

 CREATE TABLE [dbo].[store_image_temp]( [imageSHAID] [nvarchar](256) NULL, [imageURL] [nvarchar](2000) NULL, [showCount] [bigint] NULL, ) ON [PRIMARY] GO 

Lorsque le process de fusion est exécuté, j'écris un DataTable à la table temporaire en utilisant le code suivant:

 using (SqlBulkCopy bulk = new SqlBulkCopy(storeConn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null)) { bulk.DestinationTableName = "[dbo].[store_image_temp]"; bulk.WriteToServer(imageTableUpsetDataTable); } 

Je lance ensuite la command merge pour mettre à jour le showCount dans la table store_image en fusionnant à partir de la table temporaire basée sur imageSHAID . Si l'image n'existe pas dans la table store_image , je la crée:

 merge into store_image as Target using [dbo].[store_image_temp] as Source on Target.imageSHAID=Source.imageSHAID when matched then update set Target.showCount=Target.showCount+Source.showCount when not matched then insert values (Source.imageSHAID,NEWID(), Source.imageURL, Source.showCount); 

J'essaie généralement de merge des lignes 2k à 5k de la table temporaire à la table store_image lors d'un process de fusion.

J'avais l'habitude de courir cette DB sur un SSD (seulement SATA 1 connecté) et c'était très rapide (less de 200 ms). J'ai manqué de place sur le disque SSD, j'ai donc changé la database en un disque tournant de 1 To 7200, car les time de réalisation sont alors de 6 à 100 secondes (6000 – 100000 ms). Lorsque l'insertion en bloc est en cours d'exécution, je peux voir l'activité du disque d'environ 1 Mo-2 Mo / s, faible utilisation du processeur.

Est-ce un time d'écriture typique pour cette quantité de données? Cela me semble un peu lent, qu'est-ce qui cause la lenteur de la performance? Sûrement avec l' imageSHAID étant indexé nous devrions nous attendre à des time de search plus rapides que ceci?

Toute aide serait appréciée.

Merci pour votre time.

• Instruction SQL Merge utilisant un proc stocké comme source
• Transact-SQL / Vérifier si un nom existe déjà
• Fonctionnalité de fusion SQL sans spécifier de noms de colonnes
• Pourquoi "WHEN MATCHED" ne peut apparaître plus d'une fois dans une clause "UPDATE" d'une instruction MERGE?
• Msg 8672, niveau 16, état 1, ligne 1 L'instruction MERGE a tenté d'UPDATE ou SUPPRIMER la même ligne plus d'une fois