Subject ODBC/JDBC performance
Author Ray Holme
This is an old problem. Many drivers do local cache-ing, others do their
own SQL parsing (imagine the effort if you are joining two or more
tables from different databases). Many years ago, my company wrote a
driver that did this client-server thing (SQLRunner). We let the server
interprets the SQL (Oracle has it's own variants just as Firebird and
Interbase do) so you let the server worry about interpreting the syntax.
The second problem is how to get data as the syntax for getting the data
is by field when you get down to the final strokes - we cached as many
rows for a get as possible with the largest TCP-IP buffer we could
support and sent parts of an even bigger cache by dividing the 32k
server cache (or less, depending on how many rows could fit into that)
into 2k TCP blocks. The result beat the Borland equivalent for Interbase
by huge factors (20 mins VS 24+ hours to get a million+ rows). Then ODBC
came out (slow but industry accepted) - so the product was only used by
a few folks - i.e. still born. Of course, you need access to multiple
DBs (differing vendors perhaps) and the problem of doing a join between
two or more databases was left to the programmer. There was (maybe still
is) some code in the Firebird engine that could be expanded to make an
EXTERNAL table into a foreign DB object - I had hoped to do something
with that so the DB engine could do the join for you. Once in place, you
could join data from Informix (still there?), Ingres (dead), Oracle
(killing Sun) and Interbase/Firebird DBs - all located on different
machines or (shudder) on the same machine.

I could donate the code to the firebird development group if they want