13

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?
bluish
  • 26,356
  • 27
  • 122
  • 180
  • "Shopping list" questions are not appropriate here. There are Meta posts [here](http://meta.stackexchange.com/q/139618/172661) and [here](http://meta.stackexchange.com/q/158809/172661) that explain the reasons. If you narrow it down to a couple of choices, and then ask a specific question about the difference between those choices, we can probably help. Voting to close as "not constructive". Good luck. – Ken White Apr 16 '13 at 02:53
  • Thanks @KenWhite, I'm trying to orient myself... Now I restricted my question – bluish Apr 16 '13 at 07:25
  • 1
    @bluish, while that would be somehow provoking flames (but doesn't the very question?) i'd say that: do you feel you have enought expertise to be co-developer of DB access library ? If yes - then go ZeOS and makes it better for everyone's benefit. If now you should stick with some commercial tools with good support and sound future. That left us with AnyDAC and UniDAC and i'd consider AnyDAC first, but now as AnyDAC perished, you only left with UniDAC option. I would not bet that FireDAC would have class of support and spped of developenmt that AnyDAC had. – Arioch 'The Apr 16 '13 at 08:53
  • Let's imagine that XE5-6-7-... would degrade in quality and/or license terms and/or price that you would seek refuge. With UniDAC and AnyDAC you can flee to some Lazarus, with FireDAC you can not. Now, about DataAbstract, i hheard that it is robust and flexible but not very fast, however you of course should do oyu own testing with your data, your hardware, your specific access patterns and such. – Arioch 'The Apr 16 '13 at 08:55
  • Now, you may choose to completely drop TDataSet and go some ORM like mORMot or Delphi-ORM or hfORM or whatever. TDataSet indirection level is spaghetti rooted in ISAM 16-bit code (Delphi1 + BDE + DBF/Paradox) and there are a lot of event handlers for developer's flexibility, but all this hampers speed. Actually, i think you DO NOT need fast library, for HDD and LAN would anyway lag behind CPU+RAM, but... your question quotes "good performances". So the question then goes whether ORM approach would work to you and whether some ORM abstraction layer would be not slower than TDataSet. Do test :-) – Arioch 'The Apr 16 '13 at 08:59
  • @Arioch'The You've been helpful.. I can't understand why you write your answer as a series of comments instead of an answer... This will confuse future readers. Please post it as an answer ;) – bluish Apr 17 '13 at 06:44
  • But there is no *answer* - just a bunch of random thoughts, some cons and pros without common idea "ruling them all". Those are actually an almost detached comments, not some solid point of view :-) – Arioch 'The Apr 17 '13 at 07:34
  • Oh, one more random thought about "Using ODBC performance will be poorer than using native drivers" - that is true if using yet another "database abstraction layer" upon ODBC like UniDAC, ADO or DBX (though is that important ? network and HDD are still slower than CPU and RAM). However, what if to search for database library targeted directly at ODBC from beginning. Granted, it probably had long since updated and maybe you would need to be co-developer then, but at least that is one mroe option to consider. http://www.torry.net/quicksearchd.php?String=ODBC&Title=No – Arioch 'The Apr 17 '13 at 07:39

5 Answers5

9

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.

JackD
  • 597
  • 4
  • 7
  • (1) Look at how DAC defines column data types. Then you can use [FireDAC mapping rules](http://docs.embarcadero.com/products/rad_studio/firedac/Data_Type_Mapping.html). Eg, map dtFmtBCD to dtDouble, dtSQLTimeStamp to dtDateTime. That may radically reduce memory usage. 2) Try to reduce [FormatOptions.InlineDataSize](http://docs.embarcadero.com/products/rad_studio/firedac/uADStanOption_TADFormatOptions_InlineDataSize.html). Because FireDAC for VARCHAR(200) will allocate 200 или 400 bytes (when Unicode), but really your test writes only 10-15 characters. – da-soft May 22 '13 at 17:32
3

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.

bluish
  • 26,356
  • 27
  • 122
  • 180
Bruce McGee
  • 15,076
  • 6
  • 55
  • 70
  • Thanks! 1. Why dbExpress doesn't support HANA? HANA has an ODBC driver. 2. Unidirectional datasets would be difficult to migrate to, from our current TIB* components. Am I wrong? 3. Does FireDAC have uni- or bi-directional datasets? – bluish Apr 15 '13 at 15:53
  • 1) dbExpress doesn't support HANA specifically, but you should be able to connect using ODBC. 2) I don't think it would be difficult to migrate. I tend to use ClientDataSets all the time. 3) I believe FireDAC datasets are bidirectional. – Bruce McGee Apr 15 '13 at 17:06
  • Funny, you see FireDAC as a good warranty of its future, and i see it as a huge risk to loose development steam and support quality (sooner or later the bugs would not be fixed before next paid upgrade rounds). And the vendor lock-in already shifted. using AnyDAC or UniDAC you can hope to escape from Delphi wagon if need to and switch to Lazarus (would not be easy, but at least you can try). With FireDAC you put all your bets onto EMBA good will and good luck... – Arioch 'The Apr 17 '13 at 05:48
2

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.

Vector
  • 10,879
  • 12
  • 61
  • 101
  • @bluish:ADO does not use ODBC. It is the 'successor' to ODBC -framework is there from Win XP onward (I believe...) With good provider it is easy to set up:save blank text file with UDL extension, click on it. Metrics out there that ODBC is faster for certain ops. But you know ODBC is a pain & limited. Delphi has an ADO_TLB.pas-can link into your project, trace, examine, etc. I've found it to be transparent-just have to know something about COM datatypes. You often have to pay a few bucks for a good ADO provider. Just bought one for PosteGreSQL:$100:money well spent. – Vector Apr 16 '13 at 07:37
  • 2
    ADO may use ODBC like BDE did. But #1 back-end for ADO is not ODBC but OLE DB. However, given that native ADO (in contrast with ADO.Net) did not saw updates for long, given that Microsoft SQL deprecated OLE DB support, i wonder if ADO future are stable long-term. – Arioch 'The Apr 16 '13 at 09:01
  • @Arioch'The - as far as I know, ADO isn't supposed to use ODBC although I suppose you could write a provider that wraps ODBC. ODBC, is ancient but still works, and I have never had problems with ADO either. ADO is stable and documented, as it has been for many years-I still write new applications with it without issue, even in Win 7. If they break it, they will break thousands of applications (not that this bothers MS....) But SQLServerNative Client also has an ADO provider (others too). I don't think they use OLEDB. I recommended ADO as the easiest, most available, reliable data access. – Vector Apr 16 '13 at 15:51
  • "ADO is basically a high-level OO interface to various low-level OLEDB data providers. ODBC is another kind of low-level data provider, and if there is no native OLEDB provider for the type of database you use, but there is an ODBC one, you can always use the OLEDB provider for ODBC to bridge that gap." http://www.perlmonks.org/?node_id=117694 – Arioch 'The Apr 16 '13 at 16:02
  • "However, it can indirectly use an ODBC driver because there is this special OLEDB provider called the MSDASQL which can connect to an ODBC driver. In other words, the MSDASQL OLEDB provider can serve as a bridge between ADO and ODBC by acting as an OLEDB provider on its ADO side and as an ODBC client on its other side. This is the OLEDB provider that's used when you open an ADO connection by using a DSN or an ODBC connection string." http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/edf9c54e-f74b-4071-8987-5028ea3406c0/ – Arioch 'The Apr 16 '13 at 16:03
  • http://msdn.microsoft.com/en-us/library/ms810810.aspx#bkmk_DepMDAC_WDACComps http://blogs.msdn.com/b/adonet/archive/2011/09/13/microsoft-sql-server-oledb-provider-deprecation-announcement.aspx http://weblogs.sqlteam.com/dang/archive/2011/09/04/rip-ole-db.aspx While it might have little influence on ADO.Net i wonder how can Native ADO work without OLE DB provider. – Arioch 'The Apr 16 '13 at 16:04
  • @Arioch'The - good references. I see when you make an a defualt UDL file, the MS OLD DB provider for ODBC is listed there. Never noticed it :-) - I always just look for the provider I need, which is never ODBC. ADO.net is misleading name - it actually has nothing to do with legacy ADO - see http://msdn.microsoft.com/en-us/library/h43ks021%28v=VS.71%29.aspx . – Vector Apr 16 '13 at 17:07
  • @Arioch'The - by default, you get an OLEDB SqlServer Provider. If you install SQLServer Client, you get a 'native SQL Server client' provider in addition. – Vector Apr 16 '13 at 18:07
  • yes, i know about ADO.Net being different, for example http://blogs.msdn.com/b/data/archive/2006/12/22/data-access-api-of-the-day-part-iii-disconnected-programming-in-a-managed-environment.aspx "Default" one (actually MDAC one) is told to not work with SQL Servernewer than v.7 and "native" would not more be shipped and even in case of upgrade who knows how forward-compatible it would turn to be. ADO as a third-party adopted framework definitely has its merits, but as a mean to access MS SQL is is not a good choice for new projects, that need a long future, IMHO – Arioch 'The Apr 17 '13 at 05:41
  • @Arioch'The - I'm not up to date myself - leave it MS to keep moving the goalposts... try to force everyone who uses SQLServer to go with .NET I suppose... That's why I'm desperately looking for a viable development environment on Linux. So spoiled by the high end IDe's like VStudio and Delphi XE. Lazarus and MonoDevelop are both crap IMO. – Vector Apr 17 '13 at 05:50
  • Oxygene ? i hope it can target Mono/DotGNU ? PascalABC would probably also be a crap for you... I heard of a project to ad GNU Pascal to Eclipse, but that would probably also beginner-grade. Maybe time to switch to more advanced languages like Scala or Nemerle ? – Arioch 'The Apr 17 '13 at 06:11
  • Right now I'm using CodeLite for C++ on Ubuntu - very nice little IDE that I just found out about on here. Have QT installed also. QT/C++ is VERY powerful. There is also QT creator for C++ but it is bit heavy very 'decorated' and too bound op with QT. I happen to like C++ as a language - and QT provides GUI's and DB access. So far this is best option I've found - just found CodeLite the other day - it's quite nice and it's a 'live' project - they just released an update this month - and can't beat the price... – Vector Apr 17 '13 at 08:37
  • @Arioch'The : After a **lot** of messing around, for me, codelite or code:blocks/glade/gtkmm seems to be about the best way to do if you want a powerful development tool chain on Linux/C++ that includes generating GUI's without writing hundreds of lines of GUI code. There IS a learning curve. Messed with Qt quite a bit and it's excellent, but it forces you to use Qt classes and datatypes for a lot of things, and subverts generic C++. gtkmm can be used with entirely generic C++. – Vector Jun 28 '13 at 16:46
0

Firedac-Comes with Delphi,So probably you´ll have to pay for it anyway. UNidac-Faster and let you not have to upgrade Delphi so frequentely,in order to have the data Acess being update,also if you one day decide to go to Lazzarus you save a lot of work too.

user3145857
  • 27
  • 1
-2

There are many misconceptions in many of the threads so I´ll try to give my 2 cents. The problem is that we Delphians used to think in executables,so we look for ways of moving data(specially when our database and application server are hosted outside); These scenarios will never beat a RIA application, because you don´t move data anymore,but just a representation of the screen. Another misconception is saying that TDATASET does not work well with AJAX. Just see some UNIGUI examples on youtube. BTW I'm using ordinary DataSnap with a clientdataset, not ORM.

Morteza Tourani
  • 3,506
  • 5
  • 41
  • 48
Marcello Dias
  • 11
  • 1