Performance comparison btn Informix and SQL Server

Hi, recently I have set up an experimental environment for performance evaluation among DBMSs, such as Informix, MS SQL Server 7.0, Oracle 8 and DB2. The test data set I use is generated by "dbgen" using TPC-H. Before the actual heavy-loaded tests, I have just run some small tests on both Informix and MS SQL and get the following surprising results. The small test is run on 250MB data, and Informix 9.20 is running on a Sun Ultra 20, Solaris 2.7, 2 CPU, 512MB RAM machine, and MS SQL Server 7.0 is running on a PC, NT4.0, 2 CPU, 512MB RAM. One of queries I run is: select c.mktsegment, sum(l.qty) from customers c, orders o, lineitems l where c.cust_id=o.cust_id and o.order_id=l.order_id and (c.mktsegment='BUILDING' or c.mktsegment='AUTOMOBILE' or c.mktsegment='MACHINERY' or c.mktsegment='HOUSEHOLD') and (o.priority='1-URGENT' or o.priority='2-HIGH') group by c.mktsegment order by c.mktsegment; MS SQL Server needed ca. 65 seconds to complete this query, however, Informix needed ca. 206 seconds (with indexes) or 510 seconds (without indexes defined on the data set), respectively to complete the above query. Althoug it is not generally comparable between these two installation, since they run in diff. platforms with diff. OSs, the preliminary results are simply weird. I hope that there is someone from Informix, who can give me advises on the configuration of Informix IUS 9.20. Maybe I have done something wrong by configuration. P.S. MS SQL Server uses cooked-files to store data, while Informix uses raw devices to store data!!! Best Regards, Ming-Chuan Wu

Most likely a configuration problem. Goodness, you can fit your whole database in RAM if you wanted to. You should be able to get much better response than that. Can you post your onconfig file? onstat -c. Have you run update statistics? Was anything else running on the box while you were doing the testing? Also, it might be nice if you posted the output from an explain plan. At the beginning of the SQL put in 'set explain on;' (without the quotes). That will make a file in the current directory called sqexplain.out. Post that file as well. In article <8kcrtv$4rp$1@sun27.hrz.tu-darmstadt.de>, Ming-Chuan Wu <wu@dvs1.informatik.tu-darmstadt.de> wrote: > Hi, > recently I have set up an experimental environment for performance > evaluation among DBMSs, such as Informix, MS SQL Server 7.0, Oracle 8 and > DB2. The test data set I use is generated by "dbgen" using TPC-H. > Before the actual heavy-loaded tests, I have just run some small tests > on both Informix and MS SQL and get the following surprising results. > The small test is run on 250MB data, and Informix 9.20 is running on a Sun > Ultra 20, Solaris 2.7, 2 CPU, 512MB RAM machine, and MS SQL Server 7.0 > is running on a PC, NT4.0, 2 CPU, 512MB RAM. > > One of queries I run is: > select c.mktsegment, sum(l.qty) > from customers c, orders o, lineitems l > where c.cust_id=o.cust_id and > o.order_id=l.order_id and > (c.mktsegment='BUILDING' or c.mktsegment='AUTOMOBILE' > or c.mktsegment='MACHINERY' or c.mktsegment='HOUSEHOLD') > and (o.priority='1-URGENT' or o.priority='2-HIGH') > group by c.mktsegment > order by c.mktsegment;> > MS SQL Server needed ca. 65 seconds to complete this query, however, > Informix needed ca. 206 seconds (with indexes) or 510 seconds > (without indexes defined on the data set), respectively to > complete the above query. > Althoug it is not generally comparable between these two installation, > since they run in diff. platforms with diff. OSs, the preliminary results > are simply weird. > > I hope that there is someone from Informix, who can give me advises > on the configuration of Informix IUS 9.20. > Maybe I have done something wrong by configuration. > > P.S. MS SQL Server uses cooked-files to store data, while Informix uses > raw devices to store data!!! > > Best Regards, > > Ming-Chuan Wu > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.