ODBC (Open Database Connectivity) enables access to the relational databases from C/C++ applications; ODBC interface is based on a driver manager usually integrated with an operating system
JDBC (Java Database Connectivity) enable access to the relational database from Java applications; JDBC is based on a driver manager
A native interface (Pro*C/C++, OCI8, SQLJ) enables access to a particular database from C/C++/Java applications
ODBC/JDBC drivers provide transparent portability but worse performance then native interfaces; ODBC/JDBC drivers are built on top of native interfaces
Object-oriented programming forces encapsulation of object classes
Encapsulation of object classes forces one object at a time technique of object processing
Consider the relational tables
DOCUMENT(title, class, edate)
ACCESS(user,class)
A query Find all documents available to a given user is implemented as the following SELECT statement
SELECT title
FROM DOCUMENT JOIN ACCESS
ON DOCUMENT.class = ACCESS.class
WHERE user = ...;
SQL query optimizer is able to pick an appropriate algorithm for implementation of join operation
Object-oriented programming
Object-oriented programming forces encapsulation of object classes
Encapsulation of objects classes forces one object at a time technique of object processing
class Document{ String title;
String className;
Date edate )
class Access( String user,
String className)
A query find all documents available to a user is implemented as
for(a in Access)
for( d in Document)
if a.user = ... and d.className = a.className then
out(d.title);
Object-oriented programming
In object-oriented view, join is performed by an application and not by a database server
One object at a time technique of object processing forces nested loop implementation of join operation
When object-oriented application is processed remotely from a database systems the relational tables must be transmitted to a remote site
Database application programmers should directly access a bulk object (e.g. a collection of the documents) instead of forming the member objects individually and grouping them into a bulk object inside an application code
A query must be stored at and it must be processed by a database server
Application development tools may access metadata (data dictionary) to get information about column names and column types each time an application tries to access an object mapped into a row in a relational table
It takes too much time and the benefits are practically nonexistent !
A good idea is to turn off the options that allow access to metadata and to test each SQL statement outside an application
Database transactions should be organized such that they do not include user interactions, for example manual screen update, or manual data entry
To avoid such a problem transaction should whenever it is possible divide an update into read transaction and local update into write transaction (transaction chopping)
The operations required when accessing multiple and heterogeneous databases include physical reorganization, data cleaning, semantic reconciliation
Database gateways provide transparent access to external relational and non relational data sources
Database gateways support distribution transparency, heterogeneity transparency
Performance tuning includes
shared connections to reduce startup-costs,
pass-through SQL, (SQL statements written in a dialect of an external data source) to reduce CPU costs statements, large transfer blocks to reduce transmission costs (minimize a number of round trips)
References
D. Shasha and P.Bonnet Database Tuning Principles, Experiments, and Troubleshooting Techniques, Morgan Kaufmann, 2003, chapters 5, 6