RES ActiveRecord uses unnecessary join

[leifg]

RES ActiveRecord uses unnecessary join

[fidel]

Hey @leifg!

Could you share a bit more details about your use case? Reading 30k+ events sounds a lot.

Providing some setup information like DB and exact SQL which is generated would be also helpful.

event_store_events_in_streams table keeps only event's ids, which is not enough to return read events, join to event_store_events is required anyways at some point.

Have you tried performing any benchmarks to that point?

[leifg]

Could you share a bit more details about your use case? Reading 30k+ events sounds a lot.

Sorry I should have been more clear. This is not a use case but a situation in our system. We have one stream with 30k events. This happened by accident over a couple of months. The result is: the whole system is slowing down whenever we touch this stream. There are multiple ways to optimize this in we are certainly gonna do more than one thing to get rid of this issue (one thing is probably going to get rid of the stream all together and rebuild the data).

That being said, there is one optimization that can happen within the ruby_event_store-active_record gem

event_store_events_in_streams table keeps only event's ids, which is not enough to return read events, join to event_store_events is required anyways at some point.

That is true if you want to (efficiently) filter on attributes in the events table such as event type, timestamp etc...

However if you want to just load all events in a stream in order all you need to do is do 2 seperate queries. One to load the rows from the stream table, and then one query to query all events. This is happening via the .preload(:event) in the first row:

irb(main):050:0> @stream_klass.where(stream: "my_stream").preload(:event)

# SELECT "event_store_events_in_streams".* FROM "event_store_events_in_streams" WHERE "event_store_events_in_streams"."stream" = $1
# SELECT "event_store_events".* FROM "event_store_events" WHERE "event_store_events"."event_id" IN ($1, $2)

So for certain query the join is unnecessary.

[fidel]

Thanks for the broader explanation @leifg, it helped us understand what's the root of the problem.

The fix of issue you have exposed is on master branch already, official release should be expected soon.

Cheers!

[lukaszreszke]

Hi @leifg,

We have just released version 2.8.0 containing the fix for the issue that you have reported.

We'll appreciate any feedback.

[leifg]

That was quick. Thank you so much!