Avaliação de escalabilidade para um sistema de auditoria simples baseado em um ORM

Pediram-me para criar um sistema de auditoria simples para um aplicativo da Web. Devido a alguns constrangimentos optei por uma forma "suja e rápida". Isso foi possível devido à pequena atividade naquela aplicação específica (várias centenas de inserções por dia).

No entanto, agora tenho que implementar em outro aplicativo da Web que gere mais atividade, então considero refatorá-lo.

Notas:

• todas as consultas são geradas pelo ORM (estrutura de entidade) usada e parecem bastante terríveis.

• todos os testes são realizados com uma tabela preenchida com alguns registros fictícios de 2K, distribuídos uniformemente entre alguns usuários, tempo etc.

Definição de dados

CREATE TABLE dbo.AppEvent
(
    AppEventId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AppEvent PRIMARY KEY CLUSTERED,
    InsertTimestamp DATETIME2 NOT NULL CONSTRAINT DF_AppEvent DEFAULT(GETDATE()),
    UserId INT NOT NULL CONSTRAINT FK_AppEvent_UserId REFERENCES dbo.AppUser,
    EventTypeId INT NOT NULL CONSTRAINT FK_AppEvent_EventType REFERENCES dbo.EventType,
    RegionId INT NULL CONSTRAINT FK_AppEvent_Region REFERENCES dbo.Region,
    CountryId INT NULL CONSTRAINT FK_AppEvent_Country REFERENCES dbo.Country,
    InsertDay AS (CAST(InsertTimestamp as DATE)),
    InsertMonth AS (CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, InsertTimestamp), 0) AS DATE)),
    InsertYear AS (CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, InsertTimestamp), 0) AS DATE)),
    Description NVARCHAR(2000) NULL,
    ProjectId INT NULL CONSTRAINT FK_AppEvent_Project REFERENCES dbo.Project,
    ReminderActionId INT NULL CONSTRAINT FK_AppEvent_ReminderAction REFERENCES dbo.ReminderAction
)
GO

Esta é a única tabela relevante. Todas as referências FK vão para chaves primárias agrupadas e a maioria das tabelas contém menos de 100 registros.

Registro real

O aplicativo tenta agrupar informações de log, evitando inserir muito próximo no tempo (mesmo usuário, mesmo tipo de evento).

Assim, ele precisa fazer um SELECT:

exec sp_executesql N'SELECT TOP (1) 
    [Project1].[InsertTimestamp] AS [InsertTimestamp]
    FROM ( SELECT 
        [Extent1].[AppEventId] AS [AppEventId], 
        [Extent1].[InsertTimestamp] AS [InsertTimestamp]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[UserId] = @p__linq__0) AND ([Extent1].[EventTypeId] = @p__linq__1) AND (([Extent1].[ReminderActionId] = @p__linq__2) OR (([Extent1].[ReminderActionId] IS NULL) AND (@p__linq__2 IS NULL)))
    )  AS [Project1]
    ORDER BY [Project1].[AppEventId] DESC',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 int',@p__linq__0=1,@p__linq__1=4,@p__linq__2=27

Isso gera cerca de:CPU = 16, Reads = 34, Writes = 0, Duration = 0

Olhando para o plano de execução, pensei que um índice poderia melhorar as coisas:

CREATE INDEX IDX_AppEvent_User_EventType ON dbo.AppEvent (UserId, EventTypeId, ReminderActionId) INCLUDE (AppEventId, InsertTimestamp)

Isto dáCPU = 0, Reads = 20, Writes = 0, Duration = 0

As instruções INSERT reais são assim:

exec sp_executesql N'INSERT [dbo].[AppEvent]([InsertTimestamp], [UserId], [EventTypeId], [RegionId], [CountryId], [Description], [ProjectId], [ReminderActionId])
VALUES (@0, @1, @2, @3, @4, @5, @6, NULL)
SELECT [AppEventId], [InsertDay], [InsertMonth], [InsertYear]
FROM [dbo].[AppEvent]
WHERE @@ROWCOUNT > 0 AND [AppEventId] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int,@3 int,@4 int,@5 nvarchar(2000),@6 int',
@0='2017-01-30 14:54:02.6469319',@1=1,@2=7,@3=5,@4=305,@5=N'Custom message',@6=1533

SELECTA instrução é causada pelo ORM que precisa conhecer o identificador recém-criado (terei que procurar se posso me livrar do extra SELECTque não é necessário).

Isso leva cerca deCPU = 16, Reads = 32, Writes = 0, Duration = 9

O plano de execução gera o seguinte:

e isto

Relatório

O relatório de auditoria é bastante simples e raramente é executado (várias vezes por dia no máximo). As consultas típicas são assim:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K2] AS [InsertDay], 
    [GroupBy1].[K1] AS [CountryId], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[CountryId] AS [K1], 
        [Extent1].[InsertDay] AS [K2], 
        COUNT(1) AS [A1]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[EventTypeId] IN (1, 6, 7, 9)) AND ([Extent1].[CountryId] IS NOT NULL)
        GROUP BY [Extent1].[CountryId], [Extent1].[InsertDay]
    )  AS [GroupBy1]

`CPU = 16, Reads = 25, Writes = 0, Duration = 11`

SELECT 
    1 AS [C1], 
    [GroupBy1].[K2] AS [InsertMonth], 
    [GroupBy1].[K1] AS [CountryId], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[CountryId] AS [K1], 
        [Extent1].[InsertMonth] AS [K2], 
        COUNT(1) AS [A1]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[EventTypeId] IN (1, 6, 7)) AND ([Extent1].[CountryId] IS NOT NULL)
        GROUP BY [Extent1].[CountryId], [Extent1].[InsertMonth]
    )  AS [GroupBy1]

`CPU = 16, Reads = 25, Writes = 0, Duration = 12`

Pergunta: considerando uma geração de no máximo 100K eventos por dia, devo pensar em reescrever todo o mecanismo de auditoria (da perspectiva do banco de dados)?

Na camada de aplicação posso fazer diversas melhorias como: garantir que a auditoria não seja realizada na mesma transação que as alterações operacionais e utilizar outra thread para realizar as consultas.