Hi all, To see what the differences in speed are between MSEdb and fcl-db I did some benchmarks. (See the attachment for the code and results)
I think that the first conclusion is that when it comes to executing queries, opening connections to the database and such, the 'real sql- work' there's no real difference. Which was to be expected, since the code in that areas is practically the same. But as soon the TBufDataset is used in the area it's designed for, there are differences. Also not that strange, since this is the part that is rewritten in MSEdb. If you use a large dataset, (100000 records, about 2020 bytes for each record) simply opening the dataset and fetching all records is 7 times slower with MSEdb. (OpenFetch test) Further, when you 'scroll' through all records, and read-out all data, MSEdb needs more time for every record. (ReadData test, see graph) The result is that if the dataset is opened, and scrolled from the begin to the end and back again once, fcl-db is 6 times faster. If you do this 10 times, fcl-db is 'only' three times as fast. But the absolute time difference is larger. If you open this same dataset, end change the first 20.000 records. MSEdb is faster then fcl-db. (about 1.2 times) Thus you can conclude that with editing large amount of datasets, msedb is faster, it so fast that it overcomes the browse-slowlyness. I think that the reason for the difference in editing is simple: to spare memory fcl-db doesn't store the location of the edit-buffer with each record-buffer. So it has to search the edit-buffer for every record you want to change. I did that on purpose, since I thought that editing this amount of records, without applying the changes, isn't something that you do in a regular program. If it comes to the use of RecNo, msedb beats fcl-db. Fetching the data msedb is slower, offcourse. but then for each 'jump' it consumes almost no time at all. (see JumprecNo graph, the first 'dot' is more a randomness-thing if you use only 5 points) But, on the other hand, if you use bookmarks to do the same thing. fcl- db behaves just like msedb. Jumping through the records is fast. I tried to explain the readdata-difference. At first I thought that msedb converted all strings from string to widestring to string. But it doesn't do that. (see my mails on this subject) I think that the only reasonable explanation is the argument from Michael: using ansistrings/pointers to store the data instead of storing them directly into a buffer, induces so much overhead that it becomes too slow. Overall, I would conclude that the current design of the TBufDataset of fcl-db is a better choice then the design of tmseBufDataset. Since fcl- db is faster on those things a TBufDataset is build/meant for: browsing through records. Discussion: What tests could I do more? Is there something I overlooked? Regards, Joost.
program bench;
{$mode objfpc}{$H+}
{ $Define SQLDB}
uses
cthreads, Classes, SysUtils, db,
{$IFDEF SQLDB}
sqldb, ibconnection;
{$ELSE}
msqldb,mseibconnection;
{$ENDIF}
var AConn : TSQLConnection;
ATrans : TSQLTransaction;
AQuery : TSQLQuery;
procedure CreateTable;
begin
try
AConn.ExecuteDirect('drop table benchtest');
except
// do nothing
end;
AConn.ExecuteDirect('create table benchtest (id int, shortstr varchar(10), longstr varchar(2000))');
ATrans.CommitRetaining;
end;
procedure FillData;
function GetRandomStr(MaxSize : Integer) : String;
var b,size : integer;
a : string;
begin
Size := Random(MaxSize);
SetLength(a,size);
for b := 1 to Size do
a[b] := chr(Random(28)+65);
Result := a;
end;
var i : integer;
begin
Randomize;
AQuery.SQL.Text := 'insert into benchtest (id,shortstr,longstr) values (:id,:shortstr, :longstr)';
for i := 0 to 100000 do
begin
AQuery.params[0].asinteger := i;
AQuery.Params[1].AsString := GetRandomStr(10);
AQuery.Params[2].AsString := GetRandomStr(2000);
AQuery.ExecSQL;
end;
ATrans.CommitRetaining;
end;
Procedure ReadData(times : integer);
var s : string;
i : integer;
begin
AQuery.SQL.Text := 'select * from benchtest';
AQuery.Open;
for i := 0 to times -1 do
begin
while not AQuery.EOF do
begin
s := AQuery.Fields[1].AsString;
// writeln(s);
s := AQuery.Fields[2].AsString;
// writeln(s);
AQuery.Next;
end;
while not AQuery.BOF do
begin
s := AQuery.Fields[1].AsString;
// writeln(s);
s := AQuery.Fields[2].AsString;
// writeln(s);
AQuery.Prior;
end;
end;
AQuery.Close;
end;
Procedure OpenFetch;
var s : string;
begin
AQuery.SQL.Text := 'select * from benchtest';
AQuery.Open;
AQuery.Last;
AQuery.Close;
end;
Procedure JumprecNo(jumps : integer);
var s : string;
i : integer;
r : integer;
begin
AQuery.SQL.Text := 'select * from benchtest';
AQuery.Open;
for i := 0 to jumps do
begin
r := Random(99999);
AQuery.RecNo := r;
if AQuery.Fields[0].asinteger <> (r-1) then writeln('He');
end;
AQuery.Close;
end;
Procedure JumpBookmark(jumps : integer);
var s : string;
i : integer;
r : integer;
bms : array[0..9999] of TBookmark;
begin
Randomize;
AQuery.SQL.Text := 'select * from benchtest';
AQuery.Open;
i := 0;
while not AQuery.EOF do
begin
inc(i);
if (i mod 10)=0 then bms[i div 10] := AQuery.GetBookmark;
s := AQuery.Fields[1].AsString;
// writeln(s);
s := AQuery.Fields[2].AsString;
// writeln(s);
AQuery.Next;
end;
for i := 0 to jumps do
begin
r := Random(9998)+1;
AQuery.GotoBookmark(bms[r]);
if AQuery.Fields[0].asinteger+1 <> (r*10) then writeln('He');
end;
AQuery.Close;
end;
Procedure OpenClose(count : integer);
var i : integer;
begin
AQuery.SQL.Text := 'select * from benchtest where id < 15';
for i := 0 to count -1 do
begin
AQuery.Open;
AQuery.Close;
end;
end;
Procedure EditData(Records : longint);
var s : string;
i : integer;
begin
AQuery.ParseSQL := True;
AQuery.ReadOnly := False;
AQuery.SQL.Text := 'select * from benchtest';
AQuery.Open;
for i := 0 to Records -1 do
begin
AQuery.Edit;
AQuery.Fields[1].AsString := 'TEST';
AQuery.Fields[2].AsString := 'TESTING123TESTING123TESTING123TESTING123TESTING123TESTING123TESTING123';
AQuery.Next;
if AQuery.EOF then break;
end;
while not AQuery.BOF do
begin
s := AQuery.Fields[1].AsString;
if s <> 'TEST' then writeln('PROBLEM');
s := AQuery.Fields[2].AsString;
if s <> 'TESTING123TESTING123TESTING123TESTING123TESTING123TESTING123TESTING123' then writeln('PROBLEM2');
AQuery.Prior;
end;
AQuery.Close;
end;
begin
{$IFDEF SQLDB}
AConn := tibconnection.Create(nil);
{$ELSE SQLDB}
AConn := tmseibconnection.Create(nil);
{$ENDIF SQLDB}
AConn.UserName := 'sysdba';
AConn.DatabaseName := '/opt/firebird/data/testdb.fdb';
AConn.Password := 'password';
// AConn.DatabaseName := '192.168.3.1:/home/firebird/fpctest.fdb';
// AConn.Password := 'password';
ATrans := TSQLTransaction.Create(nil);
ATrans.DataBase := AConn;
AConn.Transaction := ATrans;
AQuery := TSQLQuery.Create(nil);
AQuery.Transaction := ATrans;
AQuery.DataBase := AConn;
AQuery.ParseSQL := False;
// CreateTable;
// FillData;
// JumprecNo(500);
// ReadData(10);
// OpenFetch;
JumpBookmark(50);
// EditData(20000);
// OpenClose(50);
end.
db_benchmarks.ods
Description: application/vnd.oasis.opendocument.spreadsheet
_______________________________________________ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
