| Subject | Re: [firebird-support] Design question |
|---|---|
| Author | David Johnson |
| Post date | 2004-05-19T03:59:41Z |
MOM = Message Oriented Middleware
An example you might be familiar with is MQ series from IBM. Anything else that JMS can wrapper is a MOM implementation.
In my experience an actual need to delete rows is generally a symptom of a poor design. It allows circumstances to occur where a user can make an error that requires a programmer to "fix" data - which has support and legal rammifications. It allows audit trails to be broken (ask ex-Enron employees about broken audit trails). It makes it possible to remove good data from a system where it can be analyzed to determine business trends over time.
The cost of not deleting rows is that your database will grow. With IDE storage starting at at $1.00 per GB, this is not generally an issue. I have measured Firebird performance on an 800 MHz machine running queries against tables as large as 4 million records and determined that a simple search for a record on a CHAR(38) primary key in a database with 8K page size performs 4 I/O's per record retrieval, worst case. That is pretty much 40 milliseconds per record, with some gains possible in real life applications due to the caching mechanism built into the DBMS. At a little over 4 million rows you go to 5 I/O per record, with an inverse logarithmic performance curve becasue of how the B+ tree indeces work. The larger your table, the less the performance degrades as the table increases in size.
With attention to detail and tighter design around firebird specific features, Helen and Ann can direct you in techniques to get much better baseline searching than I am getting with my tests.
An example you might be familiar with is MQ series from IBM. Anything else that JMS can wrapper is a MOM implementation.
In my experience an actual need to delete rows is generally a symptom of a poor design. It allows circumstances to occur where a user can make an error that requires a programmer to "fix" data - which has support and legal rammifications. It allows audit trails to be broken (ask ex-Enron employees about broken audit trails). It makes it possible to remove good data from a system where it can be analyzed to determine business trends over time.
The cost of not deleting rows is that your database will grow. With IDE storage starting at at $1.00 per GB, this is not generally an issue. I have measured Firebird performance on an 800 MHz machine running queries against tables as large as 4 million records and determined that a simple search for a record on a CHAR(38) primary key in a database with 8K page size performs 4 I/O's per record retrieval, worst case. That is pretty much 40 milliseconds per record, with some gains possible in real life applications due to the caching mechanism built into the DBMS. At a little over 4 million rows you go to 5 I/O per record, with an inverse logarithmic performance curve becasue of how the B+ tree indeces work. The larger your table, the less the performance degrades as the table increases in size.
With attention to detail and tighter design around firebird specific features, Helen and Ann can direct you in techniques to get much better baseline searching than I am getting with my tests.
----- Original Message -----
From: Jesper B. Kiær
To: firebird-support@yahoogroups.com
Sent: Tuesday, May 18, 2004 8:48 AM
Subject: Re: [firebird-support] Design question
Hi
I was not trying to compare Lotus Domino to RDBMS, merely stating that
speading data to several databases in Domino/notes is quite normal. It is
true that IBM is moving to DB2 ín Domino 7 as an option, but you can still
use the old format.
The "real life" information about the RDBMS is very valuable, thank you!
Could you tell what MOM is?
"Never delete rows.." ? What are the reasons for not deleting data ?
regards
Jesper B. Kiær
Jezzper Consulting
Website : http://www.jezzper.com
"David Johnson" <d_johnson@...> wrote on 18-05-2004 06:22:47:
> Lotus Notes is not really comparable to RDBMS's, or so the Lotus
> Notes group at work tells me. The RDBMS is much more efficient for
> large structures, but notes works well for small scale rapid
> development. IBM is rewriting Notes to start using DB2 as its back
> end because of the issues with the Notes SAM structures in large
> scale environments.
>
> Where I work, our real-time production database is roughly 8TB in
> size. We support roughly 8,000 concurrent users in a real-time
> business environment on multiple platforms, processing roughly 1,
> 200,000 database transactions per hour. We're not using Firebird at
> work, but the principles of ISAM based RDBMS design don't vary a lot
> - only the specifics for particular implementations.
>
> In my experience, we break relational databases apart so that the
> real-time processes are not impacted by ad-hoc querying for decision
> support, or for security reasons. Each of our databases has several
> hundred tables (1,200 is the figure that comes to mind), with
> anywhere from 1 to several million rows. Busy tables will be broken
> into a "current" and "warehouse" table, with a nightly archive job
> that moves data from the current to the warehouse as it ages. Where
> data must be propagated between databases in a timely manner, we use
> a MOM product.
>
> Indexing is critical for your database app. Design compact indeces
> and minimize I/O. Keep relationships simple, and the processes to
> maintain them simple as well. Avoid duplicated work. Never delete
> rows (hot button ... oops), except as the cleanup after a successful
> archival process..
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
[Non-text portions of this message have been removed]