Discussing the article: "SQLite capabilities in MQL5: Example of a dashboard with trading statistics by symbols and magic numbers"
There is a lack of tools that would allow you to work with a large trading history.
Unfortunately, this toolkit simply hangs up when requesting history, like many others.
It takes five minutes to get the history. Then it is impossible to do anything with the window - full CPU load.
There is a lack of tools to deal with a large trading history.
Unfortunately, this toolkit just hangs up when requesting history, like many others.
Five minutes to get the history. Then it is impossible to do anything with the window - full CPU load.
Can I have investor access to the account?
Unfortunately, there is no such possibility. But you can create something like this yourself: on a demo account, use a script to open/close the required number of positions by different symbols/magics in an hour using asynchronous OrderSend.
Doesn't want to work on the Moscow stock exchange
There is a lack of tools that would allow you to work with a large trading history.
Unfortunately, this toolkit simply hangs up when requesting history, like many others.
It takes five minutes to get the history. Then it is impossible to do anything with the window - full CPU load.
There is a lack of tools that would enable us to work with a large trading history.
Unfortunately, this tool simply freezes when a history request is made, just like many others.
It takes five minutes to retrieve the history. After that, it’s impossible to do anything with the window – the CPU is fully utilised.
No, no, no
Perhaps PostgreSWL would be better. However the author of the article does not use indexes on the tables and if you look at the queries it explains why it will only work responsively with a tiny dataset. If indexes are applied with queries tailored to these indexes it can do a large dataset with no problem.
Saving a struct is only usefull if you do not need to query the data, but just load. If you need to query data, nothing beats SQL.
No, no, no
In our line of work (when you don’t have 100,500 clients), SQLite outperforms PostgreSQL like a bull outrunning a sheep. The thing is, TS isn’t a DBA at all and has never worked with databases :-) Everything in the article is bound to slow things down
there are no indexes, and there are all sorts of nested queries with double aggregation, and the load on the database is through the roof, whilst the article is written for reasons you can well imagine.
P.S. To avoid any arguments, ask on specialist forums what’s wrong with the query
//--- retrieve trading statistics broken down by expert advisors by Magic Number request=DatabasePrepare(db, "SELECT r.*," " (case when r.trades != 0 then (r.gross_profit+r.gross_loss)/r.trades else 0 end) as expected_payoff," " (case when r.trades != 0 then r.win_trades*100.0/r.trades else 0 end) as win_percent," " (case when r.trades != 0 then r.loss_trades*100.0/r.trades else 0 end) as loss_percent," " r.gross_profit/r.win_trades as average_profit," " r.gross_loss/r.loss_trades as average_loss," " (case when r.gross_loss!=0.0 then r.gross_profit/(-r.gross_loss) else 0 end) as profit_factor " "FROM " " (" " SELECT MAGIC," " sum(case when entry =1 then 1 else 0 end) as trades," " sum(case when profit > 0 then profit else 0 end) as gross_profit," " sum(case when profit < 0 then profit else 0 end) as gross_loss," " sum(swap) as total_swap," " sum(commission) as total_commission," " sum(profit) as total_profit," " sum(profit+swap+commission) as net_profit," " sum(case when profit > 0 then 1 else 0 end) as win_trades," " sum(case when profit < 0 then 1 else 0 end) as loss_trades " " FROM DEALS " " WHERE SYMBOL <> '' and SYMBOL is not NULL " " GROUP BY MAGIC" " ) as r");
In short, just to get this out of the way so no one says I’m nit-picking:
SYMBOL must be of type TEXT NOT NULL. SQLite has very few data types: int, real, text, blob (and even these are optional – you don’t have to specify them). NOT NULL will save you from pointless checks like WHERE SYMBOL NOT NULL in every query
ID, TRADE_ID and similar columns are not just KEYs; they are PRIMARY KEYs
Fields that are frequently used in WHERE and GROUP BY clauses should be indexed. There aren’t enough indexes in the schema; queries are slowing down. See below regarding EXPLAIN
It is better to populate the database in ‘batches’. Running an INSERT for every single transaction takes a very, very long time. Where possible, send 100 records per query. (Ideally, you should synchronise the data on every run, rather than simply populating it upon creation)
The queries contain a lot of duplicated calculations and elements that can or should be offloaded to the client. Nested SELECT statements are a bad sign in themselves; if there’s no other way round it, you’ll have to run EXPLAIN separately and optimise the database and queries.
For real-world use and a large number of transactions, pagination and virtual tables are certainly needed (where you load data into your views as and when required, rather than maintaining a massive array of structures), but that’s probably beyond the scope of this article.
---
By and large, the database schema simply hasn’t been thought through properly, which is why everything has slowed down on fxsaber. It makes sense to fix and expand the schema, optimise the queries, and with minimal changes it should start working properly.
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
Check out the new article: SQLite capabilities in MQL5: Example of a dashboard with trading statistics by symbols and magic numbers.
The function is designed to draw the final statistics table for the selected symbol, magic number, or the entire account. The function receives the type of the statistics table and the name of the symbol, or the string value of the magic number, or the account number. The text determines the index of the symbol, magic number or account in the corresponding array of statistical data structures. From the required structure, we use the obtained index to get all statistical data into the structure, and then we arrange them in the rendered table according to the coordinates of its cells. In this case, the horizontal offsets of the displayed text are calculated in such a way that the data header is tied to the left edge of the table cell, and the text of the data value is tied to the right edge of its table cell. All data is displayed in four columns so that they are visually grouped on the panel in two columns in the form of "title -- value".
Let's compile the indicator and see what we got:
We can see that all the declared functionality works as expected. We can see slight "blinking" of text in tables when moving the cursor and scrolling tables. But this is the result of a suboptimal redrawing scheme - the entire visible part of the table is constantly redrawing. This can be avoided by more complex logic for handling table rows under the cursor, but this is not our objective here.
Author: Artyom Trishkin