Discussing the article: "SQLite capabilities in MQL5: Example of a dashboard with trading statistics by symbols and magic numbers" - page 2

[MetaQuotes]

SQLite: native SQL database handling in MQL5

• Modern algorithmic trading in MetaTrader 5
• Functions for working with databases
• Example of a simple query:
  
• Debugging SQL queries in MetaEditor
• Automatically reading query results into a structure using DatabaseReadBind()
• Speeding up transactions by wrapping them in DatabaseTransactionBegin()/DatabaseTransactionCommit()
• Working with trades from the trading history
• Portfolio analysis by strategy
• Analysing trades by symbol
• Analysing trades by entry time
• Convenient output of data to the expert advisor log via DatabasePrint()
• Importing and exporting data
• Saving optimisation results to a database
• Optimising query execution using indexes
• Integration of database operations into MetaEditor

[Nikolai Semko]

I jumped to conclusions without digging deep enough into the matter. My apologies — I went too far in calling it a ‘toy for students’; SQLite doesn’t deserve that.

I dug a bit deeper (using AI, Opus 5 — I set up a test environment, generated synthetic data for 500,000 trades, 20 symbols, 20 strategies, and ran the tests). Here’s what I’ve concluded.

1) Five minutes on fxsaber is almost certainly NOT SQLite.

INSERTing one trade at a time with auto-commit: ~245 seconds for 500k.

The same data in a batch within a single transaction: 2.35 seconds.

A difference of 104 times. 245 seconds is exactly those ‘five minutes’. Each insert = a separate transaction with fsync. This is fixed with two lines of code: DatabaseTransactionBegin() / DatabaseTransactionCommit(). Everything else in this debate pales in comparison to this figure.

2) Maxim is right about indexes, but not about those particular indexes.

The query from the article, with a schema without indexes: 395 ms.

The same query + a standard index (MAGIC, ENTRY): 427 ms. In other words, it doesn’t help at all.

The same query + a covering index (MAGIC, ENTRY, PROFIT, SWAP, COMMISSION): 80 ms.

For a full GROUP BY, a regular index is useless — the table is read in its entirety anyway; the SCAN … USING INDEX plan is essentially the same as a scan. ALL the columns in the query need to be in the index; only then does the plan become `SEARCH ... USING COVERING INDEX`, and the table rows aren’t even accessed. That’s when you get a ×5 performance boost.

But where an index really makes a difference is in single-character lookups, which the panel performs with every redraw: 20 queries WHERE SYMBOL=? AND ENTRY=1 — 711 ms without an index versus 208 ms with an index.

3) Regarding the array of structures. Essentially, I’m right, but only if you write your own wrapper class with the necessary indexes and methods. SQLite will, of course, reduce development time at the expense of performance and resource usage.

Reading the entire 45.8 MB binary file: 25 ms.

A single pass, aggregating by magic numbers and characters simultaneously: 7 ms.

By way of comparison — loading the same 500k rows from SQLite into an array of structures (using the DatabaseReadBind loop): 220 ms.

The performance gain is real — 4 to 18 times faster, depending on the operation. And the figure of 220 ms versus 25 ms is telling: if the statistics are calculated in MQL5 anyway, the database in this scenario simply acts as a slow file format.

But the database on disk is 84 MB compared to 46 MB for the binary file — and for those extra 38 MB, you get arbitrary filters, sorting, pagination and any new reports for free. These features aren’t available in an array of structures until you write them by hand. So the claim that ‘you don’t need a database’ is true only until the moment you need a query that you hadn’t anticipated in advance. Enrique is right here: if you need to QUERY the data, rather than simply load it, SQL is in a league of its own.

4) I take back what I said about PostgreSQL.

It’s client-server-based and can’t be embedded: a separate process, installation, ports, admin tasks. Architecturally, this doesn’t fit with a single-user terminal in a sandbox. And for local analytics, SQLite is often even faster — there’s no IPC or round-trips. The argument about JSONB has also fallen flat: JSONB has been available in SQLite since version 3.45, and JSON isn’t needed at all for transaction history, as the columns there are strictly typed.

Conclusion

The main criticism of the article is directed at the schema and the code, not the engine itself. The gap between ‘SQLite as described in the article’ and ‘SQLite implemented correctly’ is a hundredfold. The gap between correctly implemented SQLite and an array of structures is just a few times.

And the most sensible suggestion in the thread came from 92746290: don’t reload the entire history, but instead load it incrementally from the last recorded transaction. This fixes the problem regardless of whether you’re using a binary file or a database.

I can post the design with covering indexes, batch inserts and incremental `Sync()`, plus the version using an array of structures, if you’re interested.

To reiterate: personally, I naturally use lightweight classes with indexes and the necessary methods, without any databases, which offer a clear advantage in terms of resource usage and performance.

[Alexey Viktorov]

Nikolai Semko #:

I jumped to conclusions without looking into the matter properly. My apologies — I went too far calling it a ‘toy for students’; SQLite doesn’t deserve that.

It’s probably not for students, but it’s still a bit of a toy. After all, before you can use SQLite, you have to ‘sift through’ the entire history and feed it into a table element by element. And that’ll take ages. If you’re going to integrate this with MQL5, then reading the history of orders and trades should be done directly into the table… IMHO

[Maxim Kuznetsov]

Nikolai Semko #:
I dug a bit deeper (using AI, Opus 5 — set up a test environment, generated synthetic data for 500,000 trades, 20 symbols, 20 strategies, and ran the performance tests).

On a side note: it would be interesting to compare it with MonetDB/e. It’s just as embedded and serverless as SQLite, but for a number of reasons it should be significantly faster on complex queries. Provided, of course, that the schema is sensible :-)

[Nikolai Semko]

Maxim Kuznetsov #:

Just a thought: it would be interesting to compare it with MonetDB/e. It’s an embedded, serverless database just like SQLite, but for a number of reasons it should be significantly faster when handling complex queries. Provided, of course, that the schema is well-designed :-)

Perhaps

I’ve been thinking about that too

It’s better optimised for NoSQL arrays

We should give it a go. With AI, we can check this out quickly now