SQL Bible [Electronic resources] نسخه متنی

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.

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.

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.