N+1 problems with FilterExpired

[thomaseyde]

Please forgive me if I'm incorrect.

I use SqlStreamStore.MsSql, and when I call ReadAllForwards to retrieve 1000 events, I experience retrieve times of over 30 seconds. For 2000 events, it's almost a minute.

Running the query to select those rows from the Messages table, it takes < 1 second.

Investigating this, I find that for each page retrieved, there is a call to FilterExpired, which:

• calls GetMaxAge, GetStreamMetadata, then GetStreamMetadataInternal to read the last message of the stream, get the message's metadata, to finally read the MaxAge.
• if MaxAge doesn't exist or is in the future, the message is kept.
• if MaxAge is in the past, the message is deleted from the stream.

Not only do we get one additional database call per message where the overhead increases with the message count, but there is a decision during a read operation to delete messages!

I will not question the need for a MaxAge, but now that it's there:

• No read operations should try to delete anything. But if that's the most pragmatic solution, it should be optional and opt-in.
• The read operation should retrieve enough information at the first read to decide if the message has expired and then remove it from the result.
• All deletes, when opted, should be batched into one command.

As it is now, we pay a huge performance penalty for the rare occasion a message has expired.

[thomaseyde]

I cloned the repo and removed everything related to stream metadata, which includes max age handling, filtering and deletions on the read side, and experienced a 100x performance improvement.

[thomaseyde]

Setting max age and count is something you do explicit on the write side by calling IStreamStore.SetStreamMetadata. It's strange that these values are not only acted upon on the read side, but that a call to IReadonlyStreamStore.ReadAll... mutates the event store.

Wouldn't it be better to move all mutations to the write side, ie to the IStreamStore interface? And ensure that all implementations of IReadonlyStreamStore is in fact read-only?

[thefringeninja]

The extra reads for metadata are a known issue in 1.1.x. There is a prerelease version - v1.2.0-beta.5 - which addresses this, but you want to use the MsSqlStreamStoreV3 class. There is an upgrade path to migrate to the new database schema.

In addition, in this version there are drivers for both Postgres and MySql which have superior performance characteristics.