Add How I chose universal data access components for my project
Our project Business&Finance started in 1999. We needed to work with various DBMSs. Therefore we used the uncontested BDE as a data access technology. After the Firebird DBMS was released, we had no need to support several servers, since everything we required from databases we could implement on a Firebird server. Our clients rose, increased the number of employees (database connections), went to clock mode and here we faced a problem with garbage accumulation and the need to occasionally run garbage collection in the exclusive mode. But a client wanted to work nonstop, so we encountered the need to refactor the whole project in order to use competitive features of InterBase and Firebird servers: to work with several transactions in one connection (to read data in a long Read-Only transaction without garbage accumulation, to modify data in a short Write transaction). It was the time to change the data access technology.
Year 2005. The Borland company had abandoned development of BDE in 2001, released the new universal data access technology dbExpress and ensured that it was cool, forever, and everyone would be happy, and BDE - is deadlock and hogwash. We believed them and accepted dbExpress. The happiness turned out to be on paper only we had to shovel dbExpress sources in order to increase system performance up-to the BDE level, find an optimal driver (it was unreal to make Borland refine their own driver).
For us to organize work with various transaction types, the Upscene company improved their driver (Custom transaction parameters were added). Everything was fine, clients worked with no days off, but some modes of large and heavy data updates worked noticeably slower than the old project on BDE. I annoyed Martin at Upscene long and unsuccessfully to make him speed up his driver, until someone on the Upscene's forum pointed to a driver by Devart. I tested it and found out this driver to work 10% faster on average.
Devart developers enhanced their driver for us (Custom parameters transaction were added). So, since 2010, we have been using their driver in our Business&Finance system. Time doesn't stand still. 12 years passed since the day our beloved Delphi 7 was released. We already have a Delphi XE5 Professional license (purchased for another project). So what's the idea that makes me not sleep a wink? Exactly what if we migrate our project Business&Finance to Delphi XE5?
Practice makes perfect: 24 hours of coding and here it is compiled and running, but there is no happiness as always. Delphi developers had updated dbExpress twice from the 2nd version to 4th: firstly, they killed flexible transaction management for each query (the TransactionLevel property disappeared), then they just bought a new toy, the AnyDAC technology, named it FireDAC, and said we should refuse dbExpress (Do only I have a deja vu?). And we want to refuse, but cant. So now we have to search again.
The Business&Finance system is designed for automation of business processes for wholesale and retail traders, food and electronic devices production, catering (clubs, pubs, restaurants, eating joints). The major part of screen forms are created in run-time basing on metadata, that describes entities. Data access components are descendants from dbExpress. The client application runs on Windows. There are several narrowly focused mobile applications for Windows CE, Android and iOS. The system is installed and works on more than 3000 workstations, somewhere in clock mode, throughout Russia. Our main feature is work with wide variety of goods (some clients have over 1 million items) that's why many our clients are bookselling companies.
The system is supported both by partners and directly by our company. System updates have been released weekly for the past 14 years. So we do care reliability above everything.
Project migration investments refund is not expected we do it for love of the beautiful. Decision making process
Here is the candidate list:
FIBPlus by Devrace FireDAC by Embarcadero
IBDAC and UniDAC by Devart.
I have learned the interfaces, wrote several tests of working with large data fetches, working in cached updates mode with parallel transactions. Below are the average test results:
FIBPlus Opens a query - Time 0.04 sec Memory 6.6 mbFetching 125 000 records to memory - Time 5.486 sec Memory 212 mb Modifying 125 000 records in cache - Time 5.994 sec Memory 420 mb ApplyUpdates - Time 65.194 sec. Memory 430 Mb
FireDAC (FireDAC Client/Server Pack for Delphi Professional)
Opens a query - Time 0.07 sec Memory 6.5 mb Fetching 125 000 records to memory - Time 5.069 sec Memory 280 mb Modifying 125 000 records in cache - Time 6.690 sec Memory 540 mb ApplyUpdates - Time 97 sec. Memory 665 Mb
Opens a query - Time 0.01 sec Memory 6.4 mb Fetching 125 000 records to memory - Time 4.655 sec Memory 305 mb Modifying 125 000 records in cache - Time 6.519 sec Memory 534 mb ApplyUpdates - Time 55.576 sec. Memory 534 Mb
Opens a query - Time 0.01 sec Memory 6.3 mb Fetching 125 000 records to memory - Time 4.700 sec Memory 305 mb Modifying 125 000 records in cache - Time 6.460 sec Memory 534 mb ApplyUpdates - Time 47.906 sec. Memory 534 Mb
- FIBPlus consumes the least memory, but this is the reason why it is not suitable for us it has a too specific TFIBXSQLVAR instead of TParam, so we would have to rewrite a lot of code. - IBDAC and UniDAC (for Firebird) ran neck and neck, which means that the universal framework of UniDAC is designed with high quality and its universality doesn't lead to losses in performance. Components by Devart showed a bit higher performance, and, according to preliminary estimates, it is easier to migrate the project to these components than FIBPlus. And since we have implemented integration with a third-party hotel management system in one object and therefor connect to a MSSQL database directly UniDAC is preferable: we will have less problems then. Another quite remarkable option is Prepare for auto-generated queries. dbMonitor shows that the same statement is executed multiple times on the server side, that will certainly decrease server load.
- FireDAC turned out to be the most voracious in memory consumption. This might be resolved by tuning memory mapping for each SQL query. But in our system queries are generated in run-time using metadata, that describe entities for business procedures ï¿½ and there will be a lot of work and experiments to implement automatic mapping rules generation. In my estimation these components are even easier to migrate.
Economical part of a question
Price for minimum required edition - 3 600 RUB Annual updates price - 1 800 RUB. Approximate expenses for 3 years - 7 200 RUB.
Price for minimum required edition - 6 747 RUB Annual updates price - 2 697 RUB. Approximate expenses for 3 years - 12 141 RUB.
Price for minimum required edition - 13 500 RUB Annual updates price - 6 297 RUB. Approximate expenses for 3 years - 26 094 RUB
FireDAC (FireDAC Client/Server Pack Delphi Professional)
Price for minimum required edition - 23 504 RUB Annual updates price - 13 993 RUB. Approximate expenses for 3 years - 51 490 RUB
The latest news on the FIBPlus website were published on 03.06.2013. They ask on forums whether the project is alive. The fate of the project is unknown. I have always been afraid of this when using third-party libraries. I already have this product licenses, I tried to migrate to these components, but encountered serious problems, that can't be resolved by the find and replace command.
FireDAC (FireDAC Client/Server Pack for Delphi Professional)
The project is managed by Embarcadero. I feel that probability to influence the product development (get required improvements) tends to zero. The library update period, most likely, will be the same as the IDE semi-annual. Buying this package will increase expenses if there is a need to upgrade to the next version of Delphi Professional. Won't it suffer the same fate as dbExpress? In our projects, we use Firebird in 100% cases the direct competitor for InterBase by Embarcadero. Isn't absence of a physical Firebird driver for FireDAC for mobile platforms the evidence of the competition? Such a driver for Android, for example, would greatly increase the advantages of this library and such a high price would be justified. FireDAC is distributed with source codes for this money, but it is not a great advantage it is more important that the authors themselves developed their product. And there is no standard edition without sources and nor is expected.
IBDAC and UniDAC.
The set of Universal Data Access Components (UniDAC) includes Firebird specific components. Devart promptly responds the requests to the user support, and I have a positive experience of working with their developers - they have already improved their product for us. This must decrease risks of migration to their components. Preliminary assessment shows that it is easier to migrate to these components than to FIBPlus, and using UniDAC will allow to connect to MSSQL, that will simplify the integration.
After a week of tests, meditation, reading blogs and forums (especially liked the duel of noble dons Devart vs. Da-Soft on SQL.ru), I decided to migrate my project to UniDAC.