I have a ASP MVC web application that is required to load a user extract each day from a file. The users in the database should be updated accordingly: deleted if not in source, updated if in source and target, and created if only in source. While doing this, certain rights should also automatically be given to the users. If there is any error, nothing should happen at all.
First I tried to do this with Entity Framework, but the SaveChanges call takes around two minutes to return, which is a lot for the relatively small amount of users (~140 000).
My idea now is to write a stored procedure that would do the updating. I would pass the list of new users as a parameter. The type of my temporary table:
CREATE TYPE [dbo].[TempUserType] AS TABLE ( [Uid] NVARCHAR(80) NOT NULL PRIMARY KEY, [GivenName] NVARCHAR(80) NOT NULL, [FamilyName] NVARCHAR(80) NOT NULL, [Email] NVARCHAR(256) NOT NULL, [GiveRight1] BIT NOT NULL, [GiveRight2] BIT NOT NULL, [GiveRight3] BIT NOT NULL )
CREATE TABLE [dbo].[User] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Uid] NVARCHAR (80) NOT NULL, [GivenName] NVARCHAR (80) NOT NULL, [FamilyName] NVARCHAR (80) NOT NULL, [Email] NVARCHAR (256) NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC), UNIQUE NONCLUSTERED ([Uid] ASC) );
The user roles:
CREATE TABLE [dbo].[UserRole] ( [UserId] INT NOT NULL, [RoleId] INT NOT NULL, CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC), CONSTRAINT [FK_UserRole_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]), CONSTRAINT [FK_UserRole_Role] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([Id]) );
The procedure I am stuck writing:
CREATE PROCEDURE [dbo].[UpdateUsers] @extractedUsers TempUserType READONLY AS BEGIN TRANSACTION MERGE [dbo].[User] AS trg USING @extractedUsers AS src ON (trg.[Uid] = src.[Uid]) WHEN MATCHED THEN UPDATE SET trg.GivenName = src.GivenName, trg.FamilyName = src.FamilyName, trg.Email = src.Email WHEN NOT MATCHED BY TARGET THEN INSERT ([Uid], GivenName, FamilyName, Email) VALUES (src.[Uid], src.GivenName, src.FamilyName, src.Email) WHEN NOT MATCHED BY SOURCE THEN DELETE; COMMIT TRANSACTION
My question: is the use of a procedure with merge appropriate in this case to achieve the performance improvement over EF? How can I attribute roles according to the 3 boolean values that are in the source table?
Roles can be hadcoded, meaning I know that the Right1 corresponds to the RoleId 1, Right 2 to RoleId 2 and Right 3 to RoleId3.