Re: Performance comparison btn Informix and SQL Server

From: Ming-Chuan Wu <wu@dvs1.informatik.tu-darmstadt.de> > >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!!! OK. First thing is to run UPDATE STATISTICS on the Informix server. If that doesn't work, run onstat -z then run your query again. Then send us the output from: onstat - onstat -c onstat -m onstat -l onstat -p onstat -P onstat -g iov onstat -d onstat -D onstat -R onstat -F ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com