We have been receiving many questions about how UniDAC compares to FireDAC. To answer your questions, we took a data-backed approach and created a series of tests that aim to examine the performance and memory consumption of the two products.
Test Environment
The testing was conducted on UniDAC 9.2.1 and FireDAC in RAD Studio 11.1 (the version that comes with RAD Studio) with default settings for both products.
A 1Gbps network was used to access the remote databases to minimize the impact of network bandwidth on test results. A computer with an SSD drive will minimize the impact of the drive’s read/write speed on tests for local databases such as MS Access and SQLite. Other characteristics of the computer on which the testing was carried out are less important since the vast majority of modern computers have multi-core processors and more than 4Gb of RAM.
Performance Test Cases
Fetch speed tests of 10,000 records in chunks of various sizes.
| Operation type | Description | Fetch with FetchRows=25 | Measures the read speed in small blocks of 25 records |
| Fetch with FetchRows=1000 | Measures the read speed in small blocks of 1000 records |
| Fetch All Records | Measures the read speed of all the records from the table at once |
Fetch speed tests for 1,000 records, one at a time.
| Operation type | Description |
| Fetch by 1 record without Params | Measures the performance of SELECT queries without parameters that return a single record |
| Fetch by 1 record with Params | Measures the performance of SELECT queries with parameters that return the same record but from different tables |
| Fetch by 1 record with Params (prepared) | Measures the speed of multiple reads of one record from the same table. For this, a pre-prepared SELECT query with parameters is used |
Update speed tests for 1,000 records.
| Operation type | Description |
| Insert/Post Edit/Post DML insert without Params DML update without Params |
Measure the rate of data modification in a classical way using the Insert/Post or Append/Post or Update/Post methods of TDataSet Measure the speed of execution of INSERT and UPDATE queries without parameters |
| DML insert with Params
DML update with Params |
Measure the speed of execution of INSERT and UPDATE queries with parameters for modifying data in different tables |
| DML insert with Params (prepared) DML update with Params (prepared) |
Measure the speed of execution of INSERT and UPDATE queries (with parameters) that were previously prepared |
Batch update speed tests for 10,000 records.
| Operation type | Description |
| Batch Insert with BatchSize=25 Batch Update with BatchSize=25 |
Measure the speed of inserting and changing data in blocks of 25 records |
| Batch Insert with BatchSize=1000 Batch Update with BatchSize=1000 |
Measure the speed of inserting and changing data in blocks of 1000 records |
Read/write speed tests for Blobs.
| Operation type | Description |
| Read Blob | Measures the Blob read speed |
| Write Blob | Measures the Blob write speed |
Performance test for calling stored procedures 1,000 times:
| Operation type | Description |
| StoredProc with params | Measures the execution speed of stored procedures |
Memory Consumption Test Cases
It’s important to know the amount of memory consumed when reading a large number of records from the database or when reading Blob fields. Therefore, we also measured memory consumption in the following test cases:
- Fetch with FetchRows=25
- Fetch with FetchRows=1000
- Fetch All Records
- Read Blob
- Write Blob
The structure of the test database
The test database had two tables–READ_PERF and WRITE_PERF, with the following fields.
| Argument | Description |
| ID | number field |
| F_DATETIME | date and time |
| F_STR100 | string field 100 characters long |
| F_STR800 | string field 800 characters long |
| F_STR1600 | string field 1600 characters long |
| F_TEXT | a large string field where you can write several megabytes of text |
The choice of data types was dictated by the requirement to have them supported in all databases under test.
We added several string fields with different lengths in case one of the products would demonstrate higher performance for only long or short text fields.
Separate tables were used for reading and writing operations so we didn’t have to populate the table with test data for reading operations after performing write tests on the table.
Databases under test
We used major databases in our tests:
Disclaimer
You should not view the results of this testing as performance indicators of the database servers themselves (for example, Oracle vs SQL Server) because databases were deployed on different physical or virtual machines, with a different number of CPU cores and amount of RAM, and so on.
MS Access
The MS Access database files were located on an SSD driver. We used Microsoft’s native ODBC driver (Microsoft Access Driver) to connect to the database. This test benchmarked the performance of accessing data in an MS Access database and how well the two products could work with ODBC drivers.
Performance test results:
| Test name | FireDAC, sec |
UniDAC, sec |
UniDAC is faster than FireDAC, % |
| Fetch with FetchRows=25 | 0,062 | 0,038 | 63,16 |
| Fetch with FetchRows=1000 | 0,061 | 0,037 | 64,86 |
| Fetch All Records | 0,056 | 0,037 | 51,35 |
| Fetch by 1 record without Params | 0,516 | 0,384 | 34,38 |
| Fetch by 1 record with Params | 0,543 | 0,414 | 31,16 |
| Fetch by 1 record with Params (prepared) | 0,126 | 0,046 | 173,91 |
| Insert/Post | 0,477 | 0,454 | 5,07 |
| Edit/Post | 0,515 | 0,501 | 2,79 |
| DML insert without Params | 0,436 | 0,373 | 16,89 |
| DML insert with Params | 0,476 | 0,457 | 4,16 |
| DML insert with Params (prepared) | 0,205 | 0,201 | 1,99 |
| DML update without Params | 0,484 | 0,422 | 14,69 |
| DML update with Params | 0,532 | 0,511 | 4,11 |
| DML update with Params (prepared) | 0,263 | 0,263 | 0,00 |
| Batch Insert with BatchSize=25 | 1,962 | 1,914 | 2,51 |
| Batch Insert with BatchSize=1000 | 1,925 | 1,910 | 0,79 |
| Batch Update with BatchSize=25 | 2,564 | 2,542 | 0,87 |
| Batch Update with BatchSize=1000 | 2,518 | 2,500 | 0,72 |
| Read Blob | 0,074 | 0,025 | 196,00 |
| Write Blob | 0,605 | 0,571 | 5,95 |
Memory consumption test results:
| Test name | FireDAC, MB |
UniDAC, MB |
UniDAC is better than FireDAC, % |
| Fetch with FetchRows=25 | 11,227 | 8,512 | 31,90 |
| Fetch with FetchRows=1000 | 12,281 | 8,664 | 41,75 |
| Fetch All Records | 11,230 | 7,465 | 50,44 |
| Read Blob | 36,738 | 30,668 | 19,79 |
| Write Blob | 31,301 | 31,855 | -1,74 |
Diagrams:
UniDAC fetched data 1.5 times faster than FireDAC, whereas in Blob read tests, UniDAC was 3 times faster than FireDAC. The tests also revealed that UniDAC consumed much less memory.
For inserting and updating data, the difference between the products was less evident, but still, UniDAC was slightly faster.
The only test where FireDAC beat UniDAC and consumed 1.74% less memory was when writing Blobs, though FireDAC was still slower than UniDAC.
Adaptive Server Enterprise
This test compared how the products worked with SAP Adaptive Server Enterprise 16 using the native ODBC driver and the Direct mode. The Direct mode is only available in UniDAC – we used it in this test case to assess its efficiency.
We connected to the remote server through a 1 GB/s network.
Performance test results:
| Test name | FireDAC, sec |
UniDAC via ODBC, sec | UniDAC is faster than FireDAC via ODBC, % | UniDAC in Direct, sec | UniDAC is faster than FireDAC in Direct, % |
| Fetch with FetchRows=25 | 0,777 | 0,704 | 10,37 | 0,646 | 20,28 |
| Fetch with FetchRows=1000 | 0,664 | 0,536 | 23,88 | 0,501 | 32,53 |
| Fetch All Records | 0,643 | 0,599 | 7,35 | 0,578 | 11,25 |
| Fetch by 1 record without Params | 2,236 | 1,768 | 26,47 | 1,616 | 38,37 |
| Fetch by 1 record with Params | 2,458 | 1,704 | 44,25 | 1,618 | 51,92 |
| Fetch by 1 record with Params (prepared) | 1,760 | 1,606 | 9,59 | 0,779 | 125,93 |
| Insert/Post | 2,404 | 2,223 | 8,14 | 2,099 | 14,53 |
| Edit/Post | 3,581 | 3,234 | 10,73 | 3,057 | 17,14 |
| DML insert without Params | 2,367 | 2,085 | 13,53 | 1,938 | 22,14 |
| DML insert with Params | 2,289 | 2,004 | 14,22 | 1,893 | 20,92 |
| DML insert with Params (prepared) | 2,124 | 1,849 | 14,87 | 1,406 | 51,07 |
| DML update without Params | 3,849 | 3,542 | 8,67 | 3,455 | 11,40 |
| DML update with Params | 3,360 | 3,275 | 2,60 | 3,114 | 7,90 |
| DML update with Params (prepared) | 3,107 | 2,994 | 3,77 | 1,588 | 95,65 |
| Batch Insert with BatchSize=25 | 18,731 | 18,327 | 2,20 | 5,172 | 262,16 |
| Batch Insert with BatchSize=1000 | 20,086 | 19,644 | 2,25 | 3,191 | 529,46 |
| Batch Update with BatchSize=25 | 37,222 | 32,629 | 14,08 | 7,728 | 381,65 |
| Batch Update with BatchSize=1000 | 33,472 | 30,635 | 9,26 | 7,689 | 335,32 |
| Read Blob | 2,031 | 1,104 | 83,97 | 1,022 | 98,73 |
| Write Blob | 5,256 | 4,729 | 11,14 | 2,023 | 159,81 |
| StoredProc with params | 1,098 | 0,818 |
Memory consumption test results:
| Test name | FireDAC, MB | UniDAC via ODBC, MB | UniDAC is better than FireDAC via ODBC, % | UniDAC in Direct, MB | UniDAC is better than FireDAC in Direct , % |
| Fetch with FetchRows=25 | 23,621 | 20,352 | 16,06 | 11,230 | 110,34 |
| Fetch with FetchRows=1000 | 24,293 | 20,340 | 19,43 | 11,262 | 115,71 |
| Fetch All Records | 23,848 | 20,418 | 16,80 | 11,180 | 113,31 |
| Read Blob | 34,652 | 31,012 | 11,74 | 23,199 | 49,37 |
| Write Blob | 31,555 | 31,824 | -0,85 | 23,254 | 35,70 |
Diagrams:
UniDAC demonstrated exceptional performance in fetch tests both in the Direct mode (where it surpassed by far the speed of FireDAC) and when connecting through an ODBC driver. In memory consumption tests, UniDAC consumed half as much memory as FireDAC in the Direct mode and slightly less through an ODBC connection.
UniDAC also showed better performance for update statements in both modes. In particular, UniDAC inserted and updated data in batch operations in the Direct mode six times faster than FireDAC through an ODBC connection.
The read and write tests for Blob objects also proved UniDAC superiority over FireDAC. The only time FireDAC exceeded UniDAC was during a memory consumption test for writing Blobs through an ODBC connection (by 0.85%), while in the Direct mode, UniDAC still beat FireDAC.
We couldn’t perform tests on stored procedures because, for some reason, FireDAC threw an error during this test whenever we connected through the ODBC driver.
The test results show that UniDAC can work with different ODBC drivers more efficiently than FireDAC. In the Direct mode, UniDAC achieves an even higher level of performance, unattainable through an ODBC connection, while also consuming less memory.
Firebird
The testing was conducted on Firebird 3 because FireDAC didn’t officially support FireDAC 4 at that time.
We ran tests on a local server to reduce the impact of the network environment on test results. The database was located on an SSD driver.
Performance test results:
| Test name | FireDAC, sec |
UniDAC, sec |
UniDAC is faster than FireDAC, % |
| Fetch with FetchRows=25 | 0,174 | 0,157 | 10,83 |
| Fetch with FetchRows=1000 | 0,167 | 0,158 | 5,70 |
| Fetch All Records | 0,165 | 0,158 | 4,43 |
| Fetch by 1 record without Params | 0,602 | 0,869 | -30,72 |
| Fetch by 1 record with Params | 0,620 | 0,897 | -30,88 |
| Fetch by 1 record with Params (prepared) | 0,623 | 0,171 | 264,33 |
| Insert/Post | 0,856 | 0,713 | 20,06 |
| Edit/Post | 1,073 | 0,762 | 40,81 |
| DML insert without Params | 0,755 | 0,711 | 6,19 |
| DML insert with Params | 0,744 | 0,743 | 0,13 |
| DML insert with Params (prepared) | 0,728 | 0,516 | 41,09 |
| DML update without Params | 0,993 | 0,880 | 12,84 |
| DML update with Params | 0,941 | 0,900 | 4,56 |
| DML update with Params (prepared) | 0,946 | 0,538 | 75,84 |
| Batch Insert with BatchSize=25 | 1,416 | 0,926 | 52,92 |
| Batch Insert with BatchSize=1000 | 0,689 | 0,530 | 30,00 |
| Batch Update with BatchSize=25 | 1,619 | 0,957 | 69,17 |
| Batch Update with BatchSize=1000 | 0,759 | 0,576 | 31,77 |
| Read Blob | 0,279 | 0,273 | 2,20 |
| Write Blob | 0,491 | 0,452 | 8,63 |
| StoredProc with params | 0,565 | 0,147 | 284,35 |
Memory consumption test results:
| Test name | FireDAC, MB |
UniDAC, MB |
UniDAC is better than FireDAC, % |
| Fetch with FetchRows=25 | 15,035 | 9,719 | 54,70 |
| Fetch with FetchRows=1000 | 14,934 | 9,762 | 52,98 |
| Fetch All Records | 14,840 | 9,895 | 49,97 |
| Read Blob | 21,211 | 18,379 | 15,41 |
| Write Blob | 21,191 | 21,191 | 0,00 |
Diagrams:
UniDAC fetched small recordsets faster and consumed 1.5 times less memory than UniDAC.
FireDAC was faster than UniDAC by 30% in two tests involving multiple unprepared SELECT queries to fetch one record. However, UniDAC was 3.5 times faster than FireDAC in tests with prepared statements.
When updating data with the Append/Insert/Edit/Post methods or SQL statements, UniDAC was faster than FireDAC, especially in tests with prepared statements.
In batch operations, UniDAC was 1.5 times faster than FireDAC and was slightly faster in tests with Blobs.
In tests with stored procedures, UniDAC was almost 4 times faster than UniDAC.
The only test case where FireDAC outperformed UniDAC was fetching by one record with unprepared statements. In all other tests, UniDAC showed significantly better results than FireDAC.
MySQL
The testing was performed on a remote MySQL 5.7 server in a 1 GB/s network. We used the Direct mode in UniDAC and the native client library in FireDAC to access MySQL.
Performance test results:
| Test name | FireDAC, sec |
UniDAC, sec |
UniDAC faster than FireDAC, % |
| Fetch with FetchRows=25 | 0,232 | 0,168 | 38,10 |
| Fetch with FetchRows=1000 | 0,238 | 0,155 | 53,55 |
| Fetch All Records | 0,215 | 0,118 | 82,20 |
| Fetch by 1 record without Params | 1,488 | 0,726 | 104,96 |
| Fetch by 1 record with Params | 1,727 | 0,804 | 114,80 |
| Fetch by 1 record with Params (prepared) | 1,402 | 0,674 | 108,01 |
| Insert/Post | 2,466 | 0,825 | 198,91 |
| Edit/Post | 1,230 | 1,082 | 13,68 |
| DML insert without Params | 1,488 | 0,757 | 96,57 |
| DML insert with Params | 1,954 | 0,833 | 134,57 |
| DML insert with Params (prepared) | 1,217 | 0,655 | 85,80 |
| DML update without Params | 1,684 | 0,850 | 98,12 |
| DML update with Params | 2,283 | 0,941 | 142,61 |
| DML update with Params (prepared) | 1,548 | 1,119 | 38,34 |
| Batch Insert with BatchSize=25 | 1,258 | 1,083 | 16,16 |
| Batch Insert with BatchSize=1000 | 0,604 | 0,482 | 25,31 |
| Batch Update with BatchSize=25 | 17,195 | 5,318 | 223,34 |
| Batch Update with BatchSize=1000 | 16,199 | 5,034 | 221,79 |
| Read Blob | 1,756 | 0,198 | 786,87 |
| Write Blob | 6,952 | 1,185 | 486,67 |
| StoredProc with params | 0,992 |
Memory consumption test results:
| Test name | FireDAC, MB |
UniDAC, MB |
UniDAC is better than FireDAC, % |
| Fetch with FetchRows=25 | 26,121 | 9,719 | 168,76 |
| Fetch with FetchRows=1000 | 26,133 | 9,648 | 170,86 |
| Fetch All Records | 26,379 | 9,730 | 171,11 |
| Read Blob | 44,512 | 24,211 | 83,85 |
| Write Blob | 23,500 | 21,258 | 10,55 |
Diagrams:
In read tests, UniDAC was almost 2 times faster and consumed 2.5 times less memory than FireDAC.
In update tests, UniDAC was 2.5 times faster, and in batch update operations, 3 times faster than FireDAC.
UniDAC read Blobs 9 times faster and consumed 2 times less memory. In Blob write tests, UniDAC was 6 times faster than FireDAC.
We couldn’t run tests with stored procedures because we were getting an error in FireDAC.
Oracle
The testing was conducted on a remote Oracle 18c server in a 1 GB/s network. We connected to Oracle both in the Direct mode and through Oracle Client in UniDAC, and only through Oracle Client in FireDAC because FireDAC can only work through a client library.
Performance test results:
| Test name | FireDAC, sec |
UniDAC in OCI, sec | UniDAC faster than FireDAC in OCI, % | UniDAC in Direct, sec | UniDAC faster than FireDAC in Direct, % |
| Fetch with FetchRows=25 | 0,327 | 0,223 | 46,64 | 0,222 | 47,30 |
| Fetch with FetchRows=1000 | 0,536 | 0,127 | 322,05 | 0,125 | 328,80 |
| Fetch All Records | 0,299 | 0,129 | 131,78 | 0,130 | 130,00 |
| Fetch by 1 record without Params | 1,262 | 0,889 | 41,96 | 1,230 | 2,60 |
| Fetch by 1 record with Params | 1,325 | 0,881 | 50,40 | 1,242 | 6,68 |
| Fetch by 1 record with Params (prepared) | 0,919 | 0,669 | 37,37 | 0,678 | 35,55 |
| Insert/Post | 4,450 | 4,088 | 8,86 | 4,043 | 10,07 |
| Edit/Post | 4,401 | 3,831 | 14,88 | 3,714 | 18,50 |
| DML insert without Params | 4,672 | 4,276 | 9,26 | 4,181 | 11,74 |
| DML insert with Params | 3,733 | 3,614 | 3,29 | 3,519 | 6,08 |
| DML insert with Params (prepared) | 3,714 | 3,528 | 5,27 | 3,346 | 11,00 |
| DML update without Params | 5,940 | 4,961 | 19,73 | 4,893 | 21,40 |
| DML update with Params | 3,626 | 3,581 | 1,26 | 3,579 | 1,31 |
| DML update with Params (prepared) | 3,452 | 3,423 | 0,85 | 3,427 | 0,73 |
| Batch Insert with BatchSize=25 | 1,959 | 1,785 | 9,75 | 1,795 | 9,14 |
| Batch Insert with BatchSize=1000 | 0,473 | 0,375 | 26,13 | 0,363 | 30,30 |
| Batch Update with BatchSize=25 | 3,018 | 2,399 | 25,80 | 2,374 | 27,13 |
| Batch Update with BatchSize=1000 | 1,016 | 0,799 | 27,16 | 0,806 | 26,05 |
| Read Blob | 0,502 | 0,425 | 18,12 | 0,401 | 5,19 |
| Write Blob | 0,994 | 0,867 | 14,65 | 0,831 | 19,61 |
| StoredProc with params | 0,736 | 0,686 | 7,29 | 0,690 | 6,67 |
Memory consumption test results:
| Test name | FireDAC, MB | UniDAC in OCI, MB | UniDAC is better than FireDAC in OCI, % | UniDAC in Direct, MB | UniDAC is better than FireDAC in Direct, % |
| Fetch with FetchRows=25 | 16,164 | 10,086 | 60,26 | 11,160 | 44,84 |
| Fetch with FetchRows=1000 | 19,371 | 10,070 | 92,36 | 10,355 | 87,07 |
| Fetch All Records | 16,340 | 10,113 | 61,57 | 10,457 | 56,26 |
| Read Blob | 23,293 | 21,191 | 9,92 | 21,191 | 9,92 |
| Write Blob | 25,785 | 21,336 | 20,85 | 21,254 | 21,32 |
Diagrams:
When reading large volumes of data, UniDAC was 4 times faster and consumed 1.5 times less memory than FireDAC.
UniDAC also outperformed its competitor in update tests when using the Append/Insert/Edit/Post methods and INSERT/UPDATE statements. We saw similar results in batch operations where UniDAC exceeded FireDAC.
In operations with Blobs, UniDAC was better both in terms of performance and memory consumption.
In tests with stored procedures, the difference was not so evident, but still, UniDAC was slightly better.
It’s safe to say that you won’t have to make a trade-off between performance/memory consumption and ease of deployment if you choose the Direct mode which doesn’t require any external client libraries.
PostgreSQL
The testing was performed on a remote PostgreSQL14 server in a 1 GB/s network. We used UniDAC in the Direct mode and FireDAC with the native client library.
Performance test results:
| Test name | FireDAC, sec |
UniDAC, sec |
UniDAC faster than FireDAC, % |
| Fetch with FetchRows=25 | 0,560 | 0,394 | 42,13 |
| Fetch with FetchRows=1000 | 0,259 | 0,239 | 8,37 |
| Fetch All Records | 0,182 | 0,134 | 35,82 |
| Fetch by 1 record without Params | 4,505 | 4,128 | 9,13 |
| Fetch by 1 record with Params | 4,813 | 4,342 | 10,85 |
| Fetch by 1 record with Params (prepared) | 2,450 | 1,261 | 94,29 |
| Insert/Post | 1,568 | 1,465 | 7,03 |
| Edit/Post | 1,732 | 1,688 | 2,61 |
| DML insert without Params | 2,971 | 1,834 | 62,00 |
| DML insert with Params | 3,217 | 1,727 | 86,28 |
| DML insert with Params (prepared) | 1,573 | 1,432 | 9,85 |
| DML update without Params | 2,990 | 1,752 | 70,66 |
| DML update with Params | 3,617 | 1,552 | 133,05 |
| DML update with Params (prepared) | 1,580 | 1,521 | 3,88 |
| Batch Insert with BatchSize=25 | 2,252 | 1,753 | 28,47 |
| Batch Insert with BatchSize=1000 | 1,119 | 1,004 | 11,45 |
| Batch Update with BatchSize=25 | 19,242 | 3,310 | 481,33 |
| Batch Update with BatchSize=1000 | 19,283 | 1,889 | 920,80 |
| Read Blob | 0,638 | 0,248 | 157,26 |
| Write Blob | 1,094 | 1,026 | 6,63 |
| StoredProc with params | 1,973 | 1,358 | 45,29 |
Memory consumption test results:
| Test name | FireDAC, MB |
UniDAC, MB |
UniDAC is better than FireDAC, % |
| Fetch with FetchRows=25 | 32,559 | 16,113 | 102,07 |
| Fetch with FetchRows=1000 | 32,266 | 13,227 | 143,94 |
| Fetch All Records | 32,809 | 10,402 | 215,41 |
| Read Blob | 35,324 | 21,191 | 66,69 |
| Write Blob | 34,387 | 23,465 | 46,55 |
Diagrams:
In fetch tests, UniDAC showed significantly higher performance and consumed 3 times less memory than UniDAC.
When updating data with the Append/Insert/Edit/Post methods, UniDAC was slightly better than FireDAC, but the real advantage could be seen in update tests with SQL statements where UniDAC was 2 times faster than its competitor.
In batch insert operations, UniDAC was still ahead of FireDAC and showed its real power in batch update tests with 10 times better performance than FireDAC.
UniDAC read Blobs 2.5 times faster and consumed 1.5 times less memory than FireDAC, and also showed better results than the competitor when writing blobs.
In operations with stored procedures, UniDAC was 1.5 times faster than UniDAC.
SQL Server
The testing was performed on a remote MS SQL Server 2019 in a 1 GB/s network.
This test is a bit different from previous tests because FireDAC can work with SQL Server only through an ODBC driver while UniDAC supports ODBC, OLEDB, and the Direct mode.
Performance test results:
| Test name | FireDAC, sec | UniDAC via Native Client, sec | UniDAC faster than FireDAC via Native Client, % | UniDAC in Direct, sec | UniDAC faster than FireDAC in Direct, % |
| Fetch with FetchRows=25 | 0,607 | 0,113 | 437,17 | 0,111 | 446,85 |
| Fetch with FetchRows=1000 | 0,605 | 0,124 | 387,90 | 0,107 | 465,42 |
| Fetch All Records | 0,560 | 0,111 | 404,50 | 0,111 | 404,50 |
| Fetch by 1 record without Params | 1,493 | 0,848 | 76,06 | 0,698 | 113,90 |
| Fetch by 1 record with Params | 2,062 | 0,817 | 152,39 | 0,755 | 173,11 |
| Fetch by 1 record with Params (prepared) | 0,754 | 0,661 | 14,07 | 0,649 | 16,18 |
| Insert/Post | 1,171 | 1,040 | 12,60 | 1,028 | 13,91 |
| Edit/Post | 1,152 | 1,077 | 6,96 | 1,039 | 10,88 |
| DML insert without Params | 2,308 | 1,456 | 58,52 | 1,294 | 78,36 |
| DML insert with Params | 3,459 | 2,371 | 45,89 | 2,233 | 54,90 |
| DML insert with Params (prepared) | 1,313 | 1,302 | 0,84 | 1,130 | 16,19 |
| DML update without Params | 1,743 | 0,961 | 81,37 | 0,961 | 81,37 |
| DML update with Params | 2,225 | 1,028 | 116,44 | 1,026 | 116,86 |
| DML update with Params (prepared) | 0,914 | 0,831 | 9,99 | 0,844 | 8,29 |
| Batch Insert with BatchSize=25 | 3,740 | 1,219 | 206,81 | 1,281 | 191,96 |
| Batch Insert with BatchSize=1000 | 3,588 | 1,059 | 238,81 | 1,156 | 210,38 |
| Batch Update with BatchSize=25 | 3,862 | 3,491 | 10,63 | 3,378 | 14,33 |
| Batch Update with BatchSize=1000 | 3,957 | 1,895 | 108,81 | 1,897 | 108,59 |
| Read Blob | 1,229 | 0,254 | 383,86 | 0,200 | 514,50 |
| Write Blob | 1,637 | 0,738 | 121,82 | 0,782 | 109,34 |
| StoredProc with params | 0,892 | 0,824 | 8,25 | 0,791 | 12,77 |
Memory consumption test results:
| Test name | FireDAC, MB | UniDAC via Native Client, MB | UniDAC is better than FireDAC via Native Client, % | UniDAC in Direct, MB | UniDAC is better than FireDAC in Direct , % |
| Fetch with FetchRows=25 | 14,184 | 9,457 | 49,98 | 11,047 | 28,40 |
| Fetch with FetchRows=1000 | 14,168 | 8,477 | 67,13 | 11,309 | 25,28 |
| Fetch All Records | 15,168 | 9,941 | 52,58 | 12,500 | 21,34 |
| Read Blob | 23,340 | 17,660 | 32,16 | 23,191 | 0,64 |
| Write Blob | 21,313 | 21,320 | -0,03 | 23,262 | -8,38 |
Diagrams:
In read tests, UniDAC substantially outperformed FireDAC–both in the Direct mode and through an OLEDB driver, UniDAC was 5 times faster and consumed less memory.
UniDAC updated data slightly faster than FireDAC with the Append/Insert/Edit/Post methods and almost 2 times faster with SQL statements in some test cases.
In batch update operations, UniDAC was 3 times faster than FireDAC.
When reading Blobs, UniDAC was up to 5 times faster than UniDAC. The only time when FireDAC outperformed UniDAC was in a memory consumption test for writing Blobs–FireDAC consumed several percent less memory.
The compared products were almost equal in stored procedures tests, though UniDAC was a little faster.
UniDAC exceeded FireDAC in practically all tests for SQL Server. The use of the Direct mode probably won’t give you a substantial performance gain on Windows compared to the preinstalled Microsoft OLE DB Driver for SQL Server on this system. The developers who create software for non-Windows systems would benefit from the Direct mode as it ensures performance and memory consumption similar to those of the native OLE DB Driver.
SQLite
The testing was performed on an SQLite database located on an SSD driver. UniDAC and FireDAC can work with SQLite both through an external client library and a statically linked client library (it’s called the Direct mode in UniDAC). We tested both methods.
Performance test results:
| Test name | FireDAC via native DLL, sec | FireDAC Static Link, sec | UniDAC via native DLL, sec | UniDAC in Direct, sec | UniDAC faster than FireDAC, % |
| Fetch with FetchRows=25 | 0,066 | 0,066 | 0,051 | 0,053 | 26,92 |
| Fetch with FetchRows=1000 | 0,064 | 0,063 | 0,051 | 0,052 | 23,30 |
| Fetch All Records | 0,062 | 0,061 | 0,050 | 0,053 | 19,42 |
| Fetch by 1 record without Params | 0,154 | 0,154 | 0,090 | 0,092 | 69,23 |
| Fetch by 1 record with Params | 0,160 | 0,159 | 0,094 | 0,097 | 67,02 |
| Fetch by 1 record with Params (prepared) | 0,080 | 0,080 | 0,022 | 0,022 | 263,64 |
| Insert/Post | 0,147 | 0,147 | 0,089 | 0,090 | 64,25 |
| Edit/Post | 0,179 | 0,179 | 0,115 | 0,117 | 54,31 |
| DML insert without Params | 0,132 | 0,129 | 0,071 | 0,074 | 80,00 |
| DML insert with Params | 0,101 | 0,101 | 0,100 | 0,101 | 0,50 |
| DML insert with Params (prepared) | 0,038 | 0,038 | 0,038 | 0,038 | 0,00 |
| DML update without Params | 0,154 | 0,155 | 0,094 | 0,095 | 63,49 |
| DML update with Params | 0,132 | 0,129 | 0,125 | 0,125 | 4,40 |
| DML update with Params (prepared) | 0,060 | 0,060 | 0,060 | 0,060 | 0,00 |
| Batch Insert with BatchSize=25 | 0,373 | 0,364 | 0,121 | 0,125 | 199,59 |
| Batch Insert with BatchSize=1000 | 0,361 | 0,368 | 0,081 | 0,085 | 339,16 |
| Batch Update with BatchSize=25 | 0,653 | 0,651 | 0,169 | 0,169 | 285,80 |
| Batch Update with BatchSize=1000 | 0,651 | 0,650 | 0,113 | 0,113 | 475,66 |
| Read Blob | 0,052 | 0,050 | 0,039 | 0,039 | 30,77 |
| Write Blob | 0,118 | 0,119 | 0,089 | 0,092 | 30,94 |
Memory consumption test results:
| Test name | FireDAC via native DLL, MB | FireDAC Static Link, MB | UniDAC via native DLL, MB | UniDAC in Direct, MB | UniDAC is better than FireDAC, % |
| Fetch with FetchRows=25 | 27,168 | 27,762 | 10,449 | 10,840 | 150,63 |
| Fetch with FetchRows=1000 | 27,102 | 27,313 | 9,781 | 10,855 | 149,67 |
| Fetch All Records | 27,445 | 27,320 | 9,898 | 10,844 | 153,09 |
| Read Blob | 51,520 | 51,508 | 22,426 | 21,191 | 143,12 |
| Write Blob | 31,254 | 31,250 | 21,250 | 21,250 | 47,08 |
Diagrams:
When reading a large number of rows, UniDAC was still faster than FireDAC, and 3.5 times faster when reading records one by one.
In update tests, UniDAC was up to 1.5 times faster than FireDAC both when using the Append/Insert/Edit/Post methods and SQL statements.
UniDAC outperformed FireDAC by a wide margin in batch operations–in some tests, UniDAC was 5 times faster.
When reading and writing Blobs, UniDAC still exceeded FireDAC both in performance and memory optimization.
The connection method didn’t have a significant impact on the performance of the products–we got almost the same results with external and statically linked client libraries.
Conclusion
The results clearly show that UniDAC delivers a significant performance boost compared to standard data access components in RAD Studio–in some test cases, UniDAC was 10 times faster than FireDAC.
UniDAC also consumes significantly less memory than FireDAC, which is very important for applications that work with large amounts of data.
The results of this test suite prove that UniDAC is a good choice for developers who create data-intensive applications in Delphi or C++ Builder.
The archive with the source code of the application that we created for this test suite:
To eliminate the effect of the IDE and built-in debugger on test results, enable the Release configuration before compiling the project.
