Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

Hi CDI peeps,

Pablo Sanchez wrote:

> In article <3357D8D8.35C5_at_informix.com>, Dan Crowley <dcrowley_at_informix.com> writes:
 

> I don't agree with the above justification for 1 gig data cache. On a machine with 4
> gig memory, we only get to use 25% of it?

Well, okay the buffer cache is limited to 1 Gb in 32-bit ports. But we can use some ofthe extra memory for virtual segment (thread stacks, sort space, dictionary/stored procedure
cache etc. etc.) so it's not quite the limitation you make out. Also, the 64-bit ports don't
have this limitation anyway.

> > >
> > > o variable I/O read/writes: o based on platform, only 2/4K read/writes -
> > > 2/4/8/16K although on the log there are
> > > group writes
 

> > Informix does 16K I/O when doing light scans. Also, checkpoint writes
> > are sorted for better performance
>
> Great! A definitive answer. Thx.

In fact, the I/O size for light scans is not fixed but is calculated dynamically for each scan.
Also, checkpoint writes are done in units of 8 buffers which makes them 16k (2k pagesize) or 32k (4k page size) writes. Also for read ahead and other sequential I/O we will group I/O for contiguous pages and do a larger physical read if possible.

> > (keeps disk heads from randomly
> > seeking - I'm not sure if Sybase does this).
>
> It doesn't. This is a really neat feature of Informix, simple and
> effective.
>
> > Finally, are there any controllers that make use of I/O > 16k?
 

> snip...
 

> > However, with Sybase you MUST log. You have no options (less knobs if
> > you will).
> > With Informix, for each database you can choose to do
> > unbuffered, buffered, or no logging. No logging is great for Data
> > Warehouse situations where you load once a day, week, or month, and the
> > rest of the time you're only doing reads.
>
> You raise some good points here. This is a very nice feature of
> Informix. I think that "no logging" is a bit of a misnomer in that
> the RDBMS must do some logging in order to conform to ACID.

An unlogged database in Informix does not support transactions and does not log changes todata. Certain DDL and internal operations are logged however.

> >
> > Another thing to mention is the parallel use of threads - particularly
> > useful for data warehousing, parallel index builds, or any time you want
> > to reduce the time to execute a time consuming SQL statement.
> >
>
> Most definitely. Although I find it a bit strange that the
> connections must specify the percentage to allocate. Sounds
> klunky... however the feature is great. Sybase 11.5 will
> have... but that's future...

You don't need to specify the percentage for parallel index builds. It isessential for heavy DSS queries to have some resource regulation to prevent (if desired) any single query hogging the entire machine (unless that is what you want of course). You have a choice to prioritise indicidual queries.

> > o Only Page Level Locking o Page or Row Level Locking
> >
> > Advantage Informix. This is a huge weakness with Sybase.
>
> No, this is a great marketing whitewash. I won't get into this
> religious war.

Sorry but it is a *big* limitation. Row level locking provides much greaterconcurrency, even for a well designed application database.

> This is true, this is nice defensive RDBMS for a bad application. A
> good application will increase throughput by reducing latency.
> Reducing latency is accomplished by decreasing the duration of locks
> and internal processing. This is why the row level versus page
> level religious war is silly. Vendors try to play this up like it's
> some great advantage. But it's not. Informix allows the user the
> *option* to have row level locking. If it was such a great thing
> with no overhead, why make it an option and not have it built in?
> Hmmmm....

The only reason that the default is page level locking is historical. It usedto be that way so we haven't changed the default to maintain backwards compatibility. We always encourage use of row level locking these days. Received on Fri Nov 21 1997 - 00:00:00 CET