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

From: Dan Crowley <dcrowley_at_informix.com>
Date: 1997/04/18
Message-ID: <3357D8D8.35C5_at_informix.com>


Pablo Sanchez wrote:
> In article <3351f3b1.8299223_at_gate.idg.no>, Nils.Myklebust_at_idg.no writes:
> > ichudov_at_algebra.com (Igor Chudov @ home) wrote:
> >
> > :I have been asked to do a comparison of captioned database engines,
> > :to choose a database server for a medium size database (less than 2GB,
> > :about 60-100 tables).
> >
>
> I can do a *moderate* comparison between Informix and Sybase (32 bit
> port):
>
> Sybase 11.0.X Informix 7.22
> ------------- -------------
> o ~2 gig max for data cache o 1 gig max for data cache
>
> Drawback Informix. With a smaller data cache there's less data you
> can cache in memory.

But the gentleman says that his database will be less than 2 gig. Also, for 64 bit systems like DEC alpha, Informix uses 64 bit large memory addressing.

>
> 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
>
> Drawback Informix. I/O is *always* the bottleneck. Allowing 2/4K
> only is very limiting. Oracle does it even better... allowing up
> to 128K per I/O. Excellent for DSS and/or table scans.
>

Informix does 16K I/O when doing light scans. Also, checkpoint writes are sorted for better performance (keeps disk heads from randomly seeking - I'm not sure if Sybase does this). Finally, are there any controllers that make use of I/O > 16k?

> o private log cache manipulation o internal group commits, no management
> allowed by the dba
>
> Drawback Informix. By allowing the DBA to micro-manage the RDBMS
> you can tune for high performance. The log is always the
> bottleneck and allowing more "knobs" is excellent.
>

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.

> o table partitioning is limited o heavy support for table
> fragmentation
>
> Drawback Sybase. Informix has excellent "logical" striping. You
> can fragment (stripe) your table based on key values or
> round-robin. Very nice!
>
> o command line interface: o no command line interface. You
> isql/sqsh have to use their gui. Ick!
>
> Minor drawback Informix. Personally, I hate having to use their
> GUI. It's cumbersome because it's ascii based so it's not even a
> GUI.
>

DB-Access can be used as a command line interface. People just don't know how to do it. Yes it defaults to screen (curses) oriented, but you can also redirect input or use here documents.

> o virtual architecture is based o the RDBMS is comprised of many processes
> on multiple engines. One
> engine per CPU. Granularity,
> IMHO, is not fine enough.
>
> Drawback Sybase. I really like Informix's idea of having many
> smaller lightweight processes to do the work of the RDBMS. This
> really makes sense as machines with many processors are coming
> alive. It's easier to distribute the workload across multiple
> CPU's.
>

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.

> o device striping not supported o a DBSPACE may be comprised of
> striped chunks
>
> Drawback Sybase. This is a really neat feature of Informix to
> allow the DBA to stripe a DBSPACE across chunks (data devices).
>

Other things to consider...

o Only Page Level Locking o Page or Row Level Locking

Advantage Informix. This is a huge weakness with Sybase.

o 1 Isolation level o 4 isolation levels

Advantage Informix. Informix supports all 4 ANSI isolation levels: dirty read, committed read, cursor stability, and repeatable read. You choose the one that's right for your application. Also, with Informix you can "SET LOCK MODE TO WAIT 10" for example, which means that the database will only try for 10 seconds to acquire a lock. You can't do this in Sybase. If you try to lock something that someone else has and they have gone home, might as well pack your brief case.

o Replication Server on Side o Built in replication

Sybase uses a replication server. And it is very cumbersome.

One last thing that I'll mention is Stored Procedure Language. This is where, generally speaking, I'd give Sybase an advantage. TRANSACT SQL is quite powerful and flexible. HOWEVER, there are a couple of things that you can't do in Sybase that you can do with Informix. Like with Informix you can create a procedure that returns a value and use this is the select clause of a SQL statement. For example:

select myproc(col1, col2)
from mytable

There are times when this is quite useful.

That being said. There is NO comparision between Sybase and Informix Universal Server. With Universal Server you can write functions that execute in the server in C, C++, Stored Procedure Language, and soon also in Java. Not to mention the fact that you can add new data types, you can add new access methods, you can use inheritance and polymorphism ...

So Informix definitely has the advantage going forward. Received on Fri Apr 18 1997 - 00:00:00 CEST

Original text of this message