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
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
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.
