Re: Performance comparison btn Informix and SQL Server

Further to my own post... From: "Obnoxio The Clown" <obnoxio@hotmail.com> > >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 1. What does SET EXPLAIN show? 2. SQL Server cunningly caches result sets. So if you run the same query twice, it's blindingly fast. The down side is if your next query is just slightly different, that result set is useless. 3. Perhaps if Informix paid more attention to performance benchmarks, this sort of thing might not happen? Aside: Do any of you have account reps at Informix? If so, perhaps if we *all* moaned at them about their lack of current TPC numbers, they might do something. But it would have to be in large numbers. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com