SQLite in MQL5: new features and performance testing

New comment
 

In the 2265 build we have implemented the regular database functions based on SQLite 3.30.1:


Databases can be held either on disk or in memory only with the DATABASE_OPEN_MEMORY flag. Wrapping massive inserts/changes in DatabaseTransactionBegin/Commit/Rollback transactions speeds up operations hundreds of times.

As we are maximally focused on performance, here are the results of LLVM 9.0.0 vs MQL5 tests. Time in milliseconds, the less the better:
Windows 10 x64, Intel Xeon  E5-2690 v3 @ 2.60GHz
                                                        LLVM   MQL5
---------------------------------------------------------------------------------
Test  1: 1000 INSERTs:                                 11572   8488
Test  2: 25000 INSERTs in a transaction:                  59     60
Test  3: 25000 INSERTs into an indexed table:            102    105
Test  4: 100 SELECTs without an index:                   142    150
Test  5: 100 SELECTs on a string comparison:             391    390
Test  6: Creating an index:                               43     33
Test  7: 5000 SELECTs with an index:                     385    307
Test  8: 1000 UPDATEs without an index:                   58      54
Test  9: 25000 UPDATEs with an index:                    161    165
Test 10: 25000 text UPDATEs with an index:               124    120
Test 11: INSERTs from a SELECT:                           84     84
Test 12: DELETE without an index:                         25     74
Test 13: DELETE with an index:                            70     72
Test 14: A big INSERT after a big DELETE:                 62     66
Test 15: A big DELETE followed by many small INSERTs:     33     33
Test 16: DROP TABLE: finished.                            42     40

The speed in MQL5 is absolutely the same as in native C++ with one of the best compilers. A suite of benchmarks for replaying is attached.


We have also implemented a unique DatabaseReadBind function that allows you to read records directly into the structure, which simplifies and speeds up bulk operations.

Here is a simple example:

struct Person
  {
   int               id;
   string            name;
   int               age;
   string            address;
   double            salary;
  };

//+------------------------------------------------------------------+
//| Test                                                             |
//+------------------------------------------------------------------+
bool TestDB(string filename,int flags)
  {
   int db;
//--- open
   db=DatabaseOpen(filename,flags);
   if(db==INVALID_HANDLE)
     {
      Print("DB: ",filename," open failed with code ",GetLastError());
      return(false);
     }
//--- create a table
   if(!DatabaseTableExists(db,"COMPANY"))
      if(!DatabaseExecute(db,"CREATE TABLE COMPANY("
                          "ID INT PRIMARY KEY     NOT NULL,"
                          "NAME           TEXT    NOT NULL,"
                          "AGE            INT     NOT NULL,"
                          "ADDRESS        CHAR(50),"
                          "SALARY         REAL );"))
        {
         Print("DB: ",filename," create table failed with code ",GetLastError());
         DatabaseClose(db);
         return(false);
        }
//--- insert data
   if(!DatabaseExecute(db,"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); "
                       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "
                       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"
                       "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"))
     {
      Print("DB: ",filename," insert failed with code ",GetLastError());
      DatabaseClose(db);
      return(false);
     }
//--- prepare the request
   int request=DatabasePrepare(db,"SELECT * FROM COMPANY WHERE SALARY>15000");

   if(request==INVALID_HANDLE)
     {
      Print("DB: ",filename," request failed with code ",GetLastError());
      DatabaseClose(db);
      return(false);
     }
//--- выводим записи
   Person person;

   for(int i=0; DatabaseReadBind(request,person); i++)
      Print(i,":  ",person.id, " ", person.name, " ",person.age, " ",person.address, " ",person.salary);

   Print("");
//--- close all
   DatabaseFinalize(request);
   DatabaseClose(db);
   return(true);
  }

//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {
   TestDB("test.sqlite",DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE |DATABASE_OPEN_COMMON);
  }


Output:
0:  1 Paul 32 California 20000.0
1:  3 Teddy 23 Norway 20000.0
2:  4 Mark 25 Rich-Mond  65000.0



Files:
SqLiteTest.zip  2709 kb
 
Great news-introduction Renat! There is such a question.
Can the .sqlite file be included in the ME project structure? for subsequent packaging in .ex5
If so, how will the .ex5 program behave when the .sqlite file size is increased? in an already compiled .ex5 program
 

Thanks for the new functionality.
I consider the good help on the new functionality as the key to success in mastering it. I really miss examples of the work in the help itself.
Please also pay attention to the following deficiencies which were found:


1) The description of the DatabaseExecute function is not true, but copied fromDatabasePrepare.

2) Incomplete description of the first parameter ofDatabaseRead function:intdatabase,// database handle obtained in DatabaseOpen;
 SinceDatabasePrepare provides more complete information:Creates a query handle, which can then be executed with DatabaseRead().

3) DoDatabaseTransactionXXX functions really generate the given GetLastError() error lists or do they perform a "follow-up error from a previous failure"?

4) No information is provided for DatabaseTransactionXXX functions about handling nested transactions.

5) There is a misprint in theDatabaseColumnName function parameter description (it must be "to get the field name")
string&name// reference to the variable for getting thetable name

 
Roman:
Great news, Renat! One question has arisen.
Can a .sqlite file be included in the ME project structure?
If so, how will the .ex5 program behave when the .sqlite file size is increased? in an already compiled .ex5 program

Most likely we will allow resources to be included and these files will be automatically extracted to disk the first time the programme is started.

That is, there will be no swelling of the base inside ex5. The file can only be handled on disk.

 
Renat Fatkhullin:

The bases can be kept either on disk or in memory only, using the DATABASE_OPEN_MEMORY flag.

Do I understand correctly that this is the official mechanism of data exchange between MT5 terminals (instead of killing SSD files) and between programs inside the terminal (instead of resources)?

 
Sergey Dzyublik:

Thanks for the new functionality.
I think a good help for the new functionality is the key to success in mastering it. I really miss the examples of how to work in the help itself.
Please also pay attention to the following disadvantages which I found:


1) The description of the DatabaseExecute function is not true, but copied from DatabasePrepare.

2) Incomplete description of the first parameter ofDatabaseRead function:intdatabase, // database handle obtained in DatabaseOpen;
 Since DatabasePrepare provides more complete information: s creates a query handle, which can then be executed with DatabaseRead().

3) DoDatabaseTransactionXXX functions really generate the given error lists GetLastError() or do they perform "follow-up error from a previous failure"?

4) No information is provided for DatabaseTransactionXXX functions about handling nested transactions.

5) There is a misprint in the DatabaseColumnName function parameter description (it must be "to get the field name")
string&name// reference to the variable for getting thetable name

The help has already been partially updated, have a look again. This is still the first version of the help and it will be updated.

There are no nested transactions in SQLite, so no need to try to do them.

Examples are presented in the topic, there's nothing complicated there at all. We'll do an article and a wrapper class in the standard library later on.

 
fxsaber:

Do I understand correctly that this is an official mechanism of data exchange between MT5 terminals (instead of SSD killing files) and between programs inside the terminal (instead of resources)?

Stop spreading blatant nonsense about "killing SSDs" from incompetent users.

No, these are file bases - they can be exchanged, but it's risky to access them simultaneously from different experts due to potentially monopoly access when bases are open at the same time.

Open "in-memory-only by DATABASE_OPEN_MEMORY flag" databases are only available to a specific program and are not shared with anyone.


Database applications:

  1. Storing of settings and states of MQL5 programs

  2. Mass data storage

  3. Using externally prepared data

    . For example, exported data from Metatrader to SQLite, in Python calculated this data using ready-to-use math packages and put the result also in SQlite format.

In the next release, there will be native support for viewing and editing SQLite databases directly in the editor, which will lead to using these databases as a regular data exchange mechanism.
 
Renat Fatkhullin:

In the next release there will be native support for viewing and editing SQLite databases directly in the editor, leading to the use of these databases as a regular data exchange mechanism.

The database editor in ME, well that would be very handy, thank you.

 
Renat Fatkhullin:

No, these are file bases - they can be exchanged, but it is risky to access them simultaneously from different EAs due to potentially monopoly access when bases are open at the same time.

Python has a library calledSqlite3Worker for thread-safe I/O when working with a database from multiple threads.
Perhaps it makes sense to think about porting the implementation to mql, to allow asynchronous work with the database of multiple Expert Advisors.
Well, or borrow the idea and implement your own asynchronous I/O.

sqlite3worker
sqlite3worker
  • 2017.03.21
  • pypi.org
('Thread safe sqlite3 interface',)
 
Roman:

Python hasSqlite3Worker library, for thread-safe input/output, when working with the database from multiple threads.
Perhaps there is a point to consider porting implementation to mql, to allow asynchronous work with the database of several Expert Advisors.
Well, or borrow the idea and implement your own asynchronous I/O.

Have you seen the performance table above? It's often faster in MQL5 than in C++.

We have multithreading, of course, and everything is correct.

The question is about something else - what happens if different programs/processes independently access the same database file. Not a single program (MQL5), but several independent programs that do not know about each other and do not use the same database handle.

 
Is it difficult in principle to synchronise access to the same database from different programmes/terminals? Are you getting ersatz again?
1234567
New comment
Reason: