The Future of Embedded SQL

The Embedded SQL was born of the first attempts to extend SQL with procedural language capabilities. Its main function was to carry on the internal RDBMS control and administration. The other goal was to provide the ability to write programs that could connect to the database, retrieve, process, modify data, and so on. The first task is being gradually delegated to stored procedures, functions, and triggers that have better mechanisms of doing that. The second is still mostly done using various programming languages; however, the mechanics of databases constantly evolve. Most modern programming languages provide different (sometimes more efficient and/or easier to use) means of accessing RDBMS. These include ODBC, JDBC, CLI, along with the object-oriented interfaces (like Microsoft ADO) — to name just a few.

Embedded (Static) Versus Dynamic SQL: Advantages and Disadvantages

One may ask, what is more efficient, embedded or dynamic SQL? Most sources (SQL books, Web sites, etc.) unambiguously answer this question in favor of the embedded SQL. The reason is simple — in the embedded SQL, the first four steps of SQL statement processing are done in compile-time and do not need to be repeated again in runtime. In theory, these speculations are difficult to argue, but in real life, not everything is that simple.

The key to the understanding of the issue lies in Step 3, which is the optimization step of SQL processing. Most modern RDBMS have special built-in components called optimizers that create an execution plan for each single SQL statement, usually based on dynamic system catalog information. That means the execution plan for a SQL statement that was optimal one day may be far from optimal in a month, and even less so in a year. This is especially true for large DML-intensive databases where the optimal execution plan may change within days or even hours. The problem is that the plan is hardcoded into your program and you will have to recompile it to change it. The price for bad execution plans for SQL statements in your program can thus be very high; in fact, it could be much higher than repeating all five SQL processing steps multiple times.

Using the two-step dynamic SQL could help to improve performance even more — you might need to PREPARE your SQL statement just once and then EXECUTE it as many times as you need during the run of the program.

However, none of previously mentioned advantages apply to databases that are static — the optimal execution plan for such databases is probably not going to change for many years.

The final answer is — it all depends; there is no silver bullet, and developers have to make decisions for each particular case.

Cross-References 

ODBC, JDBC, CLI, and other alternative methods of accessing RDBMS are discussed in Chapter 16.

In spite of all this, embedded/dynamic SQL still has its niche in the contemporary programming world — the embedded database market. Embedded databases are widely used in mobile and wireless devices, consumer electronics, medical equipment, and more. Using embedded/dynamic SQL requires less memory and processor power and provides fast and reliable access to relatively static embedded databases, and with embedded databases making their way into a vast array of the new devices — cell-phones, microwaves, personal digital assistants (PDA), and the like — embedded SQL is experiencing a revival.