Dodanie parametru table-valued spowalnia wykonanie zapytania

Próba optymalizacji

Podczas implementacji Ses.MsSql chciałem jak najbardziej zoptymalizować zapis danych do bazy. Jedną ze znanych metod jest zmniejszenie ilości odwołań do serwera bazy danych.

Kiedy pojawia się więcej niż jeden rekord do zapisania standardowo zapisujemy w pętli jeden po drugim. Niestety każdy zapis to wywołanie zapytania typu INSERT, czyli strzał do bazy. A gdyby tak wysłać do serwera wszystkie rekordy naraz?

MS SQL Server od wersji 2008 posiada taką możliwość. Możemy zdefiniować własny typ, na przykład:

CREATE TYPE dbo.NewEvents AS TABLE (
    [Version] INT NOT NULL,
    [ContractName] NVARCHAR(225) NOT NULL,
    [Payload] VARBINARY(MAX) NOT NULL
)

Teraz kawałek SQLa, w którym skorzystamy z naszego nowego typu:

--DECLARE @Events NewEvents

INSERT INTO [Streams]([StreamId],[CommitId],[Version],[ContractName],[Payload],[CreatedAtUtc])
SELECT
    @StreamId,
    @CommitId,
    [Version],
    [ContractName],
    [Payload],
    @CreatedAtUtc
FROM
    @Events
ORDER BY
    [Version];

Taka konstrukcja pozwala na wrzucenie do tabeli Streams wszystkich rekordów przesłanych w strukturze NewEvents. Pokazany powyżej przykład zapytania to tylko wycinek procedury, która odpowiedzialna jest za dodawanie nowych zdarzeń do bazy projektu Ses.MsSql.

No dobra, mamy załatwioną część po stronie sqla. Teraz kod c#:

await cmd
    .AddInputParam(SqlQueries.InsertEvents.ParamStreamId, DbType.Guid, streamId)
    .AddInputParam(SqlQueries.InsertEvents.ParamCommitId, DbType.Guid, commitId)
    .AddInputParam(SqlQueries.InsertEvents.ParamCreatedAtUtc, DbType.DateTime, DateTime.UtcNow)
    .AddInputParam(SqlQueries.InsertEvents.ParamMetadataPayload, DbType.Binary, metadata, true)
    .AddInputParam(SqlQueries.InsertEvents.ParamIsLockable, DbType.Boolean, isLockable)
    .AddInputParam(SqlQueries.InsertEvents.ParamEvents, records)
    .ExecuteNonQueryAsync(cancellationToken)
    .ConfigureAwait(false);

gdzie records to poprostu:

IEnumerable<SqlDataRecord>

Typ SqlDataRecord został dodany do framework.net już w wersji 2.0.

Pokazany powyżej przykład daje (powinien dać) nam taką przewagę nad pojedynczym insertem, że przesłanie jednego czy wielu rekordów nie powinno zwiększać czasu wykonania (przynajmniej w pewnej skali).

I faktycznie tak jest. Czy przesyłam 1, 10 czy 100 rekordów to nadal czasy wykonania są podobne - zachowuje pewną liniowość.

Fiasko

Niestety kiedy wykonałem pomiary dla procedury bez parametru typu table-valued to wyszło mi, że cała konstrukcja wykonuje się ponad 9 razy szybciej. Problem opisywałem już jakiś czas temu na SO. Do tej pory nie znalazłem rozwiązania.

Wnioski

Taka optymalizacja przynajmniej w tym przypadku traci jakikolwiek sens.

Co więcej, większość biznesowych operacji generuje zazwyczaj jedno (dwa) zdarzenia. Dla takiej ilości wystarczająco wydajnym mechanizmem jest zapis zdarzeń rekord po rekordzie, a na dodatek w przypadku wystąpienia jakiegokolwiek błędu (błąd wersji agregatu - concurrency) od razu dostajemy informację, na którym zdarzeniu się to stało i możemy bezpośrednio podjąć odpowiednie kroki. O czym w kolejnych wpisach.

Komentarze