Efficient database access to various DBMS from Delphi XE2

Question

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

1. Using ODBC performance will be poorer than using native drivers. If true, how can I overcome this issue?
2. 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 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:

• 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?

Answer 1

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.

Answer 2

If you are using XE2, I would recommend dbExpress.

• It supports ODBC (but not for SAP HANA)
• Unidirectional datasets can be used with ClientDataSet for caching. In fact, ClientDataSets can be used to cache any dataset component.

If you are using XE3 or later, I would recommend FireDAC.

• Embarcadero purchased AnyDAC and have renamed it FireDAC.
• It is included with the Enterprise SKU and above. A free download is available for licensed XE3 users.
• I believe this will be their data access strategy going forward. See this recent blog post.

I understand FireDAC can be used with XE2, but I'm not sure if there are any issues.

Answer 3

I always use ADO - used it with SQLServer, Oracle, Sybase, PostGreSQL and others. You can find an ADO provider for just about any database. Never had a problem I was not able to work out with a bit of research. Since ADO is so widely used, most of the problems are well known. And UDL files can make your life a lot easier.

But I never use the Delphi ADO components on the component palette - either I use them in memory, or more often, dump the results of the ADO calls directly into a TKBMMemtable and avoid the Delphi 'out of the box' stuff entirely. You can write a utility function that will do it automatically for you.