Writing every increment of message sequence number to DB is a major bottleneck

[varunbpatil]

SQL store writes every single increment of sender message sequence number and target message sequence number to the DB. For high volume of incoming messages, such as market data messages, this is a major bottleneck.

[steelkorbin]

Reading/Writing straight to a SQL store of any kind with any package(not just this one) will always result in this effect when processing real-world market data speeds and volumes. The SQL store is the wrong data management tool for the market data straight from a market data feed. To correctly capture market data, goroutine out to a IMDB/cache to keep pace with the feed, then handle data persistence on the other side of that cache with a regular flushing process into your DB/File Store. Remember that this package is only the endpoint for FIX message parsing as fast and accurate as possible, plus the market data is immutable, so a DBMS is pointless directly bolted on here. Use your DB after the persistent data is loaded.

[varunbpatil]

Hi @steelkorbin , thanks for the response. I didn't mean persistence of the market data messages itself. I meant the SQL store that quickfix uses to store session data.

quickfix/sqlstore.go

Line 247 in 098031e

func (store *sqlStore) SetNextTargetMsgSeqNum(next int) error {

Quickfix increments the incoming message sequence number in the session table for every incoming message. This happens regardless of whether I consume the message later or not. So, maybe I should tell quickfix to use the in-memory store in this case, but maybe some sort of batching in quickfix's SQL store implementation would be useful.

[Yu-Xie]

I think this is a bottleneck for both incoming and outgoing messages. See the thread I opened for outgoing:
#555

For incoming, it's less concerning because you can use in-memory store and if you lose data, you can always ask the other party to resend. For outgoing, the bottleneck is a more serious concern because we need to persist before sending out, so in-memory store isn't really an option most of the times.