Is there any additional configuration for ZeosLib 7.2.4 to optimize MySQL performance?

[EsequiasTorres2]

@ EsequiasTorres2,

I do not understand why this is happening?
Maybe change the server?
For me it is so (in your test):

Server info = 5.5.5-10.1.1-MariaDB
Client info = 5.5.40
Delphi 10 Seattle -Win32(on Win 10-64)

Zeoslib 7.3.0-alpha svn 5355
Insert data, do not use prepared sql statement 49031 MilliSeconds
Insert data, use prepared sql statement 15359 MilliSeconds
Select and sum data, use the RecordCount property 157 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 140 MilliSeconds. Result 705082704

UniDAC 7.4.11
Insert data, do not use prepared sql statement 16469 MilliSeconds
Insert data, use prepared sql statement 15406 MilliSeconds
Select and sum data, use the RecordCount property 63 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 62 MilliSeconds. Result 705082704

Michal

Thank you very much.
To use mORMot I want to follow the recommendations of using ZeosLib for database access, since the direct access to create JSON can greatly increase performance by avoiding the DB layer, but I'm losing hope because I see that even in your performance tests the speed difference is high. I will continue the development of the project trying to maintain compatibility with several data access layers, facilitating the use of one or another data access layer like ODBC, ZEOS, UniDAC, etc.

Delphi 10.2, UniDAC 7.3.10
Insert data, do not use prepared sql statement 12084 MilliSeconds
Insert data, use prepared sql statement 9703 MilliSeconds
Select and sum data, use the RecordCount property 108 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 95 MilliSeconds. Result 705082704

Some interesting evidence:
http://blog.synopse.info/post/2014/03/0 ... PostgreSQL

[EsequiasTorres2]

I'm so sorry. I apologize for not answering your questions and for not including in the test code the configuration you have recommended.

How did you insert those records?

Code: Select all

Query1.SQL.Text:= 'INSERT INTO tb_test_zeos (ID) VALUES (:PARAM_ID)';
For Id:= 1 To Records Do
Begin
        Query1.ParamByName('PARAM_ID').AsInteger:= Id;
        Query1.ExecSQL;
End;

Which Component did you use to insert the records?

TZQuery

MyDAC/Unidac. I know they are there .. that's all. Are those components unidirectional or do you have a scrollable cursor?

These components are not unidirectional.

In mORMot you just need forwardonly (unidirectional...

mORMot uses UTF8 only. Set your ZConnection.ControlsCodePage to cCP_UTF8, and switch off the TZConnection.AutoEncode modus. Open your MySQL connection with utf8mb4 or utf8(buggy for more than 4Byte encoded characters).

I'm not using mORMot yet, I'm starting the preliminary tests.

Code: Select all

procedure RunTest;
const Records = 100000;
var TimeBegin: TDateTime;
    Id: Integer;
    Log: TStringList;
    Sum: Integer;
begin
     ShowMessage('Begin test');
     Log:= TStringList.Create;
     try
        {$IfDef Use_ZeosLib}
        Connection1:= TZConnection.Create(Nil);
		    //ZConnection1.UseMetadata:= True;
		    Connection1.Properties.Add('UseResult=True');
        Query1:= TZQuery.Create(Nil);
        Query1.Connection:= Connection1;
		    Query1.Options:= Query1.Options - [doCalcDefaults];
        Log.Append('Zeoslib ' + Connection1.Version);
        with Connection1 do
        begin
             LibraryLocation:= ExtractFilePath(ExtractFileDir(Application.ExeName)) + 'libmySQL.dll';
             Protocol:= 'mysql';
             HostName:= 'localhost';
             Port:= 3307;
             User:= 'root';
             Password:= '';
             Database:= 'dbtestzeos';
             Connect;
        end;

        Query1.SQL.Text:= 'TRUNCATE TABLE tb_test_zeos';
        Query1.ExecSQL;

        TimeBegin:= Now;
        For Id:= 1 To Records Do
        Begin
             Query1.SQL.Text:= 'INSERT INTO tb_test_zeos (ID) VALUES (' + IntToStr(Id) + ')';
             Query1.ExecSQL;
        End;
        Log.Append('Insert data, do not use prepared sql statement ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds');
        
        // zeoslib-code-0-r5355-branches-testing-7.3 insert 3 records and raise Access violation at address 00000000 in module 'Test.exe'. Read of address 00000000.
        Query1.SQL.Text:= 'TRUNCATE TABLE tb_test_zeos';
        Query1.ExecSQL;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'INSERT INTO tb_test_zeos (ID) VALUES (:PARAM_ID)';
        Query1.ParamCheck:= True;
        Query1.Prepare;
        Try
        For Id:= 1 To Records Do
        Begin
             Query1.ParamByName('PARAM_ID').AsInteger:= Id;
             Query1.ExecSQL;
        End;
        Except On Error: Exception Do InputBox('', '', Error.Message);
        End;
        Log.Append('Insert data, use prepared sql statement ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds');

        Sum:= 0;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'SELECT ID FROM tb_test_zeos';
        Query1.Open;
        For Id:= 1 To Query1.RecordCount Do
        Begin
             Sum:= Sum + Query1.Fields[0].AsInteger;
             Query1.Next;
        End;
        Log.Append('Select and sum data, use the RecordCount property ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds. Result ' +  IntToStr(Sum));

        Query1.Close;
        Sum:= 0;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'SELECT ID FROM tb_test_zeos';
        Query1.Open;
        While Not Query1.Eof Do
        Begin
             Sum:= Sum + Query1.Fields[0].AsInteger;
             Query1.Next;
        End;
        Log.Append('Select and sum the data, do not use the RecordCount property ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds. Result ' +  IntToStr(Sum));
        Log.SaveToFile(ExtractFilePath(Application.ExeName) + FormatDateTime('yyyy-mm-dd hh-nn-ss.zzz', Now) + 'Zeoslib ' + Connection1.Version + '.txt');
        {$Else}
        Connection1:= TMyConnection.Create(Nil);
        Query1:= TMyQuery.Create(Nil);
        Query1.Connection:= Connection1;
        Log.Append('MyDAC 5.20.0.11');
        with Connection1 do
        begin
             Server:= 'localhost';
             Port:= 3307;
             Username:= 'root';
             Password:= '';
             Database:= 'dbtestzeos';
             Connect;
        end;
        Query1.SQL.Text:= 'TRUNCATE TABLE tb_test_zeos';
        Query1.Execute;

        TimeBegin:= Now;
        For Id:= 1 To Records Do
        Begin
             Query1.SQL.Text:= 'INSERT INTO tb_test_zeos (ID) VALUES (' + IntToStr(Id) + ')';
             Query1.Execute;
        End;
        Log.Append('Insert data, do not use prepared sql statement ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds');

        Query1.SQL.Text:= 'TRUNCATE TABLE tb_test_zeos';
        Query1.Execute;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'INSERT INTO tb_test_zeos (ID) VALUES (:PARAM_ID)';
        Query1.ParamCheck:= True;
        Query1.Prepare;
        For Id:= 1 To Records Do
        Begin
             Query1.ParamByName('PARAM_ID').AsInteger:= Id;
             Query1.Execute;
        End;
        Log.Append('Insert data, use prepared sql statement ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds');

        Sum:= 0;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'SELECT ID FROM tb_test_zeos';
        Query1.Open;
        For Id:= 1 To Query1.RecordCount Do
        Begin
             Sum:= Sum + Query1.Fields[0].AsInteger;
             Query1.Next;
        End;
        Log.Append('Select and sum data, use the RecordCount property ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds. Result ' +  IntToStr(Sum));

        Query1.Close;
        Sum:= 0;
        TimeBegin:= Now;
        Query1.SQL.Text:= 'SELECT ID FROM tb_test_zeos';
        Query1.Open;
        While Not Query1.Eof Do
        Begin
             Sum:= Sum + Query1.Fields[0].AsInteger;
             Query1.Next;
        End;
        Log.Append('Select and sum the data, do not use the RecordCount property ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds. Result ' +  IntToStr(Sum));
        Log.SaveToFile(ExtractFilePath(Application.ExeName) + FormatDateTime('yyyy-mm-dd hh-nn-ss.zzz', Now) + ' MyDAC 5.20.0.11  ' + '.txt');
     {$EndIf}
     ShowMessage('Test finished');
     finally
            Log.Free;
            Connection1.Free;
            Query1.Free;
     end;
end;

Delphi 7, Zeoslib 7.2.4-stable
Insert data, do not use prepared sql statement 25602 MilliSeconds
Insert data, use prepared sql statement 10580 MilliSeconds
Select and sum data, use the RecordCount property 325 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 311 MilliSeconds. Result 705082704

The performance is very good, but I don't know if it will be possible to configure it in mORMot without using the DB layer. I have to learn more.

[EgonHugeist]

These components are not unidirectional.

Ok, then let's forget about undirectional mode.

According the "Emulateted" inserts:
My advice is never do that with a loads of records. One or two .. it does'nt matter. If you use no parameters than switch of the

Code: Select all

ParamCheck := False

. This avoids scanning the sql strings for possible available params. Consider a TZConnection.ExecuteDirect instead if you don't need an update count validation.

7.2 does not use the prepared c-api of my SQL by default. You've to explicit add

Code: Select all

preferprepared=true

to the TZConnection.Properties. But the way it is implemented in 7.2 it won't boost the performance very much.
7.3 automatically prepares the Stmts after third execution and only [selects, inserts, updates, deletes]. That was schown in your regression. You can setup this delay for real preparing by using the TZConnection/TZDataSet Property

Code: Select all

MinExecCountBeforePrepare=x

Did you notice my fix for that? Update 7.3 from svn and repeat the tests with 7.3 plz.

You'd start the thread, i have no MyDAC by hand and i think it is helpful for others too as you mentioned in you original question. It would be nice if you present the values of 7.2 and 7.3 against the DevArt components and which advice(s) finally did help to increase the performance.

To use mORMot I want to follow the recommendations of using ZeosLib for database access, since the direct access to create JSON can greatly increase performance by avoiding the DB layer, but I'm losing hope because I see that even in your performance tests the speed difference is high. I will continue the development of the project trying to maintain compatibility with several data access layers, facilitating the use of one or another data access layer like ODBC, ZEOS, UniDAC, etc.

Esequias, according the mORMot. For high performance Server it makes no sence to use any TDataSet component. All TDataSets are slow by design but these components are very userfriendly with datasensitive controls. Fetching a row or filling a fieldbuffer, changing active record etc. are firing ton's of notifications and testing dozens of events and options. All the developer-simplification of the "R" in the "RAD" finally decreases the performance to much.

mORMot does NOT use the TZDataSet decendants of Zeos. They are using the simple ZDbc high performance kernel which skips the TDataSet bottleneck. If you would compare ZDbc against MyDac using 7.3 i would expect the results are vice verca. Which means there is not concurential TDataSet availible having a comparable performance to ZDbc. And again that is by design.

[aehimself]

Kind of a long shot, but worth a try.
When I had "performance issues" (a specific data processing task took 500 ms which I found unacceptable ) my solution was to put the whole thing in a single transaction:

Try
sqlconnection.StartTransaction;
TheveryLongProcedureWithLotsOfUpdates;
sqlconnection.Commit;
Except
If sqlconnection.InTransaction Then sqlconnection.Rollback;
End;

This way the thing finished in 200 ms instead of the previous 500. I have to mention that the "LotsOfUpdates" means like ~100 locates (and data comparison, without primary key defined), ~3 updates and ~7 inserts. Also, I was connected to the test database (which is hosted on an underpowered VM) through a 54 Mbits WLAN connection (WRT54GL is love, WRT54GL is life!). The running time directly on the underpowered VM decreased from 250 ms to 120.
I imagine that packing thousands of inserts in a single transaction would yield even better results.