Use prepared statements prevents caching

[arthurwolf]

When running thousands of bbgo processes (backtesting) over the same symbol and the same time period, every time the process is launched, all k-lines for that symbol/period need to be recovered from the database.

This can take minutes depending on the parameters/load, while backtesting itself takes much less time. So there is room for a lot of optimization here.

One such method is to use ProxySQL between MySQL and BBGO, and ask it to cache the queries, so if a query is asked multiple times, the data is taken from the cache instead of requested/computed again.

However, ProxySQL can not cache "prepared" statements, and BBGO (through sqlx) uses prepared statements.

See : http://go-database-sql.org/prepared.html

If BBGO were to change from prepared statements to "sprintf"-type statements (at least for the two k-line requests), this would result in massive speed improvements when using caching.

The statements that would most benefit from this change are:

• SELECT t.* FROM (SELECT * FROM binance_klines WHERE end_time <= ? AND symbol = ? AND interval = ? ORDER BY end_time DESC LIMIT ?) AS t ORDER BY t.end_time ASC
• SELECT * FROM binance_klines WHERE end_time BETWEEN ? AND ? AND symbol IN (?) AND interval IN (?, ?) ORDER BY end_time ASC

[crossgate10]

WIP

[arthurwolf]

wow, very cool, thank you!

are you somebody else who actually needs this / wants to cache queries ?