Speed up replay from SQL Server

[kblooie]

From @danbarua on February 13, 2015 10:21

I'm not sure how relevant this is to V5/6 but this massively speeds up replays on MSSQL:

CREATE NONCLUSTERED INDEX [IX_Commits_Ordered] ON [dbo].[Commits]
(
    [CommitSequence] ASC,
    [StreamId] ASC,
    [StreamRevision] ASC
)

Copied from original issue: NEventStore/NEventStore#391

[kblooie]

From @damianh on February 13, 2015 11:31

nice!
On 13 Feb 2015 11:21, "Dan Barua" notifications@github.com wrote:

I'm not sure how relevant this is to V5/6 but this massively speeds up
replays on MSSQL:

CREATE NONCLUSTERED INDEX [IX_Commits_Ordered] ON [dbo].[Commits]([CommitSequence] ASC,
[StreamId] ASC,
[StreamRevision] ASC)

—
Reply to this email directly or view it on GitHub
NEventStore/NEventStore#391.

[kblooie]

From @OyvindAndersen on March 12, 2015 10:48

Hi,

On what version did you add this index? We're using both 2.x and 5.x and for 2.x the PK_Commits has StreamId and CommitSequence. This seems for me like a good index but I have not investigated the actual sql statement for replay.

Why wouldn't you want StreamId first and then CommitSequence. Does including StreamRevision increase performance?

Have you tried snapshots if your performance regarding replays are slow?

[kblooie]

From @danbarua on March 12, 2015 11:50

This was on V3/4. Snapshots are no use when rebuilding my read model as it needs all the events regardless.
My read model rebuild code is essentially this:

foreach(var commit in store.Advanced.GetFrom(DateTime.MinValue)
foreach(var @event in commit.Events)
{
  DispatchToEventHandlers(@event);
}

I just found this by playing around with SQL Profiler during replays and looking at the query plans.

[kblooie]

From @OyvindAndersen on March 12, 2015 12:34

Ahh. Ok. In your case u're using DateTime and the actual sql statement is ordered by CommitStamp, StreamId and StreamRevision, at least for V2. So that should be a good index also. There is already one for CommitStamp (IX_Commits_Stamp) that maybe could include StreamId and StreamRevision so you don't need two indexes.

[AGiorgetti]

Instead of adding the suggested index,
Consider adding the 'CommitSequence' to this index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_Revisions] ON [dbo].[Commits] ([BucketId], [StreamId], [StreamRevision], [Items]);