informix + oracle 
Author Message
 informix + oracle

Hi Informix Gurus,

I am comparing a query against Oracle.
This is a table which has 2,740,000 rows (row size:649 for both oracle and informix 9.21.)I have no indexes so sequental scan is the only way.
I have done this tests on windows 2000 and sun solaris 2.8

I have two simple queries:

1. select * from hplandet where borc1<0.
   There is no record like this.
   In oracle it takes 6.11 min with 5mb.read per second.
   In informix it takes 1:10 min with 21mb.per second. :))
    (my read ahead is well:512-64 )

2.select sube
  sum(borc1+borc2+........+borc12) ,
  sum(alac1+alac2+........+alac12),
  sum(borc1+borc2+........+borc12) -
  sum(alac1+alac2+........+alac12)
  from hplandet group by sube ,order by sube

   In oracle this takes 6:39 min with 5mb.read per seond.
   In informix it takes 6:28 min with 5mb.per seond. :(((
   What did happend to my nice 21mb.per second read?
   Why did it decrease?

Does the performance of the query decrease if i have
more select fields:

select sube
sum(borc1+borc2+........+borc12) ,
from hplandet group by sube ,order by sube

is faster than

select sube
sum(borc1+borc2+........+borc12) ,
sum(alac1+alac2+........+alac12),
  sum(borc1+borc2+........+borc12) -
  sum(alac1+alac2+........+alac12)
from hplandet group by sube ,order by sube

Each addition of sums makes the query run
slower.(degrades ioreads per second also)

I will be appreciated if someone explain me what i am doing wrong.

Kind Regards,
hope

==================================
Poster's IP address: 212.253.3.50
Posted via http://www.***.com/
Linux Programmer's Site



Sat, 12 Jun 2004 00:34:43 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. 22159-FL-Tampa-Informix-ORACLE-DBA Skills-Informix-HP-9000-Performance Tuning-Informix, Oracle

2. SQL differences:Informix, Oracle, MS-SQL7, ...!

3. JDBC drivers for Informix, Oracle

4. Informix, Oracle or Sybase ?

5. US-IL-Naperville System Engineer, Solaris, Unix, Informix, Oracle (1297-1998)

6. US-IL-Naperville System Engineer, Solaris, Unix, Informix, Oracle (1297-1998)

7. US-IL-Naperville System Engineer, Solaris, Unix, Informix, Oracle (1297-1998)

8. US-IL-Naperville System Engineer, Solaris, Unix, Informix, Oracle (1297-1998)

9. US-IL-Naperville System Engineer, Solaris, Unix, Informix, Oracle (1297-1998)

10. Massachusetts - UNIX / Informix / Oracle DBA

11. Massachusetts - UNIX / Informix / Oracle DBA

12. Massachusetts - UNIX / Informix / Oracle DBA


 
Powered by phpBB® Forum Software