Re: informix SE vs Online 5.x

->From: davids@Software.Mitel.COM (David So) ->Subject: informix SE vs Online 5.x ->Date: 8 Jul 93 13:39:42 GMT ->Reply-To: davids@Software.Mitel.COM (David So) ->Organization: Mitel. Kanata (Ontario). Canada. -> ->Hello, I am trying to do a comparison between SE and ->online, and I believe online is superior than SE ->according to the specification. -> ->I appreciate someone can give me some good reasons that ->SE will be used instead of Online regardless of the ->price. -> ->thanks/david -> ->-- ->David Y. So ->Mitel Corporation Phone: 613-592-2122 x3018 ->350 Legget Drive, Kanata Fax : 613-592-4784 ->Ontario, Canada K2K 1X3 Email: david.so@Software.Mitel.COM -> David, I believe you have found the key point, which is "according to the specification". Informix OnLine is a much more powerful product than Standard Engine: 1. OnLine supports more data types, including VARCHAR, TEXT, and BYTE. 2. OnLine supports raw I/O for its database storage, while Standard Engine does not. 3. Online is significantly more tuneable than Standard Engine. 4. As I discussed in a separate message, OnLine keeps more statistics than Standard Engine; so I would presume its cost-based optimizer is "smarter". Given all this, why would someone choose to use Standard Engine? Well, in general, I believe that Standard Engine is targeted at small to medium sized applications, while OnLine goes after the monster apps. But see below. A Testimonial for Standard Engine --------------------------------- As a DBA for several projects using Standard Engine, I can tell you that Standard Engine just WORKS, and works well. You install it and it runs. (By the way, I believe that SE is uniformly less expensive than OnLine, but I haven't done pricing recently, so I can't be sure.) My primary project involves a database running under Standard Engine version 2.10.03 (can you believe it?!) on a Sparc 690 in Denver and supporting a network of Sparc systems from Cape Canaveral, Florida, to Vandenburg AFB in California. The DB contains 90 tables with the largest table having ~59,000 rows and several tables in the 20-30,000 row range. Based on my activity log table, I have 97 active users (out of 394 recognized) who average 41 logons (for the group, not per each) to the database per day. This is obviously not a high thru-put transaction processing system, but neither is it trivial in size. (The I4GL application is actually a front-end file manager for a series of CAD products.) For our purposes, the Standard Engine works very well, giving us reasonable response time. Our worst-case standard queries take 8-10 seconds. As DBA, I am the only person who does direct ISQL queries/updates. When I run certain peculiar queries, with four-table joins that don't use indexes, I sometimes get response in the 60-90 second range, but that is unusual, and acceptible. I never tune the system, since I can't. The closest I have come to tuning is to add an index to support a standard query that was taking too long. The application (actually the engine) automatically picked up on the index and later queries had improved response, without any application changes. If I sound like a satisfied customer, indeed I am. Back to OnLine -------------- If you have been following this newsgroup for any time, you will have noticed many, many questions about tuning OnLine engines. As I have said, you can't really tune Standard Engine, there are no "knobs" to turn, no parameters to set. By the same token, you can't DEtune it either. I have read horror stories of OnLine performance going down the tubes when the database was badly tuned. So SE takes a lot less "care and feeding" than does OnLine. Raw I/O is a key feature of OnLine, and is supposed to speed up performance. However, Shane Booth of Co-Cam in Australia reported that for a sequential scan of a 10 row x 50,000 row join, he has experienced faster results with a cooked (standard file system) database than with a raw I/O implementation of the same database. He asked how this could be so. While trying to answer that question, Jonathan Leffler of Informix asked the following supporting questions: ->We're going to need to know a good deal more about your hard disk ->configuration and other aspects of your system. ->How many physical drives have you got? How are they partitioned? Where is ->the raw device space, the cooked space, the root file system, the tmp file ->system, the swap space? How much shared memory (buffers) have you ->configured? How big is your kernel buffer pool? Have you run update ->statistics? Would it make any difference? How much other activity is ->there on the machine? I believe that this supports my statements about tuning and "care and feeding". Some Conclusions ---------------- It is not my intention to trash Informix OnLine. It is a fine product, but not everyone needs it. I only see two real reasons for choosing Informix OnLine over Standard Engine: 1. You really need the extended datatypes that are only supported by OnLine. However, the application I described above manages megabyte engineering drawings in external files rather than in BLOBs, so BLOBs (TEXT or BYTE) are not the only way to do such things. 2. You have really high transaction volume, more than SE will support. Bear in mind that the high thru-put comes at the cost of significant tuning effort. David, I hope this somewhat verbose reply is of some help to you. O ___________________________ Regards, H | R. Alan Popiel |__________________ Alan H | Martin Marietta, Tech Ops | Internet: |_________________________ H | P.O. Box 179, M/S 5422 | alan@den.mmc.com | / H | Denver, CO 80201-0179 USA | Voice: | Std disclaimers apply./ H |___________________________| 303-977-9998 | ( H (_____________________| (But you knew that!) \\ H (___________________________\\