Database Reference
In-Depth Information
though—and you'll apparently have in your head more knowledge about more databases than most anyone I know
of (after all, that's the only way to know if something has a chance of working the same on all databases!). Another
approach that is both more portable and offers better performance, would be to use stored procedures to return
resultsets. You will discover that every vendor's database can return resultsets from stored procedures, but how they
are returned is different. The actual stored procedure source code you must write is different for different databases.
Your two choices here are to either not use stored procedures to return resultsets, or to implement different code
for different databases. I would definitely follow the different-code-for-different-vendors method and use stored
procedures heavily. This might seem as if it would increase the time it takes to implement on a different database.
However, you'll find it is actually easier to implement on multiple databases with this approach. Instead of having
to find the perfect SQL that works on
all
databases (perhaps better on some than on others), you will implement the
SQL that works best on that database. You can do this outside of the application itself, which gives you more flexibility
in tuning the application. You can fix a poorly performing query in the database, and deploy that fix immediately,
without having to patch the application. Additionally, you can take advantage of vendor extensions to SQL using this
method freely. For example, Oracle supports a wide variety of SQL extensions, such as analytic functions, the SQL
model clause, row pattern matching and more. In Oracle, you are free to use these extensions to SQL since they are
“outside” of the application (i.e., hidden in the database). In other databases, you would use whatever features they
provide to achieve the same results, perhaps. You paid for these features so you might as well use them.
Another argument for this approach—developing specialized code for the database you will deploy on—is that
finding a single developer (let alone a team of developers) who is savvy enough to understand the nuances of the
differences between Oracle, SQL Server, and DB2 (let's limit the discussion to three databases in this case) is virtually
impossible. I've worked mostly with Oracle for the last 20 years (mostly, not exclusively). I learn something new about
Oracle
every single day I use it.
To suggest that I could be expert in three databases simultaneously and understand
what the differences between all three are and how those differences will affect the “generic code” layer I'd have to
build is highly questionable. I doubt I would be able to do that accurately or efficiently. Also, consider that we are
talking about individuals here; how many developers actually fully understand or use the database they currently
have, let alone three of them? Searching for the unique individual who can develop bulletproof, scalable, database-
independent routines is like searching for the holy grail. Building a team of developers that can do this is impossible.
Finding an Oracle expert, a DB2 expert, and a SQL Server expert and telling them “We need a transaction to do X, Y,
and Z”—that's relatively easy. They are told, “Here are your inputs, these are the outputs we need, and this is what
this business process entails,” and from this they can produce transactional APIs (stored procedures) that fit the bill.
Each will be implemented in the manner best for that particular database, according to that database's unique set
of capabilities. These developers are free to use the full power (or lack thereof, as the case may be) of the underlying
database platform.
These are the same techniques developers who implement multiplatform code use. Oracle Corporation, for
example, uses this technique in the development of its own database. There is a large amount of code (though a
small percentage of the database code overall) called
OSD
(Operating System Dependent) code that is implemented
specifically for each platform. Using this layer of abstraction, Oracle is able to make use of many native OS features
for performance and integration, without having to rewrite the majority of the database itself. The fact that Oracle can
run as a multithreaded application on Windows and a multiprocess application on UNIX/Linux attests to this feature.
The mechanisms for inter-process communication are abstracted to such a level that they can be reimplemented on
an OS-by-OS basis, allowing for radically different implementations that perform as well as an application written
directly, and specifically, for that platform.
In addition to SQL syntactic differences, implementation differences, and differences in performance of the
same query in different databases outlined earlier, there are the issues of concurrency controls, isolation levels,
query consistency, and so on. We cover these items in some detail in Chapter 7 of this topic, and you'll see how their
differences may affect you. SQL92/SQL99 attempted to provide a straightforward definition of how a transaction
should work and how isolation levels should be implemented, but in the end, you'll get different results from different
databases. It is all due to the implementation. In one database an application will deadlock and block all over the
place. In another database, the same exact application will run smoothly. In one database, the fact that you did block
(physically serialize) was used to your advantage but when you deploy on another database and it does not block,
you get the wrong answer. Picking an application up and dropping it on another database takes a lot of hard work and
effort, even if you followed the standard 100 percent.
