Conference ulysse::rdb_vms_competition

91.0. "Ingres Competitive Info" by BANZAI::BERENSON (Rdb/VMS - Number ONE on VAX) Sun Mar 13 1988 03:41

This is a modification of notes I made while comparing INGRES with the
next release of VAX Rdb/VMS.  I have modified this information to make
the comparison between INGRES and VAX Rdb/VMS V2.3.  Note that many of
the "*" change to "-" with shipment of our next release and a lot of new
"-" appear.

Notes from comparison of INGRES V5.0/03 documentation to VAX Rdb/VMS V2.3:

- = INGRES Functionality Inferior to VAX Rdb/VMS V2.3

* = INGRES Functionality with some advantages over VAX Rdb/VMS V2.3

? = Should validate exact information

===========================================================================
* INGRES allows (requires!) each relation and each secondary index to be
placed in a separate file.  These files may be placed on different disks by
"extending" the database to include another disk/directory.  Relations may NOT
be partitioned over multiple files nor can a single file contain multiple
relations.  This limits both the size of an individual relation as well as the
user's ability to spread the I/O load over multiple disks.

* INGRES stores each secondary index as a relation, and therefore in its own
file.  These have the same restrictions as above (no partitioning or grouping)
and therefore impose the same size and performance constraints.

- INGRES supports only a single data page size of 2048 bytes.  Records
may not span pages, resulting in a maximum record size of 2008 bytes.
VAX Rdb/VMS V2.3 supports variable size pages (max?) and records up to
64KB (?). Records may span data pages if necessary. 

- INGRES supports a maximum of 127 columns in a single relation while VAX
Rdb/VMS supports up to 2000 (?).

* INGRES provides more flexability in indexing structures than VAX Rdb/VMS
V2.3.  In particular, they provide an ISAM structure for static data that does
not require any locking (but does require periodic manual reorganization if
the data is not totally static).  ISAM cannot be used to answer queries
requiring data in sorted order.  They also provide Hashing.

* INGRES utilizes somewhat different BTREE algorithms to minimize lock
contention when compared to Rdb/VMS.  In particular, INGRES ensures that locks
need not be held on any level of the BTREE other than the leaf nodes.

- INGRES does not rebalance the BTREE on deletions.  This eliminates some
concurrency problems, but forces the user to perform periodic manual
reorganizations of the index to re-balance it.

- INGRES attempts to speed the storage of new records by not reusing the disk
space from deleted records unless that space is on the data page "associated"
with the leaf node.  Again, this forces the user into periodic manual
reorganizations to reclaim disk space.  Also, it will result in lower
performance for some range retrievals since more I/O will be required to read
in the data pages containing the desired data.

- INGRES BTREE leaf nodes consist of an entire database page (2008
bytes, after the page header).  With small keys, a very large number of
entries can fit in a leaf page.  Since INGRES locks leaf pages, a large
number of records are simultaneously locked.  VAX Rdb/VMS allows the
user to set the size of B-Tree nodes to reduce the number of records
that are locked whenever a leaf node is locked.  (One could use INGRES'
MAXINDEXFILL parameter to force the actual amount of space used within a
leaf node to be smaller and therefore achieve somewhat similar results.
However, this would also result in a large amount of space being wasted
since INGRES would still allocate a full 2KB page for each index and
leaf node.)

- INGRES typically does I/O one page at a time.  VAX Rdb/VMS V2.3 reads
in an entire buffer on each I/O.  When writing data, VAX Rdb/VMS V2.3
will write as many contiguous data pages as possible in a single I/O. By
default, a buffer is equal to 3 times the largest data page defined in
the database.  The user may control this by explicitly specifying a
buffer size.  Larger I/Os generally lead to improved performance. 

- INGRES executes in 2 processes per user.  When the user runs an INGRES
application, a subprocess is created to act as a back-end process. These
processes communicate via mailboxes, a very CPU-intensive mechanism for
transfering data and control information.  VAX Rdb/VMS runs as a
privileged subroutine library in the same process as the application
program is running.  Control information is passed via the normal VAX
Procedure Call instruction, with similar overhead to calling any system
service (mailbox I/O, by contrast, requires multiple system service
calls).  For uncompressed data where no data type conversion is
required, VAX Rdb/VMS transfers data from its buffers to the user
application program with a single instruction per field.

- INGRES only allows a single location to be specified for the temporary
files required for large Sorts.  This limits the maximum sort size to
the amount of free space on a single disk drive or VMS Volume-Set.  VAX
Rdb/VMS supports up to 2 temporary sort workfiles, each of which
may be placed on a separate disk or VMS Volume-Set.

- When a file needs to be extended, INGRES attempts to allocate space in
32-block chunks.  At high transaction volumes, this may result in very
frequent calls to VMS to extend the journal file, a very time consuming
operation (which we have found noticably impacts response time at high
transaction rates). VAX Rdb/VMS allows the DBA complete control
over the initial and subsequent extension size of database and snapshot
files. 

- INGRES only allows a new column to be added to a relation by copying
that relation into a temporary relation, adding or omitting a column in
the process.  The original relation is then deleted and recreated by
copying the data from the temporary relation back into it. The same
procedure must be followed to change the datatype of a column.  This
requires that sufficient disk space be available to hold both copies of
the relation.  It also is very time-consuming when large relations are
involved.  VAX Rdb/VMS allows all of these operations to be
performed dynamically without copying any data.

- INGRES allows a base relation to be deleted even though a view exists
on that base relation.  A new base relation with the same name but
totally different columns may then be created and the view will return
completely invalid data.  VAX Rdb/VMS will not allow a base
relation to be deleted unless all views referencing that relation are
deleted first.  Thus, it prevents users from seeing invalid data. 

- INGRES supports up to 10 base relations per view, while VAX Rdb/VMS V2.3
supports up to 32 base relations per view.

- INGRES does not provide a cross-relation integrity checking facility.  That
is, it does not support any form of referential integrity.  VAX Rdb/VMS 
allows the DBA to define cross-relation integrity constraints, including
ensuring referential integrity (?check that its safe to say this?).

- INGRES' COPY command ignores it's integrity constraints (equivalent to our
VALID IF checks) thus allowing a table to contain invalid data.

* INGRES provides more direct field-level security than VAX Rdb/VMS V2.3
and provides value-based security.  On the other hand, INGRES does not
(?) allow a user to have different access rights to a view than to the
underlying relations, while VAX Rdb/VMS does.  Using this latter
capability, VAX Rdb/VMS can provide much (but not all) of the
security capabilities of INGRES. 

* INGRES' query optimizer is capable of using the minimum and maximum
values for a column, as well as a histogram of the distribution of data
values for the column, and the average number of duplicate values, as
well as the number of records in the relation ("cardinality") and number
of data pages allocated to the relation.  VAX Rdb/VMS only uses the
cardinality of the relation.  Therefore, for a small number of queries,
the INGRES query optimizer is more likely to choose an optimum execution
strategy than the VAX Rdb/VMS V2.3 optimizer. 

* INGRES supports a global cache of database pages.  Under some circumstances
this can result in applications performing less I/O and using less memory than
the per-user cache scheme used by VAX Rdb/VMS V2.3.  In many applications,
particularly those where different users access different portions of the
database, the global vs local cache approaches will make little difference in
performance.  In a very small number of cases, the global cache approach will
result in lower performance because the data access pattern of one user
application can interfere with the access pattern of another application.  This
is particularly true when the size of the global cache is not set appropriately.
On VAXclusters, INGRES REQUIRES that the global cache be disabled. 

- INGRES requires that a utility, OPTIMIZEDB, be run periodically in
order to gather its statistics on data values.  This can be a very
time-consuming operation and, if the DBA chooses the wrong information
to gather or does not update the statistics when necessary, query
performance will suffer. VAX Rdb/VMS maintains its' statistics
automatically. 

- INGRES does not support fine granularity locking, its finest
granularity being the Page level.  VAX Rdb/VMS supports locking down to
the level of the individual record. 

- INGRES locks the entire relation if it's query optimizer estimates
that more than 10 pages will be locked by a single query.  In addition,
if the optimizer estimates that less than 10 pages will be locked, but
more than 10 pages are actually locked, INGRES will attempt to upgrade
the page locks to a relation lock (and drop the page locks).  Under
conditions of heavy concurrent access, this can result in deadlocks. The
user can change the default of "10 pages" before using a table lock by
specifying the MAXLOCKS parameter for each session.  VAX Rdb/VMS V2.3
provides a much more flexible locking scheme.  As part of that scheme,
VAX Rdb/VMS supports Adjustable-Lock-Granularity (ALG).  Using ALG VAX
Rdb/VMS will attempt to use the coarsest granularity lock available to
access a particular piece of data, starting with the entire relation (or
partition of a relation).  As other users make lock requests which
conflict with the coarse granularity lock, VAX Rdb/VMS downgrades that
lock to finer granularity locks.  This scheme is deadlock free and uses
the minimum number of locks necessary while achieving maximum
concurrency.  VAX Rdb/VMS V2.3 also allows a user to specify that a
relation-level lock is to be used for a particular relation  and will
always use a relation-level lock when sequential access is used to read
records from a relation. 

- INGRES does not provide a SNAPSHOT transaction facility like VAX
Rdb/VMS's READ_ONLY transactions.  Thus, INGRES applications which only
perform read access to a database (such as long-running reports)
interfere with update transactions.  INGRES does provide a way for
applications performing only reads to disable taking out locks on the
data, however this results in the application seeing inaccurate data (in
other words, useful for a casual scan of the data but not appropriate
for end-of-period reporting).


- On system failures, process deltion, or even if a process is
terminated with CONTROL-Y and then the user logs out before the INGRES
back-end process can cleanup, the database is left in an inconsistent
state.  Further access to the database is denied until the RESTOREDB
utility is run.  VAX Rdb/VMS V2.3 automatically restores the consistency
of the database without manual intervention.

* INGRES includes many more application development tools under the
INGRES banner than does VAX Rdb/VMS V2.3.  The VAX Rdb/VMS V2.3 approach
provides the buyer with more choice of development tools, at the cost of
a somewhat less integrated approach.  VAX TEAMDATA and VAX RALLY (as
well as VAX Datatrieve and VAX COBOL Generator) are Digital's current
offering in the Database Application Development Tools arena.

    This is an absolutely excellent competitive analysis of
    Ingress.  Is there a chance you've updated it since '88?
    Both products have gone through some great upgrades. I'm
    in a competitive situation with an installed Ingress customer
    who is considering Rdb, so I really need up to date help.
    
    Thanks!
    
    Beau Schless  dtn 224-1647
    

I did the comparison once because I personally needed to know how
Rdb/VMS compared to Ingres.  Unfortunately, no one in the marketing
group picked up on it and did an update after INGRES (and ORACLE and
SYBASE and IBM) came out with major releases.

I (1) Don't really have the time to update it and (2) Don't have access
to the documentation necessary.

    EB-M1859-45 is a consultants report comparing MANY different DB
    products. Rdb/VMS fares very well in this report. It was listed in the
    Database Resources handout at DUIT.
    
    Orderable at Northboro
    
    
    Barry