Re: Performance comparison btn Informix and SQL Server
Execute UPDATE STATISTICS for the tables that are used in your query.
For example:
UPDATE STATISTICS HIGH FOR TABLE customers;
UPDATE STATISTICS HIGH FOR TABLE orders;
UPDATE STATISTICS HIGH FOR TABLE lineitems;
You might also want to re-state your query to the following:
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 in
('BUILDING','AUTOMOBILE','MACHINERY','HOUSEHOLD')
and o.priority in ('1-URGENT', '2-HIGH')
group by c.mktsegment
order by c.mktsegment;
Do you have an index in column customers.mktsegment? If you don't place
an index on it.
Can you provide me the query path of the query by running SET EXPLAIN ON
before executing
the query.
Don
Ming-Chuan Wu 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