My needs
I work with Delphi/C++Builder XE2.
I need to access at least these DBMSs:
- Firebird
- DB2/400
- SQL Server
- SAP HANA (a new in-memory DB, available interfaces: JDBC, ODBC, ODBO, SQLDBC)
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.
My guesses
- Using ODBC performance will be poorer than using native drivers. If true, how can I overcome this issue?
- Even through ODBC, performances for HANA in-memory DB will be great (I can't test it now).
What I found so far
-
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
andTClientDataSet
)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:
- dbExpress or FireDAC: where will go Embarcadero in the future?
- dbGo: is it ADO a good choice? Seems that it relies on ODBC, so what about performance?
- a commercial product like UniDAC or Data Abstract: is it necessary? Would it be better?
Best Answer
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:
UniDAC - 0,909 seconds, ate 12 324 044 of memory
FireDAC - 0,967 seconds, ate 282 179 668 of memory (I'm shocked)
MySQL:
UniDAC - 0,363 seconds and 11 552 604 of memory
FireDAC - 0,713 seconds and 49 375 108 of memory
SQL Server:
UniDAC - 0,391 seconds and 14 155 576 of memory
FireDAC - 0,324 seconds and 51 775 844 of memory
Everything was measured simply:
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.