Paged queries are slow for large datasets

[kblooie]

From @CoreyKaylor on August 27, 2013 23:2

The queries are always starting with a base row of 0. Here is one example where this happens, but it's not the only place assuming 0 as the starting index. Things seem to work fine until you get into very large datasets.

https://github.com/NEventStore/NEventStore/blob/master/src/NEventStore/Persistence/SqlPersistence/SqlDialects/CommonDbStatement.cs#L129

Copied from original issue: NEventStore/NEventStore#230

[kblooie]

From @CoreyKaylor on August 27, 2013 23:5

I'll have some more information regarding slowness of the paged queries. We found that the default indexes were not ideal for large datasets. Specifically including the body in the index is largely unnecessary and slows things down for the typical usage.

[kblooie]

From @damianh on March 18, 2014 14:38

Can you verify if this is still a problem with v5? Thx :)

[adamfur]

Did some benchmarking, in our system, reading events gets linearly slower when reaching a high checkpoint number.
Reading events at offset 2M, yields 2.5 additional seconds of latency for each query.

Changed this file: NEventStore/Persistence/Sql/SqlPersistenceEngine.cs

        public IEnumerable<ICommit> GetFrom(string checkpointToken)
        {
            LongCheckpoint checkpoint = LongCheckpoint.Parse(checkpointToken);
            Logger.Debug(Messages.GettingAllCommitsFromCheckpoint, checkpointToken);
            return ExecuteQuery(query =>
            {
                string statement = @"
SELECT top 512 BucketId, StreamId, StreamIdOriginal, StreamRevision, CommitId, CommitSequence, CommitStamp, CheckpointNumber, Headers, Payload
FROM Commits
WHERE  CheckpointNumber > @CheckpointNumber";
                query.AddParameter(_dialect.CheckpointNumber, checkpoint.LongValue);
                return query.ExecutePagedQuery(statement, (q, r) => { })
                    .Select(x => x.GetCommit(_serializer, _dialect));
            });
        }

Querying events seems to be constant time. You probably also wanna use an order by, if you wanna use this code in vanilla NEventStore.