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

In article <3357D8D8.35C5@informix.com>, Dan Crowley <dcrowley@informix.com> writes: > > 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. 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? > > > > 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 Great! A definitive answer. Thx. > (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? I think that the question to ask is: are controllers being maxed out by 16K I/O? The answer, IMHO, is no. Ask the question, are there any disks that are being maxed out by 16K I/O? The answer is most definitely. > > 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. 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. Using the no logging option in Informix really is nice to data loading. Equivalently you can use Sybase's fast bcp routines to load unlogged however there are restrictions to this: no indexes and a database dump must be done afterwards. > > 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. "isql" is lame because it has no history. DB-Access is worst because the command line interface that Informix professes exist is nothing more than redirecting stdin. At least "isql" has a command line interface where you can invoke "vi" to edit your last command and so forth. It's klunky but way better than DB-Access. As I've stated before, my choice is to use "sqsh" which has a t-shell like interface. > > 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... > 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. > o 1 Isolation level o 4 isolation levels I'm not sure where you heard that Sybase only supports one isolation level??? This isn't true. set transaction isolation level 0, 1, 2 and 3 are supported. > 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. 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.... > o Replication Server on Side o Built in replication > > Sybase uses a replication server. And it is very cumbersome. Your characterization is subjective. I've used it and it's very simple. I don't know what version of the replication software you are basing your assertion on but RS 11 is very easy to use and replicate a whole database. > > select myproc(col1, col2) > from mytable > > There are times when this is quite useful. > Agreed. Sybase T-SQL also doesn't have very good support for Image/Blobs. Although they probably won't enhance this because it's an RDBMS afterall... > That being said. There is NO comparision between Sybase and Informix > Universal Server. Well you're comparing apples and oranges therefore there is no comparison. However something to keep in mind for the Universal Server is lack of speed and availability. It seems strange that alll the hype of three months ago is now gone? This is natural for a product that wasn't quite ready. When it is, look out. But then I believe we're talking future now aren't we? > So Informix definitely has the advantage going forward. Of course, from someone @informix.com... I'll quote from the movie, Do the right thing: Don't believe the hype. Don't align yourself with one vendor. Consider the *best* vendor for your application. Write a good benchmark that represents your workload. Everything that I've written on this subject and what everyone else has, is strictly their opinion. If you have to believe a TPC-C/-D, look at the details. Is your shop really going to be buying a 7 million (I'm not making this up) HP to get equivalent performance for TPC-D? Will you have the same engineering staff that produced that