I work with Delphi/C++Builder XE2.
I need to access at least these DBMSs:
I need to show and edit data in data-aware visual controls. Data can reside on any of these DBMS, I'll configure connection properties and SQL statements on an external text file.
So I'm looking for a set of components for database access which supports such DBMSs and has good performances, similar to old Paradox tables.
BDE (Borland Database Engine) (TDatabase
, TTable
...)
Deprecated.
DBX (Embarcadero dbExpress) (TSQLConnection
, TSQLTable
...)
Replaces BDE, unidirectional datasets (cursor goes only ahead; doesn't buffer data in memory, such a dataset cannot be displayed in a DBGrid; to build a user interface using dbExpress you will need to use two more components: TDataSetProvider
and TClientDataSet
)
Uses native drivers (none for HANA) or ODBC.
FireDAC (Embarcadero Fire Data Access Components) (TADConnection
, TADTable
...)
It's the continuation of AnyDAC; uses native drivers (none for HANA) or ODBC or dbExpress.
UniDAC (Devart Universal Data Access Components)
Not free; uses native drivers (none for HANA) or ODBC or "DB Client".
DA (RemObjects Data Abstract for Delphi)
Not free.
ZDBC (Zeos Database Connectivity Interface) (TZConnection
, TZQuery
...)
Open source; started as a port of JDBC to Object Pascal; doesn't provide binding with data-aware visual controls.
dbGo (Embarcadero dbGo) (TADOConnection
, TADOTable
...)
Implements ADO (hence over OLE DB over ODBC). Has a number of quirks, like with repeating same-named parameters in queries.
Jv BDE (TJvQuery
, TJvBDESQLScript
...)
Enhancement of correspondent standard library.
Jv Data Access (TJvADODataset
, TJvADOQuery
...)
Enhancement of correspondent standard library.
(feel free to enhance this list)
So my choice is amongst:
I decided to conduct a little performance research: UniDAC (5.0.1) vs FireDAC (8.0.1), on Delphi XE3. Databases: Firebird, MySQL & SQL Server.
Here are the 150k records fetch results (memory usage was considered as the difference between before and after fetching).
Firebird:
CREATE TABLE TEST_PERF (
ID INTEGER PRIMARY KEY,
VC VARCHAR(200),
NM NUMERIC(18,2),
DT TIMESTAMP
)
UniDAC - 0,909 seconds, ate 12 324 044 of memory
FireDAC - 0,967 seconds, ate 282 179 668 of memory (I'm shocked)
MySQL:
CREATE TABLE TEST_PERF (
ID INTEGER PRIMARY KEY,
VC VARCHAR(200),
NM NUMERIC(18,2),
DT DATETIME
)
UniDAC - 0,363 seconds and 11 552 604 of memory
FireDAC - 0,713 seconds and 49 375 108 of memory
SQL Server:
CREATE TABLE TEST_PERF (
ID INTEGER PRIMARY KEY,
VC VARCHAR(200),
NM NUMERIC(18,2),
DT DATETIME
)
UniDAC - 0,391 seconds and 14 155 576 of memory
FireDAC - 0,324 seconds and 51 775 844 of memory
Everything was measured simply:
function MemoryUsed: Cardinal;
var
st: TMemoryManagerState;
sb: TSmallBlockTypeState;
begin
GetMemoryManagerState(st);
Result := st.TotalAllocatedMediumBlockSize + st.TotalAllocatedLargeBlockSize;
for sb in st.SmallBlockTypeStates do
Result := Result + sb.UseableBlockSize * sb.AllocatedBlockCount;
end;
UniQuery1.SQL.Text := 'select * from test_perf';
UniQuery1.SpecificOptions.Values['FetchAll'] := 'True';
mem := MemoryUsed;
tc := Now;
UniQuery1.Open;
UniQuery1.Last;
tc := Now - tc;
mem := MemoryUsed - mem;
Memo1.Lines.Add('UniDAC Firebird: Time: ' + FloatToStr(tc * 24 * 60 * 60) + ' sec; Memory used: ' + IntToStr(mem));
ADQuery1.SQL.Text := 'select * from test_perf';
ADQuery1.FetchOptions.Mode := fmAll;
mem := MemoryUsed;
tc := Now;
ADQuery1.Open;
ADQuery1.Last;
tc := Now - tc;
mem := MemoryUsed - mem;
Memo1.Lines.Add('FireDAC Firebird: Time: ' + FloatToStr(tc * 24 * 60 * 60) + ' sec; Memory used: ' + IntToStr(mem));
If anyone is interested, here is the test application, you can add there performance comparison for ADO, dbExpress, ZeosLib, and others you are interested in.