Page 1 of 2

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

Posted: 03.12.2018, 04:19
by EsequiasTorres
I use Delphi 7 with MyDAC (https://www.devart.com/mydac/) and I want to use Synopse mORMot Framework (https://synopse.info/fossil/wiki?name=SQLite3+Framework) with ZeosLib (7.2.4-stable build at 2018-03-25 11:08:27) to access MySQL databases (MySQL 5.0.18-nt), but in the tests I have found that ZeosLib behaves very slowly when making selection queries with a lot of data.
I have researched a little and it seems that several users have had the same drawbacks, as described in this link http://zeoslib.sourceforge.net/viewtopic.php?t=37

I have done some performance testing (without using Synopse mORMot Framework), inserting and reading 100,000 whole numbers in a table:

Code: Select all

CREATE DATABASE `DBtestZeos` /*!40100 CHARACTER SET utf8 COLLATE utf8_spanish_ci */;
CREATE TABLE tb_test_zeos (ID MEDIUMINT (8) UNSIGNED, PRIMARY KEY(ID)) TYPE = MyISAM /*!40100 DEFAULT CHARSET utf8 COLLATE utf8_spanish_ci */;
INSERT INTO tb_test_zeos (ID) VALUES (1);
…
INTO INSERT tb_test_zeos (ID) VALUES (100000);
With ZeosLib it has taken 25 seconds to insert the records, executing each SQL statement with a TZQuery and 15 seconds to select the data and access each record to load it into a StringStream or display the data in a DBGrid.

With MyDAC it took 12 seconds to insert the records, executing each SQL statement with a TMyQuery and 150 milliseconds to select the data and access each record to load it into a StringStream or display the data in a DBGrid.

Is there any additional configuration for ZeosLib 7.2.4 to optimize MySQL performance?
I guess I still need to configure some options to improve performance.

In the Testing branch 7.3 (https://sourceforge.net/p/zeoslib/code- ... esting-7.3) I find

Code: Select all

{.$DEFINE USE_SYNCOMMONS} //enable JSON content support by using SynCommons.pas from Synopse project
I suppose it will optimize performance when used with Synopse mORMot Framework, but being a trial version I think I can't use it.

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

Posted: 04.12.2018, 08:22
by Fr0sT
Don't know about MyDAC but Zeos uses automatic transaction control by default. Try switching to manual control and perform all your of loop inside a single transaction. I believe it will significantly increase perf.
As for USE_SYNCOMMONS, it just enables one method to export dataset to JSON, it has nothing to do with perf.

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

Posted: 04.12.2018, 11:15
by miab3
@EsequiasTorres,

It is possible that the MyDAC opens only the initial records.

Can you give the results of such a test ?:

Code: Select all

SQL:  
"select ID from tb_test_zeos"

procedure TForm1.Button1Click(Sender: TObject);
var li:integer;
    st,st1: cardinal;
begin
ZConnection1.Disconnect;
ZConnection1.Connect;
li:=0;
st:=GetTickCount;
ZQuery1.Open;
while not ZQuery1.Eof do
begin
 li:=li+ZQuery1.Fields[0].AsInteger;
 ZQuery1.Next;
end;
st1:=GetTickCount;
Edit2.Text:=IntToStr(st1-st);
Edit1.Text:=inttostr(li);
end;

procedure TForm1.Button2Click(Sender: TObject);
var li:integer;
  st,st1: cardinal;
begin
UniConnection1.Disconnect;
UniConnection1.Connect;
li:=0;
st:=GetTickCount;
UniQuery1.Open;
while not UniQuery1.Eof do
begin
 li:=li+UniQuery1.Fields[0].AsInteger;
 UniQuery1.Next;
end;
st1:=GetTickCount;
Edit4.Text:=IntToStr(st1-st);
Edit3.Text:=inttostr(li);
end;
Michal

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

Posted: 04.12.2018, 14:30
by marsupilami
Hello EsequiasTorres,

Zeos 7.3 most probably will contain new performance enhancements for MySQL. But one more thought on 7.2: I seem to remember that Zeos always uses prepared staments. In a case like that using your code could become a performance killer because it needs to be prepared again and again. What happens if you use a parameter in your statement?

Code: Select all

ZQuery.SQL.Text := 'INSERT INTO tb_test_zeos (ID) VALUES (:ID);'
for x := 1 to 100000 do begin
  ZQuery.ParamByName('ID').AsInteger := x;
  ZQuery.ExecSQL;
end;

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

Posted: 07.12.2018, 07:47
by EsequiasTorres
Fr0sT wrote:Don't know about MyDAC but Zeos uses automatic transaction control by default. Try switching to manual control and perform all your of loop inside a single transaction. I believe it will significantly increase perf.
As for USE_SYNCOMMONS, it just enables one method to export dataset to JSON, it has nothing to do with perf.
Thank you very much.

I'm going to study the manual transactions.

On performance improvement with USE_SYNCOMMONS, I found this information in Synopse mORMot:

...
it appears that all libraries based on DB.pas are slower than the others for reading speed. In fact, TDataSet sounds to be a real bottleneck, due to its internal data marshalling. Even FireDAC, which is known to be very optimized for speed, is limited by the TDataSet structure. Our direct classes, or even ZEOS/ZDBC performs better, since they are able to output JSON content with no additional marshalling, via a dedicated ColumnsToJSON() method.
Source: https://synopse.info/files/html/Synopse ... #TITLE_137

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

Posted: 07.12.2018, 07:53
by EsequiasTorres
miab3 wrote:@EsequiasTorres,

It is possible that the MyDAC opens only the initial records.

Can you give the results of such a test ?:

Code: Select all

SQL:  
"select ID from tb_test_zeos"

procedure TForm1.Button1Click(Sender: TObject);
var li:integer;
    st,st1: cardinal;
begin
ZConnection1.Disconnect;
ZConnection1.Connect;
li:=0;
st:=GetTickCount;
ZQuery1.Open;
while not ZQuery1.Eof do
begin
 li:=li+ZQuery1.Fields[0].AsInteger;
 ZQuery1.Next;
end;
st1:=GetTickCount;
Edit2.Text:=IntToStr(st1-st);
Edit1.Text:=inttostr(li);
end;

procedure TForm1.Button2Click(Sender: TObject);
var li:integer;
  st,st1: cardinal;
begin
UniConnection1.Disconnect;
UniConnection1.Connect;
li:=0;
st:=GetTickCount;
UniQuery1.Open;
while not UniQuery1.Eof do
begin
 li:=li+UniQuery1.Fields[0].AsInteger;
 UniQuery1.Next;
end;
st1:=GetTickCount;
Edit4.Text:=IntToStr(st1-st);
Edit3.Text:=inttostr(li);
end;
Michal

Thank you very much.

In the question I have indicated the following:
...select the data and access each record to load it into a StringStream or display the data in a DBGrid.
These are the results:

Zeos:
Edit1: 705082704
Edit2: 15834

MyDAC:
Edit3: 705082704
Edit4: 141

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

Posted: 07.12.2018, 08:00
by EsequiasTorres
marsupilami wrote:Hello EsequiasTorres,

Zeos 7.3 most probably will contain new performance enhancements for MySQL. But one more thought on 7.2: I seem to remember that Zeos always uses prepared staments. In a case like that using your code could become a performance killer because it needs to be prepared again and again. What happens if you use a parameter in your statement?

Code: Select all

ZQuery.SQL.Text := 'INSERT INTO tb_test_zeos (ID) VALUES (:ID);'
for x := 1 to 100000 do begin
  ZQuery.ParamByName('ID').AsInteger := x;
  ZQuery.ExecSQL;
end;
Thank you very much.

If I don't use prepared queries as you suggested it takes 25 seconds to insert the 100,000 numbers.

If I use prepared queries as you have suggested it takes 11 seconds to insert the 100,000 numbers, less time than MyDAC.

I need to optimize the time it takes to read the information.

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

Posted: 07.12.2018, 08:12
by EsequiasTorres
If any of you can please execute the following code and show the result would be of great help to me.

Code: Select all


SQL:  
"select ID from tb_test_zeos" to select 100,000 numbers

procedure TForm1.Button1Click(Sender: TObject);
var li: integer;
    st, st1: cardinal;
begin
     ZConnection1.Disconnect;
     ZConnection1.Connect;
     li:= 0;
     st:= GetTickCount;
     ZReadOnlyQuery1.Open;
     while not ZReadOnlyQuery1.Eof do
     begin
          li:= li + ZReadOnlyQuery1.Fields[0].AsInteger;
          ZReadOnlyQuery1.Next;
     end;
     st1:= GetTickCount;
     Edit2.Text:= IntToStr(st1-st);
     Edit1.Text:=inttostr(li);
end;

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

Posted: 07.12.2018, 08:43
by Fr0sT
EsequiasTorres wrote:On performance improvement with USE_SYNCOMMONS, I found this information in Synopse mORMot:
Yes, Zeos has full-featured and faster low-level classes that many people use directly instead of TDataset ancestors. These are IZStatement and IZResultSet. You can see them in action in {Zeos}\test\dbc units. But the define only does matter if you stream your data to JSON.

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

Posted: 07.12.2018, 12:12
by miab3
@EsequiasTorres,
EsequiasTorres wrote:
These are the results:

Zeos:
Edit1: 705082704
Edit2: 15834

MyDAC:
Edit3: 705082704
Edit4: 141
For me, in a similar example, it goes like this:

MySQL - ~1mln records, Delphi 10 Seattle - Win32 libmysql.dll - 5.5.40.0

ZEOS 7.3.x svn 5342:
1783293661
2219 msec

UniDAC 7.4.11:
1783293661
796 msec

2.5 times slower
Yours is 100 times slower.

Try it with another version libmysql.dll because it is scandalously slow.

Michal

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

Posted: 07.12.2018, 18:18
by EgonHugeist
@EsequiasTorres,

Hi and welcome to Zeos!

According the inserts:
How did you insert those records? I mean you'd say:
EsequiasTorres wrote:If I use prepared queries as you have suggested it takes 11 seconds to insert the 100,000 numbers, less time than MyDAC.
I want to know how you did that. On 7.3 i've optimized the prepareds for MySQL whereas on 7.2 it's not really faster, just the 0,02 cents if we'd beating MyDAC with the current component layer. Which Component did you use to insert the records? If it's the TZQuery/TZTable than a tip: omit the [doCalcDefaults] option in the component.

I don't know anything about MyDAC/Unidac. I know they are there .. that's all. Are those components unidirectional or do you have a scrollable cursor?
In mORMot you just need forwardonly (unidirectional called on the TDataSets). If they are forwardonly, than plz enable IsUnidirectional in your TZReadOnlyQuery. You could also switch of Metadata retrieving by switch of UseMetadata in your TZConnection object. Once done you can also force MySQL to an unidicrectional mode by adding
UseResult=True
to the TZDataSet-Properties.

To be clear Zeos is a full abstraction component set like BDE was. Abstraction in some cases costs time but makes live easier.
The current implementation of ZDbc <-> ZComponent could be more optimized. I know that. But this will happen first if my DBC optimization is done for 100%(all drivers). Actually it's a proof of concept (without breaking anything -> thanks to absatraction) and more changes are in queue..

Btw. it would be nice 4all to see the whole testcase, not just fragments. Than guiding your is much easier. Ergo plz post the whole testcase.

Ahhhh i forgot:
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).
Waiting 4 reply.

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

Posted: 16.12.2018, 20:54
by EsequiasTorres2
EgonHugeist wrote:@EsequiasTorres,

Hi and welcome to Zeos!

According the inserts:
How did you insert those records? I mean you'd say:
EsequiasTorres wrote:If I use prepared queries as you have suggested it takes 11 seconds to insert the 100,000 numbers, less time than MyDAC.
I want to know how you did that. On 7.3 i've optimized the prepareds for MySQL whereas on 7.2 it's not really faster, just the 0,02 cents if we'd beating MyDAC with the current component layer. Which Component did you use to insert the records? If it's the TZQuery/TZTable than a tip: omit the [doCalcDefaults] option in the component.

I don't know anything about MyDAC/Unidac. I know they are there .. that's all. Are those components unidirectional or do you have a scrollable cursor?
In mORMot you just need forwardonly (unidirectional called on the TDataSets). If they are forwardonly, than plz enable IsUnidirectional in your TZReadOnlyQuery. You could also switch of Metadata retrieving by switch of UseMetadata in your TZConnection object. Once done you can also force MySQL to an unidicrectional mode by adding
UseResult=True
to the TZDataSet-Properties.

To be clear Zeos is a full abstraction component set like BDE was. Abstraction in some cases costs time but makes live easier.
The current implementation of ZDbc <-> ZComponent could be more optimized. I know that. But this will happen first if my DBC optimization is done for 100%(all drivers). Actually it's a proof of concept (without breaking anything -> thanks to absatraction) and more changes are in queue..

Btw. it would be nice 4all to see the whole testcase, not just fragments. Than guiding your is much easier. Ergo plz post the whole testcase.

Ahhhh i forgot:
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).
Waiting 4 reply.
I am @EsequiasTorres. I’ve lost my password!
I found the recovery instructions in http://zeoslib.sourceforge.net/faq.php#f0r6.
I tried to recover my account, but it wasn't possible because the site doesn't offer password recovery options.

Source Code

Code: Select all

program Test;

uses FastMM4,
     MySQL in '..\MySQL.pas';

{$R *.res}

begin
     RunTest;
end.
Test.inc

Code: Select all

{$define Use_ZeosLib}

Code: Select all

unit MySQL;

{$Include ..\Test.inc}

interface

uses Forms, Dialogs, SysUtils, Classes, DateUtils,
     {$IfDef Use_ZeosLib}
     ZAbstractConnection, ZConnection, ZAbstractRODataset, ZAbstractDataset, ZDataset;
     {$Else}
     DB, DBAccess, MyAccess, MemDS;
     {$EndIf}

const Records = 100000;

var {$IfDef Use_ZeosLib}
    Connection1: TZConnection;
    Query1: TZQuery;
    {$Else}
    Connection1: TMyConnection;
    Query1: TMyQuery;
    {$EndIf}

procedure RunTest;

implementation

procedure RunTest;
var TimeBegin: TDateTime;
    Id: Integer;
    Log: TStringList;
    Sum: Integer;
begin
     ShowMessage('Begin test');
     Log:= TStringList.Create;
     try
        {$IfDef Use_ZeosLib}
        Connection1:= TZConnection.Create(Nil);
        Query1:= TZQuery.Create(Nil);
        Query1.Connection:= Connection1;
        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));

        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;

end.
Test

Processor: Core i3-2350M CPU @ 2.30GHz (4 CPUs)
RAM: 8 GB
Disk: SSD ADATA SP900
Operating System: Windows 7 Ultimate 64-bit SP1

MySQL: 5.0.18-nt

Delphi 7, MyDAC 5.20.0.11
Insert data, do not use prepared sql statement 10382 MilliSeconds
Insert data, use prepared sql statement 9451 MilliSeconds
Select and sum data, use the RecordCount property 112 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 109 MilliSeconds. Result 705082704

Delphi 7, Zeoslib 7.2.4-stable
Insert data, do not use prepared sql statement 24714 MilliSeconds
Insert data, use prepared sql statement 10835 MilliSeconds
Select and sum data, use the RecordCount property 15291 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 15611 MilliSeconds. Result 705082704

Delphi 7, zeoslib-code-0-r5355-branches-testing-7.3
Insert data, do not use prepared sql statement 27753 MilliSeconds
Insert data, use prepared sql statement, insert 3 records and raise Access violation at address 00000000. Read of address 00000000.
Select and sum data, use the RecordCount property 299 MilliSeconds. Result 1767748990
Select and sum the data, do not use the RecordCount property 296 MilliSeconds. Result 1767748990

Note: when I open the file:
zeoslib-code-0-r5355-branches-testinging-7.3\hi7\ZeosDbo.bpg packages
Delphi 7 does not correctly interpret line breaks, por lo tanto, lanza el error
projects macro in project group file missing or incorrect
It has been corrected by opening the file with another text editor and entering the corresponding line breaks.

Delphi 10.2, Zeoslib 7.2.4-stable
Insert data, do not use prepared sql statement 72971 MilliSeconds
Insert data, use prepared sql statement 11164 MilliSeconds
Select and sum data, use the RecordCount property 15388 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 15401 MilliSeconds. Result 705082704

Delphi 10.2, zeoslib-code-0-r5355-branches-testing-7.3
Insert data, do not use prepared sql statement 73380 MilliSeconds
Insert data, use prepared sql statement, insert 3 records and raise Access violation at address 00000000 in module 'Test.exe'. Read of address 00000000.
Select and sum data, use the RecordCount property 361 MilliSeconds. Result 705082704
Select and sum the data, do not use the RecordCount property 350 MilliSeconds. Result 705082704

Access violation on last line of zeoslib-code-0-r5355-branches-testing-7.3:

Unit:

Code: Select all

ZDbcMySqlStatement

Code: Select all

procedure TZAbstractMySQLPreparedStatement.FlushPendingResults;
Code:

Code: Select all

else if (FMYSQL_STMT <> nil) and FStmtHandleIsExecuted then begin
    (*if FPlainDriver.IsMariaDBDriver then begin  //mysql raises a out of sync here, even if docs saying they clear all pending results
      Status := FPlainDriver.mysql_stmt_reset(FMYSQL_STMT);
      if Status <> 0 then
        checkMySQLError(FPlainDriver, FPMYSQL^, FMYSQL_STMT, lcExecPrepStmt,
          ConvertZMsgToRaw(SPreparedStmtExecFailure, ZMessages.cCodePage,
            ConSettings^.ClientCodePage^.CP), Self);
    end else *)while true do begin //so we need to do the job by hand now
      Status := FPlainDriver.mysql_stmt_next_result(FMYSQL_STMT);


I have little time available for testing, but I will try to follow your recommendations.

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

Posted: 16.12.2018, 21:14
by EsequiasTorres2
I've done a little test and I find that something is wrong.

Code: Select all

CREATE DATABASE "dbtestzeos" /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish_ci */;

USE "dbtestzeos";

CREATE TABLE "tb_test_zeos" (
  "ID" mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  ("ID")
) /*!40100 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci*/;

Code: Select all

program Test2;

uses
  FastMM4,
  MySQLTest in 'MySQLTest.pas';     

{$R *.res}

begin
     RunTest;
end.

Code: Select all

unit MySQLTest;

interface

uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, DateUtils,
  Dialogs, DB, ZAbstractRODataset, ZAbstractDataset, ZDataset,
  ZAbstractConnection, ZConnection;

procedure RunTest;

implementation

procedure RunTest;
Const Records = 10;

var Connection1: TZConnection;
    Query1: TZQuery;
    TimeBegin: TDateTime;
    Id: Integer;
    Log: TStringList;
    Sum: Integer;
begin
     ShowMessage('Begin test');
     Log:= TStringList.Create;
     try
        Connection1:= TZConnection.Create(Nil);
        Query1:= TZQuery.Create(Nil);
        Query1.Connection:= Connection1;
        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');
        
        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;
             Log.Append('Value: ' + IntToStr(Query1.Fields[0].AsInteger));
             Log.Append('SUM: ' + IntToStr(Sum));
             Log.Append('');
             Query1.Next
        End;
        Log.Append('Select and sum data, use the RecordCount property ' + IntToStr(MilliSecondsBetween(TimeBegin, Now)) + ' MilliSeconds. Result ' +  IntToStr(Sum));
        Log.Append('Query1.RecordCount: ' + IntToStr(Query1.RecordCount));
        
        Log.SaveToFile(ExtractFilePath(Application.ExeName) + FormatDateTime('yyyy-mm-dd hh-nn-ss.zzz', Now) + 'Zeoslib ' + Connection1.Version + '.txt');
     ShowMessage('Test finished');
     finally
            Log.Free;
            Connection1.Free;
            Query1.Free;
     end;
end;

end.
Result:
Zeoslib 7.3.0-alpha
Insert data, do not use prepared sql statement 5 MilliSeconds
Value: 1
SUM: 1

Value: 2
SUM: 3

Value: 3
SUM: 6

Value: 4
SUM: 10

Value: 5
SUM: 15

Value: 0
SUM: 15

Value: 0
SUM: 15

Value: 0
SUM: 15

Value: 0
SUM: 15

Value: 10
SUM: 25

Select and sum data, use the RecordCount property 8 MilliSeconds. Result 25
Query1.RecordCount: 10

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

Posted: 17.12.2018, 14:31
by miab3
@ 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

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

Posted: 18.12.2018, 05:54
by EgonHugeist
miab3 wrote:I do not understand why this is happening?
I've downloaded the MySQL 5.0.18 Server. The libmysql simply does not contain the two methods

Code: Select all

mysql_stmt_next_result, mysql_next_result
which is a bit surpricing, We can create sp's returning multiple resultsets but we can't call them.
Patch done R5632 (SVN) merged to trunk already.
EsequiasTorres2 wrote:I am @EsequiasTorres.
I didn't wrote something else, did i?
How ever you did not answer one of my questions nor did you follow one of my advises. Could you plz consider them?