mardi 16 juin 2015

Load user extract with stored procedure

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.

Aucun commentaire:

Enregistrer un commentaire