Database connectivity frameworks in Delphi

Since its very first version, Delphi has provided great database components, for the applications to access SQL servers like MS SQL Server and Oracle. So we can say that it is one of the main values of the product, well established from its birth.

This extraordinary capability to connect to the most popular database engines (including NoSQL engines), combined with the terrific performance of the operations executed using these components, makes Delphi the perfect choice for developing data-rich applications.

There are several connectivity frameworks currently available out of the box in Delphi (and C++ Builder): BDE (through a separate download), dbGo (ADO), dbExpress and FireDAC. I’ve listed them in chronological order (by release date). There are others great 3rd party database components, but they’re out of scope for the purpose of this post. I’m also excluding the IBX components as they’re tied to Interbase and Firebird, and they are not universal data access components. But IBX is a great choice if the application will connect only to those DB engines.

BDE

The Borland Database Engine (BDE) is the first data-access mechanism that was included in Delphi. It was a great engine in the past, but technology runs fast and it was deprecated in RAD Studio XE7 (2014).

It can only be used for 32bit Windows VCL applications (no 64 bits) and does not have Unicode support.

But if you have a legacy application using BDE, the most recent versions of Delphi still include it as a separate download: http://cc.embarcadero.com/item/30359 (for RAD Studio 10 Seattle).

You may also want to consider migrating your BDE applications to FireDAC (highly recommended). Here they are  several links to help you getting started:

• Migrating BDE Applications to FireDAC
• http://blog.marcocantu.com/blog/refind_and_bde_migration.html
• YouTube – FireDAC Skill Sprints – Migrating BDE applications to FireDAC

If you’re still thinking if moving away from BDE worth the effort, you will find some interesting performance comparisons later in this post.

dbGo (ADO)

The dbGo components provide data access through the ADO framework. ADO, (Microsoft ActiveX Data Objects) is a set of COM objects that access data through an OLE DB provider. The dbGo components encapsulate these ADO objects in the Delphi database architecture.

Windows is the only supported target platform for VCL and FMX applications (32 and 64 bits). Obviously, this technology isn’t available for OS X, iOS and Android operating systems.

dbExpress

dbExpress is Embarcadero’s data driver architecture that replaced the BDE. First released with Borland Delphi 6 and C++Builder 6, it has gone through several iterations itself. It provides unidirectional database access, that means you can traverse data obtained from a database table only in the forward direction. You cannot go back, unless you add other layers. This makes dbExpress a very fast access technology, compared to many others.

It can be used in VCL and FMX Windows applications (32 and 64 bits), but also for Mac OS X applications (more information about deploying dbExpress database applications: Deploying dbExpress Database_Applications), and provides local database connectivity for iOS and Android mobile applications (Using InterBase ToGo with dbExpress (iOS and Android))

But dbExpress is being deprecated. This means that it will be removed from RAD Studio in an upcoming release. To migrate your dbExpress application to FireDAC, you can find useful information in this link: Migrating dbExpress Applications to FireDAC

FireDAC

FireDAC is a Universal Data Access library for developing applications for multiple devices, connected to enterprise databases. And its the recommended database access framework in Delphi, C+ + Builder and RAD Studio on the latests releases.

It provides high-performance, easy-to-use enterprise database connectivity. Universal data access with database specific features to get the most from every specific database engine. Deploying the final application is way easier as it does not requires any drivers to be installed.

It can be used in VCL and FMX Windows applications (32 and 64 bits), for Mac OS X applications, and for local database access in iOS and Android.

For detailed information about FireDAC, please check the following links:

• https://www.embarcadero.com/products/rad-studio/firedac
• http://docwiki.embarcadero.com/RADStudio/en/FireDAC

 

Performance comparison

I’ve created a simple Windows VCL application (with RAD Studio 10 Seattle) to perform a bunch of SQL operations, connected to a couple of SQL Server databases running in different environments. I’ve used MS SQL Server as the target database engine as it’s the most used database enterprise server among Delphi programmers.

The results shown in this post are merely informative, and should not be used per se to take any enterprise nor technical decission. If these results make you to consider migrating to another data access framework, I strongly encourage to perform your own testing, as the performance gain (or loss) depends on how your application uses the data, and the SQL and actions performed.

The time to perform a database operation depends on several factors (server load, disk activity, free memory available, etc…). This means that these tests will show some peaks and lows here and there, I launched the same SQL operations several times (as you can see) to try to minimize external factors, and to get a meaning average value for every framework/test.

The first test consists in opening 10 Select queries, for 14 times. And there is a clear winner: FireDAC. Surprisingly, the BDE framework performs quite well and it’s the runner up.

If we take the total FireDAC time as 1, then here there is the relative results table:

BDE	ADO	dbExpress	FireDAC
1,40	1,61	1,41	1

The second test consists in opening a different set of 10 Select queries, again for 14 times. FireDAC wins again, but there’s no significant difference.

My impression is that FireDAC will probably work better for most of the open query operations, but performance gain will also depend on the query performed (I don’t  have enough date to get to a conclussion, it could depend on the number of fields on the dataset, number of records retreived, etc…). More tests about this, below in this post.

And the relative results table for this test:

BDE	ADO	dbExpress	FireDAC
1,08	1,06	1,01	1

For the next test I’ve performed a massive insert operation in the database. The tests performs a 1000 records insert operation. In this test there is a comparison between ADO (using a single SQL sentence: Stackoverflow: inserting multiple rows in a single sql query), and FireDAC (using the Array DML feature: Embarcadero docwiki: Performance of the Array DML Command (FireDAC) ).

The performace of the Array DML is outstanding. And the average time to perform 1000 inserts using ADO insert Sql sentence is 1,57 times the average time when using FireDAC Array DML, that provides another great benefit: it can be used to insert (or update or delete) more than 1000 records in one operation. Trying to do that using a single Sql sentence, you get a «The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.» error on MS SQL Server.

The following graphs will show the same tests but executed in a 64 bit VCL applications. There are no results for the BDE framework as it’s not 64bit compatible.

ADO	dbExpress	FireDAC
1,31	1,17	1

 

ADO	dbExpress	FireDAC
1,23	1,12	1

 

 

For the next set of test, let’s compare opening 59 Select SQL queries on a completely different SQL Server database. I ran the test 3 times in 32bit, and another 3 times on 64bit.

FireDAC performs significantly better than ADO in this test.

The next test consists in opening and looping through all the records on those 59 queries. I’ve navigated through the ADO query using 2 different techniques: the regular Next method, and the Recordset.MoveNext method. Using the second method can make a huge difference, more info about this:

• ‘Serious’ ADO Speed Techniques with Delphi http://edn.embarcadero.com/article/27790
• http://stackoverflow.com/questions/2312444/delphi-ado-query
• http://stackoverflow.com/questions/8016595/delphi-performance-reading-all-values-under-a-field-in-a-dataset

Let’s see the graphs.

And now the relative results table for this test:

FireDAC	ADO .MoveNext	ADO .Next
W32
1	1,1	1,59
W64
1	1,06	1,49

Conclussion: FireDAC wins another round.

 

And the last test consists in opening a single query and fetch all its 22614 records.

And, again, FireDAC performs slightly better than ADO. As can be seen in the table:

FireDAC	ADO
1	1,05

Final thoughts

Delphi always had great database connectivity technologies. IMHO, one of its top features, providing better flexibility and performance than its competitors.

And the latest versions of the product come with FireDAC: an absolute brilliant database connectivity framework. I won’t describe all its great features in this blog post, as there is a lot of information on the Embarcadero website, and several awesome videos on YouTube.

If you have an application that is still using BDE (or even dbExpress as it’s deprecated now), you may want to consider migrating to FireDAC, and here there are some reasons to give it a try:

• x64 bit target platform (only in case of BDE, as dbExpress is x64 compatible)
• Unicode support (only for BDE, as dbExpress supports Unicode)
• Easier deployment
• Faster data access (most likely)
• Array DML Execution, and other performance features like Live Data Window or Asynchronous Command Execution
• Automatic Connection Recovery
• NoSQL MongoDB connectivity

 

Thanks for reading.