This research was investigated by me and my students as a practical task to learn the existing embedded database systems available for Delphi programmers. The results seemed interesting to me and I would like to share them with all the Delphi community.
My goal was to test all existing embedded databases available for Delphi to find the best in SQL support for my students. Certainly, most of us use world wide known client-server database like Oracle, MS SQL Server, MySQL, PostgreSQL, Interbase/Firebird and so on. But sometimes we need so called embedded database: when we can simply copy application executable and database files to flash drive or burn it to CD/DVD disk and use it in on any machine without installation of any client drivers and on machines without access to Internet. Most of modern embedded databases supports client-server technology, at least in local networks.
The next important feature is a good SQL support. We would be able to study all kinds of modern software using same engine: from computer games to science programs. As we already have good knowledge of world known SQL engines, we need standard SQL statements to work without modifications. We do not plan to study some kind of internal languages or SQL dialects without pressure. So we included MS SQL Server 2008 to this test just to refer to it as etalon engine for standard SQL statements / functions. Its result is out of competition as it is not an embedded database. The Firebird is included in the competition as it has embedded version (Here you can download Firebird Embedded).
The quality of the SQL engine, presented on the Figure 1 which show total results from the Table 1, is treated as the sum of SQL features comparison score (presented on the Figure 2 and in the Table 2) and SQL statements checking score (presented on the Figure 3, in the Table 3 and Table 4). To avoid problems with viewing in different browsers and screen resolutions, any table can be opened in a new window.
The SQL statements testing principles are: any SQL statement that cannot be executed by MS SQL Server or cannot be executed by at least 2 independent engines automatically deleted from the test. Dependent engines are the following: DBISAM and ElevateDB (same vendor). All SQL functions that described in documentation of at least 2 independent database systems were added to the test. If same function has different parameter sets in some engines all possible combinations are tested. If some engine has some kind of bug I always try to find a workaround in all further tests, so only first occurrence will loose points.
All SQL statements were checked for correct results. If the statement executed correctly, the database gets 1 point. If the database system returns incorrect result or raises exception about unsupported statement it gains 0 points. If access violation or range check error occurs, the database gets a penalty: -1 points. If the application (SQL console) is crashed and must be terminated, the database system gets a penalty: -2 points. If it hangs and must be terminated by the operator, the database gets a penalty: -3 points.
The SQL features comparison has the following scale:
We have tested the following database systems (all newest evaluation versions available at 15th January 2011):
All tests were performed under the Windows 7 x64 Professional (English). Date format is MM/DD/YYYY.
I have created a simple model of the relational database for automobile sales centre.
There are 3 simple tables linked by 32-bit integer identifiers:
1) dept table: departments of the sales center - 2 records, ID - unique identifier
2) emp table: employees working in departments - 6 records, ID -
unique identifier, DeptID - identifier of the department from dept
table
3) orders table: cars sold by employees - 8 records, ID -
unique identifier, EmpID - identifier of the employee who sold this
car.
My main goal is to check the SQL functionality and reliability, so I have created very small tables for easy checking of the result data - most of queries return data that can be viewed without scrolling and resizing.
I have created a simple demo database using SQL scripts
for all tested database engines, except Apollo 7
(its current version cannot execute CREATE TABLE statements correctly,
so I have created tables using dbfUtilsLite tool).
You can see scripts here:
Accuracer
Advantage
Apollo
DBISAM
ElevateDB
Firebird
MS SQL Server
NexusDB
TurboDB
All SQL statements testing results are presented in the Table 4 you can find below.
Any problem and error we faced has my short remark. Remarks have usual black font,
errors are marked as shown:
Non-critical error
Critical error: wrong result, standard function is not supported, etc.
Extra critical error: Access violation, unhandled exception, app closes or hangs, etc.!
Very solid SQL engine (19 bugs - standard statements that are not supported or returns wrong results, 35 unsupported features), leader in 4 categories: Math Operators, Date and Time Functions, Subqueries, Supported Data Types. Runner-up (2-4 place, if it got more then 75% of leader's score) in 5 categories: Basic Queries (2nd place), Math Functions (2nd place), SELECT Statements With Joins (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (2nd place), DDL Statements (2nd place). However, there is nothing perfect in the world. The main imperfection is lack of internal SQL functions (aggregate functions 6/16 failed, string functions 19/39 failed). It has even no TRIM function, so we must write LTRIM(RTRIM(x)) to remove all leading and trailing spaces. DDL statements support also could be much better (10/43 failed). However, in most important areas it is on top: Joins - 35/40, Unions - 12/13, Subqueries - 22/22, Date and time functions - 36/37. It has all possible SQL features (58 points of 58).
The best SQL engine among all tested (18 bugs, 18 unsupported features), leader in 7 categories: Basic Queries, Aggregate Functions, Math Operators, String Functions, Logical Functions, Supported Data Types, DDL Statements. Runner-up in 4 categories: Date and Time Functions (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (2nd place), SELECT Statements With Joins (4th place), Subqueries (4th place). It has very good set of useful internal functions, can work with date and time values in any format and supports expressions aliases not only in ORDER BY, but also in WHERE, HAVING and even in SELECT columns (it allows to make SQL scripts with lots of expressions much shorter and easier to understand). However it has very limited mathematical functions support (no trigonometry functions), some minor bugs and 1 access violation (I hope, it will be solved in the following version). In any case it is undisputable leader in SQL statements test and the best of embedded databases in SQL functionality comparison, though does not have full text indexing support. Very easy to deploy - compiles into executable, stores database in single file, and even can merge database and executable in single read-only database file.
Good SQL engine (28 bugs, 43 unsupported features), leader in 2 categories: Math Operators , Logical Functions. Runner-up in 5 categories: Basic Queries (3rd place), String Functions (4th place), Date and Time Functions (3rd place), SELECT Statements With Joins (3rd place), Subqueries (3rd place). It has a good set of SQL functions and supported statements. However it does not support operators INTERSECT, EXCEPT and CORRESPONDING BY (6/13 failed in UNION, EXCEPT, INTERSECT) and has very limited data types support (10/27 failed). It does not support || concatenation operator (described in all existing SQL standards, including SQL 2008: 5.2). The engine is rather solid - it got no penalties. However it has some minor bugs, like ' Trim Test' instead of ' Trim Test ' and in DATE_DIFF evaluation with QUARTER and WEEK parameters. As for functionality, it has almost everything, except foreign keys - rather serious miss. The transactions are supported only in client-server mode. As a result - 3rd place in functionality, 2nd place in absolute and in SQL statements. Supports well known DBF format, requires to deploy library and configuration files.
Good SQL engine (29 bugs, 55 unsupported features), leader in 3 categories: Basic Queries, Math Operators, Supported Data Types. Runner-up in 4 categories: Aggregate Functions (3rd place), Logical Functions (3rd place), SELECT Statements With Joins (2nd place), Subqueries (2nd place). It has an adequate set of SQL functions and supported statements. However it has poor results in String Functions (16/39 failed), Date and Time Functions (27/37 failed), Logical Functions (7/22 failed), DDL Statements (11/43 failed) and has problems with ORDER BY in UNION statements (6/13 failed). NexusDB as well as Firebird has major problems with floating point values, it displays and calculates them incorrectly (1000.20001220703 instead of 1000.2). It cannot find record by condition Total = 1400.35. Nevertheless it has very good feature set that lacks only reverse engineering (export data to SQL) - 1st place in features comparison and 3rd place in absolute. Easy to deploy - can be compiled into executable, creates one file for each table.
Average SQL engine (31 bugs, 51 unsupported features), leader in 2 categories: SELECT Statements With Joins, Subqueries. Runner-up in 4 categories: Basic Queries (3rd place), Math Operators (2nd place), Math Functions (3rd place), Supported Data Types (2nd place). It has an adequate set of SQL functions and supported statements. But it has poor results in SELECT Statements With UNION, EXCEPT, INTERSECT (8/13 failed), Logical Functions (10/22 failed), DDL Statements (22/43 failed). It is the only engine that hangs in endless loop on INSERT INTO t_insert SELECT * FROM t_insert. It is very serious problem - if operator will not kill the application, it will use all available disk space. The main disadvantages are poor operation with floating point and date values: lots of bugs in simple expressions like 1.0/d, CAST(Birthday AS CHAR(10)), EXTRACT(WEEK FROM Birthday). The minor problems are unsupported auto-increment fields (it requires to create generator and trigger for each auto-inc field), unsupported || concatenation operator, converting all field names to upper case, global namespace for database objects (so it is impossible to have two indexes with same names even in different tables). The list of supported internal functions can be much longer for such well known database. The functionality is very good - it lacks full text indexing and normal support of SQL scripts (CREATE TABLE :; INSERT INTO..;) and auto-increment fields, - 2nd place in functionality comparison. It requires library files to deploy and stores all data in single file.
Average SQL engine (36 bugs, 52 unsupported features). Runner-up in 7 categories: Basic Queries (3rd place), Aggregate Functions (2nd place), String Functions (2nd place), Logical Functions (3rd place), Math Functions (3rd place), SELECT Statements With UNION, EXCEPT, INTERSECT (3rd place), Supported Data Types (3rd place). Its SQL engine is not the best - it was unable to win even single category of 12. It has problems in Math Operators (3/9 failed - cannot divide integer values correctly), Logical Functions (7/22 failed), Date and Time Functions (25/37 failed), SELECT Statements With Joins (10/40 failed, does not support FULL JOIN), DDL Statements (12/43 failed). The troubles started since installation - it failed to install in both RAD Studio XE and Delphi 7 - most of units missed, only .bpl files were extracted. So all tests has been done from ElevateDB Manager. It is the only database from our test that does not support standard SQL scripts (DDL statements separated by semi-colon) and numbers in ORDER BY clause (ORDER BY 1 DESC). As for minor bugs, there are a lot of them in different tests - EXTRACT(WEEK FROM Birthday), CAST(Birthday as CHAR), CASE Gender WHEN 'F' THEN 'Female' ELSE 'Male' END, CASE WHEN Gender = 'F' THEN 'Female' ELSE 'Male' END, bugs in correlated subqueries, incorrect evaluation of COT function, and so on. The feature set is rather good, it lacks only support of normal SQL scripts and full support of auto-increment fields (with possibility to get last inserted auto-increment value). As for deployment, ElevateDB can be compiled into executable and stores data in own proprietary format (2 files per table plus configuration and lock files).
Mediocre SQL engine (62 bugs, 56 unsupported features), leader in category Math Functions. Runner-up in 3 categories: Basic Queries (4th place), Logical Functions (2nd place), SELECT Statements With UNION, EXCEPT, INTERSECT (3rd place). The SQL engine could be much better - it practically does not support subqueries (only 2 uncorrelated subqueries executed correctly, all correlated and most of uncorrelated subqueries failed). It has problems in Math Operators (4/9 failed), String Functions (15/39 failed), Date And Time Functions (26/37 failed), SELECT Statements With Joins (13/40 failed, does not support FULL JOIN), Subqueries (20/22 failed), Supported Data Types (8/27 failed), DDL Statements (15/43 failed). It does not execute SELECT with ORDER BY on the field that was not selected for output, has serious bugs in operations with date values, like d1-d2, EXTRACT(WEEK FROM Birthday) and almost does not support subqueries. DBISAM got 30 point lesser then ElevateDB (181 vs 211), rather poor system. The feature set has serious misses - referential integrity (foreign keys) and stored functions are not supported. RowsAffected property of TDBISAMQuery does not work on DDL statements. As for deployment, DBISAM can be compiled into executable and stores data in own proprietary format (2-3 files per table plus lock file).
Poor SQL engine (57 bugs, 75 unsupported features), leader in category SELECT Statements With UNION, EXCEPT, INTERSECT. It will take too long time to describe all numerous bugs and unsupported features of this engine, just mention that it has good result only in 1 category of 12 (1st place, 13/13 queries). The weakest categories are: Basic Queries (7/14 failed), Aggregate Functions (8/16 failed), String Functions (17/39 failed), Logical Functions (13/22 failed), Math Functions (10/15 failed), Date And Time Functions (25/37 failed), SELECT Statements With Joins (16/40 failed), Subqueries (13/22), Supported Data Types (9/27 failed), DDL Statements (19/43 failed). The engine is not stable - simple SQL statement (SELECT ID,Car FROM orders ORDER BY Sale_price) crashes the application, the engine cannot execute any other statements, on exit Windows shows the dialog with message that application needs to be closed. TurboDB like Firebird cannot execute CREATE TABLE and INSERT in same script, transactions cannot be handled by SQL statements, cannot export database to SQL script (reverse engineering), like DBISAM does not support RowsAffected, has no triggers and views. However it has full text indexing and stored functions support. TurboDB compiles into executable and can store all tables in single database file. Unfortunately it cannot be recommended due to instability and poor SQL functionality.
The worst SQL engine among all tested databases (102 bugs, 95 unsupported features). Runner-up in String Functions category (3rd place). Appollo has serious problems in all categories, the best result is in String Functions (12/39 failed), the worst result in Subqueries (22/22 failed). The product causes an overwhelming desire to uninstsall it and forget about it just after few minutes of use. The DDL statements does not work at all, even simple CREATE TABLE type1 (f1 INTEGER). The worst thing, that it is caused by the bugs, not data types support. The CREATE TABLE example from help file does not work too. The application built by the evaluation version requires to start it as Administrator under Vista/Windows 7. The library files are not installed into IDE automatically. The CAST operator seems having same bugs as in CREATE TABLE - totally unusable. The subqueries are not supported in any form (22/22 failed, even uncorrelated). The list of bugs is so long, that I will not spend my and your time on it. Everybody can try to run SELECT Now() FROM emp WHERE id = 1 to get fun - you will see some buggy characters instead of the current date and time. The feature set is so poor, that only forces to uninstall it as soon as possible. Apollo requires library files to deploy, stores data in DBF format. There are no any significant advantages, but lots of bugs and unsupported features. Shortly, I have only one conclusion - many years lagging from all competitors. The worst of all SQL database systems that I ever tried for last 25 years. Shame on its developers!
This test and article were made and publishing by the volunteers of SQL DB Test Team.
The original of this article is published on http://sql-db.cz.cc. This is the permanent URL where you can find the latest version of the database investigation we made.
You can freely print, copy, distribute and publish this article elsewhere in case you do not change it. If you need to make some skipping, additions or any other changes, you should contact us to get writing permissions.
|
|