On Friday 16 January 2004 04:24, David Johnson wrote:

Hi,

> Unfortunately, I do not have control over my end-user's machines or
> architecture specifications. The company that I work for, for example,
> will not purchase any product that cannot use DB2 as the back end DBMS.
> Other large companies are married to Oracle or M$-SQL. I do not want to
> rule out the (lucrative) enterprise market by tying into a single DBMS.
> This product is aimed at an open market, so the DBMS must be as transparent
> to the product as possible.

We produce large scale eneterprise systems, and like you, it is extreamly
rare for a product of ours, to use a single, known RDBMS. Some systems might
use a number of different backends, other systems use a single RDBMS, but the
make/model varies depending on where it is deployed.

In years gone by, we have gone out of our way to produce portable code, so we
could switch backends by just swapping the driver etc. This took a lot of
work, and resulted in sub-par performance for all RDBMS, and the inability to
use 'the best feature' for the job in our code. If one RDBMS did not support
something, it effectivly means no RDBMS supported it. A classic case was
left outer joins in Oracle pre v9, which mean't we couldn't use them in any
RDBMS, other examples include FIRST, SKIP, EXISTS.

It also meant that our code contained a lot of stuff that 'emulaulating'
features of RDBMS's that all could do natively except one.

Even with Java, that really only has a single 'driver' technology (JDBC) that
works with just about every DB there is, it envolved just as much work,
because the features offered via SQL varied dramitically. Esentially
limiting us to just using 'basic' SELECT, INSERT, UPDATE, DELETE statments.

..and don't get me event started on Transactions, Locking, security etc.
differences.

In recent years, we have totally given up on portable code, and concentrated
on writing modular code. This allows us to use every feature and squeeze
every ounce of performce out of each specific RDBMS we use. Sounds like more
work/test, but it results in far less code and much, much, better performance.

There are many ways to do this, but probably one of the best is to use
Interfaces (we use Java and Delphi).

For all your DB functionality, write an interface, and code your system to
use those interfaces. Then for each RDBMS implement those Interfaces using
the best 'driver', and best features of the RDBMS targeted. You app doesn't
care what implementation its using, as long as it implements the correct
interface. (The implementations can even be switched at runtime if you use
Factories in your code).

As an example. Firebird does not support derived tables, Oracle does. To
write 'portable' code, you would probably have to run 2 standard queries and
'join' the result sets in you app in code, leading to more code in your app,
and bad performance. Using the 'Interface' method, you would be able to use
derived tables for the Oracle implementation, and maybe a stored procedure or
something in Firebird to get the same result... less code, easier to do,
better performance.

> My current task is to determine the performance characteristics of Firebird
> so I can extrapolate load capacities and know the limits of its scalability
> - will it be limited to desktop, or can I go up to 100 users, or 1,000
> users? What size box would be required to service my target 8,000
> concurrent users without undue strain?

How long is a bit of string <g> It totally depends on what you are doing.
We have systems that support thousands of users, running on a single FB
server on modest hardware, via appservers, performing fairly light 'work'.
We also have systems where users are perorming 'heavy work' (reporting, batch
updates), that require bigger boxes and can only handle user counts in the
10's.

Phil
--
Discover a lost art - play Marbles
April 2004
ICQ: 760757 | AIM: pjshrimpton | Y!: pjshrimpton | pjshrimpton@...