Thanks for your feedback.

The DBMS is an important part of my system, but it is not where the business logic is handled. The problem I am solving is the sort that gives data modellers nightmares - as a result, the DBMS is being used primarily as a data repository in this design. Since the problem is not susceptible to relational decomposition, complex queries, locking, fine grained DBMS security, RI, and many other advanced features of DBMS are not of great concern. Since the framework has its own security model with a finer granularity than any DBMS can support, the DBMS only needs to support an administrator and a single user ID for the application. No one else needs access to the DBMS itself.

For comparative performance, I have timed the business layer of the product at 500,000 transactions per second. Each test transaction is approximately equivalent, in database terms, to a single row update with full locks and commits between each row. The business layer queue's transactions consisting of 1 to n rows (averaging 10 rows per transaction) for the persistence layer to handle in its own time. Transaction mix is roughly 60% select, 35% insert, and 5% update. There is no delete logic anywhere except in the archival process.

All of the work for the users is being performed in the business layer of the framework. The DBMS impact on performance comes into play first when an object is requested that is not already resident in memory, and second when the users are all very busy and the persistence transaction queue has potential to fill up with pending insert/update transactions faster than it is emptying.

It is a lot of work, as you said, but I believe that the end product will warrant the effort. It's also been a fun learning experience so far.

I have started to sketch out my own high performance class framework (for Delphi) which will operate at the native interface level, provide reentrancy for multi-thread operation, and avoid the need for many of the overheads I observed in the DBExpress framework. With a granularity of 10 rows per transaction, it appears that I can double throughput over DBExpress.

I am already familiar withthe ISO CLI interface used by both MS-SQL and DB2 for high end markets. I only need to add support for Firebird to hit my low end market (and my own development needs).

Anyone know which C header file or if there is an object pascal unit that imports the fbclient.dll?

I have the most source code for 1.5 RC8, I just need to track down the correct header files from that build to address the DLL directly.

Thanks,

----- Original Message -----
From: Phil Shrimpton
To: firebird-support@yahoogroups.com
Sent: Friday, January 16, 2004 5:46 AM
Subject: Re: [firebird-support] Firebird/Internet performance questions


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@...

Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]