Be Fundamentally Sound? (1)

Is Your Database Fundamentally Sound?

Virtually all RDBMSs have inadequate implementations of the relational model. Their implementation of relational is ad-hoc and not fundamentally sound. These systems suffer from errors and arbitrary limitations, incomplete optimizations and uneven performance.

Is your database system fundamentally sound, implemented according to relational principles? Tests of major database systems show that only two systems are relationally implemented - Ingres and FirstSQL. Other systems exhibit the problems described below.

Errors and Limitations

Improper relational implementations cause errors in processing and impose arbitrary limitations on queries. Because implementations are not systematic, feature support is uneven. Facilities that work in some contexts give wrong results in others. Some systems also provide extensions to SQL that are relationally incorrect and produce unpredictable results.

Most notable among common errors is the EXISTS bug. First documented by C. J. Date, the error occurs in virtually all implementations. Only FirstSQL has demonstrated a solution to this bug. The EXISTS bug is discussed in a number of places in these pages. See Can Your Database Handle Nulls Properly?

Ad-hoc implementations also force limitations on users. The complexity of queries may be arbitrarily restricted. The number of tables that can be joined, the depth of embedded operators and joins, the number of columns in a table may all have artifical boundaries. In many cases, this causes restructuring of the database schema and decreased normalization of the data.

Incomplete Optimization

A properly designed relational system decomposes a SQL command to a lower level algebraic form. It then uses mathematical theorems to transform the request into optimized form for processing. Other systems don't go through this step and must use ad-hoc optimization techniques. This results in incomplete optimization.

Inadequate optimizations force users to do extensive tuning to get reasonable performance from the systems. Simliar queries can have wide variations in execution timing. Incomplete optimization produces uneven performance.

Uneven Performance

One of the most noticeable aspects of ad-hoc relational implementations is uneven performance. Because these systems perform inadequate transforms during optimization, equivalent queries can have wide variations in performance.

In 1988, Fabian Pascal published a report on uneven performance by PC database systems, "SQL Redundancy and DBMS Performance" in Database Programming & Design, vol. 1, #12 (December 1988), pp. 22-28. Pascal constructs 7 queries using different formulations but producing the same result set on an example database. These tests illustrated the redundancy of constructs in SQL, but they also revealed uneven performance for all but one RDBMS - Ingres.

Ingres timings were basically the same for all 7 queries. Ingres also had the best average time. The other systems showed wide variations across the 7 queries. The worst timing was an order of magnitude or more than the best. In the case of Oracle, the worse timing was over 600 times the best.

FirstSQL was not released when the tests were performed, but subsequent runs of the same 7 queries against FirstSQL showed very even timings. There was less than 10% difference between the worst and best timings. FirstSQL was also second to Ingres in best average timings.

Conclusion

The uneven timings found by Pascal in his tests mean serious difficulties for users of these systems. Without deep understanding of the specific optimizer, the choice of the best performing formulation is rarely made.

Uneven performance and errors in processing indicate that these relational systems are not fundamentally sound in their implementation of the relational model. Only FirstSQL provides the benefits of a fundamentally sound system. Is your database fundamentally sound?

Return to Can your Database do this? Return to FirstSQL Home Page