Subject Re: [firebird-support] which one faster (overall) , MSSQL or Firebird ?
Author David Johnson
> MS-SQL has been successfully used to support databases as large as 64
> terabytes. I believe that 7GB is the effective limit of a Firebird
> database.

Is the 7GB a typo for 7TB? Or, what is "effective limit" ?

It is not a typo - it is an honest mistake. Obviously I was wrong this limit. You can't argue with empirical evidence.

> All current relational DBMS are ultimately based on the same ISAM
> technology, so the query optimization and hardware selection are the two
> keys to performance.

In mixed mode (select/insert/update/delete) use, the locking (or not) can
be a factor.

Absolutely true. My tests so far are aimed at my own framework's operation, which is designed to very rarely have locking issues at the physical persistence (DBMS) layer (think of the implications of a paradigm that only permits insert and select operations under normal operation). There are industry standard cheat ... er ... benchmarks that I am not replicating (yet). My tests are relatively immature so far, and are as much concerned with idenitfying bottlenecks in my code and tool selection as with the underlying DBMS.

Practical experience with large databases (8TB in DB2, not interbase) is that locking conflicts can decrease a DBMS apparent performance by a couple of orders of magnitude. By careful attention to code design and resource marshalling, locking conflicts can often be eliminated. For example, the simple expedient of claiming locks on tables in alphabetical order by table name will absolutely eliminate deadlocks on any system. It's not magic of alhabetical order, it's simply that a deadlock occurs when two processes request concurrent locks on two or more resources in a different order. Alphabetical order is an easy rule to remember, and is ensures that rresource locks are never requested in different sequences, so they will generally be released in the time frame allowed for pending locks.

That seems like a pretty serious generalization. If you mean that all
current relational databases use indexes and can scan tables serially,
then I guess I agree. But I've never seen an ISAM system that implements
multi-generational concurrency. Perhaps I'm behind on my reading about
ISAM (likely, in fact) but the ISAM systems I'm familiar with store their
data as the leaf-level data. Some databases do that, many do not.
Postgres has some very interesting features that I've never seen in an
ISAM system. So does Firebird, even without considering such frivolity
as views, selectable stored procedures, and constraints.

ISAM forms the basis of the technology, but it is not the limit of the technology. ISAM is twenty years old or more, and advances have been made on its foundations. Think of it as one part of a many layered system. The physical layer is the hard drive or DASD, then there is one or more OS level layers that controls the physical interaction and directory structure of the system. On top of that sits the ISAM (or VSAM which is a blatant rip off of ISAM) layers that define the mapping of "table" and B+ tree indexing structures to pages, and provide for the interface to the OS level layers for I/O and maintenance of those data structures.

In the early days, each table and index was mapped to a different physical file. Examples are VSAM on the IBM mainframe systems, and dBase on the PC's. The addition of abstraction layers (the SQL parser and query optimizer) added potential for further extensions using the ISAM base - most DBMS today map multiple tables and indeces into the same ISAM file (or files), taking advantage of the ISAM page mapping functionality keep all members of the database in a single physical file structure, and then depending on the SQL layers to isolate the developer from the physical implementation.

You are right that ISAM, on its own, does not know about these higher level abstractions. It is the layer that is "concerened with leaf level information". It is the foundation on which the higher level constructs depend.

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