J’ai récemment été confronté à un problème d’architecture SQL et de performances d’opérations CRUD sur SQL SERVER 2008
Pour tout vous dire, il s’agit de mon petit projet WP7 Starcraft 2 (oui je sais, je suis un Geek joueur 
)
Il ne devrait pas tarder à pointer le bout de son nez sur le Market Place, mais en attendant, voici un post résumant une des problématiques SQL que j’ai rencontrée !
Voici globalement les données du problème :
- Je récupère des données provenant d’une source externe
- Je dois fusionner ces données avec les données existantes de ma base de données. Il faut :
- Insérer les données non existantes
- Mettre à jour les données existantes
- Supprimer les données existantes de ma base de données ne faisant plus partie du scope
- Je doit traiter pour une clé primaire de 0 à N données associées (N variant de 0 à environ 1000 enregistrements environ)
- Pour imager le truc, pensez à quelque chose du genre (“Pour chaque client, je récupère de 0 à N Factures)
- Je dois garder une consitence des données lors de ces opérations
- Je dois être performance (si si !)
Bien, à partir de là, on peut commencer à réfléchir à une solution.
Première solution : Brut-Force
Oui alors bon celle là, elle est loin d’être excellente, mais c’est la première qui vient à l’esprit:
Je crée une procédure stockée qui prend en paramètre un Record.
Celle ci vérifie si la ligne existe :
- Si elle existe : Update
- Si elle n’existe pas : Insert
On éxécute cette requête N fois le nb de records à mettre à jour. (Pour info je mets à jour environ 1 Million de record, faudra pas être pressé 
)
On encapsule ça dans une TransactionScope supportée par MSDTC et roulez !
Bon problème :
- On ne gère pas les enregistrements existants obsolètes à supprimer
- C’est ANTI-performant …
- C’est une solution qui va engendrer un nombre de problèmes incalculables…
Deuxième solution : La solution élégante
Alors, aprés avoir réfléchi à une solution pérenne, voici ce que je vous propose.
0 à N Enregistrements :
Là c’est plutôt simple, on va passer par une TVP : Table Value Parameter.
j’en parle ici pour ceux qui sont intéressés par la découverte de cette formidable avancée
Voici la définition de ma TVP:
CREATE TYPE [dbo].[CharacterLeaguesTvp] AS TABLE(
[LeagueId] [int] NOT NULL, [RegionId] [nvarchar](5) NOT NULL,
[CharacterId] [int] NOT NULL, [int] NOT NULL, [Name] [nvarchar](150) NULL,
[MostPlayedRace] [tinyint] NULL, [DivisionName] [nvarchar](250) NULL, [TeamType] [int] NULL,
[LeagueType] [int] NULL, [Rank] [tinyint] NULL, [Points] [int] NULL, [VictoriesCount] [int] NULL,
[LossesCount] [int] NULL, [LastModifiedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
([RegionId] ASC, [LeagueId] ASC, [ZoneId] ASC, [CharacterId] ASC)WITH (IGNORE_DUP_KEY = OFF)
)
Bon la défintion de la procédure stockée devient, du coup, BEAUCOUP plus simple 
Et elle a la bonne idée de prendre l’ensemble de mes records en un seul appel 
ALTER PROCEDURE [dbo].[spInsertOrUpdateCharacterLeague]
(
@tmpCharacterLeagues AS CharacterLeaguesTvp readonly
)
as
BEGIN
....
END
Ok, premier problème résolu, on a UN seul appel à une procédure stockée. Facile de gérer la consitence des données du coup !
Fusion : MERGE
Pour la partie fusion des données, la réponse est assez simple quand on parle juste d’insertion et mise à jour.
On va utiliser l’instruction MERGE de SQL SERVER 2008. De plus avec l’apport de la TVP, rien de plus simple !
Pour l’utilisation du Merge, je vous redirige ici.
Voici le corps de la méthode d’insertion / mise à jour :
MERGE tCharacterLeague AS target
USING @tmpCharacterLeagues AS source
ON (target.CharacterId = source.CharacterId and
target.LeagueId = source.LeagueId And
target.ZoneId = source.ZoneId and
target.RegionId = source.RegionId)
WHEN MATCHED THEN
UPDATE SET LastModifiedDate = source.LastModifiedDate,
Name = source.Name,
MostPlayedRace = source.MostPlayedRace,
[Rank] = source.[Rank],
Points = Source.Points,
VictoriesCount = Source.VictoriesCount,
LossesCount = source.LossesCount
WHEN NOT MATCHED By TARGET THEN
INSERT (RegionId, CharacterId, ZoneId, LeagueId, Name, MostPlayedRace, [Rank], Points,
VictoriesCount, LossesCount, LastModifiedDate)
VALUES (Source.RegionId, Source.CharacterId, Source.ZoneId, Source.LeagueId,
Source.Name, Source.MostPlayedRace, Source.[Rank], Source.Points,
Source.VictoriesCount, Source.LossesCount, Source.LastModifiedDate)
Ok là on est vraiment pas mal, en 1 appel, on fusionne TOUS les enregistrements existants et les nouveaux.
Reste à gérer les suppressions.
Là j’étais parti sur un truc assez simple de prime abord : On rajoute l’instruction de suppression sur le Merge
WHEN NOT MATCHED By Source THEN
Delete;
Et ça marche … un peu trop
En effet, le Delete agit sur L’ENSEMBLE de la table, du coup TOUS les enregistrements de ma base (même ceux qui ne correspondent pas à ma clé primaire) sont impactées et donc supprimés !
Du coup je me retrouve avec une base .. vide! (enfin presque, il reste les éléments insérés / updatés)
Bon, il faut donc réduire le scope de mon merge “à la base”
Rien de plus simple avec une CTE !
Ce qui donne au final :
-- Utilisation d'une CTE pour réduire le scope, surtout pour le delete
WITH TargetCharacterLeague AS
(
SELECT CL.*
FROM dbo.CharacterLeague CL
Inner Join @tmpTableLeague T
on T.LeagueId = CL.LeagueId And T.ZoneId = CL.ZoneId and T.RegionId = CL.RegionId
)
MERGE TargetCharacterLeague AS target
USING @tmpCharacterLeagues AS source
ON (target.CharacterId = source.CharacterId and
target.LeagueId = source.LeagueId And
target.ZoneId = source.ZoneId and
target.RegionId = source.RegionId)
WHEN MATCHED THEN
UPDATE SET LastModifiedDate = source.LastModifiedDate,
Name = source.Name,
MostPlayedRace = source.MostPlayedRace,
[Rank] = source.[Rank],
Points = Source.Points,
VictoriesCount = Source.VictoriesCount,
LossesCount = source.LossesCount
WHEN NOT MATCHED By TARGET THEN
INSERT (RegionId, CharacterId, ZoneId, LeagueId, Name, MostPlayedRace, [Rank], Points,
VictoriesCount, LossesCount, LastModifiedDate)
VALUES (Source.RegionId, Source.CharacterId, Source.ZoneId, Source.LeagueId,
Source.Name, Source.MostPlayedRace, Source.[Rank], Source.Points,
Source.VictoriesCount, Source.LossesCount, Source.LastModifiedDate)
WHEN NOT MATCHED By Source THEN
Delete;
Et voilà, une utilisation à la fois élégante et performante des outils mis à votre disposition avec SQL SERVER 2008 !
Bon Merge , bonne CTE, bonne TVP etc etc !