Load user extract with stored procedure

Question

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
)

The users:

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.

Answer 1

After reading the question I want to leave an idea for the solution. For me it makes more sense to have an IF / ELSE for calling the update or the insert, not using the merge since you need the UserId that you are updating/inserting to add it's role permissions.

You can check if UId exists and if so update the user details, if does not exists then create and keep the new Identity value.

In both cases, when having the user ID add the corresponding permissions according to the boolean values with IF statements.

Pseudo-code:

If UserId exists in UsersTable
   Update
   Store UserId
Else
   Insert
   Store created UserId (using the @@IDENTITY)

If bool1 add permission 1
If bool3 add permission 2
If bool3 add permission 3