Informix Query takes 10 times longer than Orrible

Informix Query takes 10 times longer than Orrible

Post by Sean Kels » Fri, 31 May 2002 22:57:04



IDS 9.30.FC1 on Solaris 8

I have a table in Informix, with about 200000 rows in it. It is an
exact copy of a table in the Oracle database with exactly the same
indexes etc...

We have UPDATED STATISTICS HIGH for the table on both systems.

When we issue a query on an un-indexed column, Oracle brings back the
information almost immediately (sub second) whereas Informix takes in
excess of 30 seconds to return the same row (just a single row).

SELECT *
FROM rdl_instrument
WHERE c_in_short_name = 'CAN 1292';

I have made sure that the table is a single fragment, the indexes are
single fragments and for all intent and purposes the Informix system
is configured the same as the Oracle.

Both Informix and Oracle indicate a full table scan is being
performed.

The table has 167 columns in it
The row size is 1109 bytes

I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
respectively but this doesn't appear to solve the problem.

I have enclosed below a copy of the ONCONFIG paramters that I think
may have an impact.

Any and all help anyone can provide, very welcome.

Thanks

Sean Kelsey

==============================
ONCONFIG

BUFFERS      10000
NUMAIOVPS    2
PHYSBUFF     128
LOGBUFF      128
CLEANERS     20
SHMVIRTSIZE  1000000
SHMADD       256000
RA_PAGES     10000
RA_THRESHOLD 1000

==============================
The SQL to create the table is:-

CREATE TABLE "informix".rdl_instrument
        (
        c_in_id float,
        c_in_sedol varchar(7,0),
        c_in_short_name varchar(8,0),
        c_in_issuer varchar(18,0),
        c_in_desc varchar(27,0),
        c_in_it_code varchar(4,0),
...
...
...
        c_in_sub_lsc_code varchar(4,0),
        c_in_lot_size integer,
        c_in_usgaap_class varchar(6,0),
        c_in_exsh_usgaap_class varchar(6,0)
        )
        IN tmis_01
        EXTENT SIZE 250000 NEXT SIZE 20000
        LOCK MODE PAGE;

-- Creating index
CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
"informix".rdl_instrument
        (
        c_in_id "informix".btree_ops
        )
        USING "informix".btree
        IN tmis_01
        ENABLED;

-- Creating index
CREATE INDEX "informix".ix_rdl_instrument2 ON
"informix".rdl_instrument
        (
        c_in_sedol "informix".btree_ops,
        c_in_current_mkr "informix".btree_ops
        )
        USING "informix".btree
        IN tmis_01
        ENABLED;

 
 
 

Informix Query takes 10 times longer than Orrible

Post by Obnoxio The Clo » Sat, 01 Jun 2002 00:36:28




Quote:>IDS 9.30.FC1 on Solaris 8

>I have a table in Informix, with about 200000 rows in it. It is an
>exact copy of a table in the Oracle database with exactly the same
>indexes etc...

>We have UPDATED STATISTICS HIGH for the table on both systems.

>When we issue a query on an un-indexed column, Oracle brings back the
>information almost immediately (sub second) whereas Informix takes in
>excess of 30 seconds to return the same row (just a single row).

Well, OK, so Oracle is faster at a full table scan.

a) Why so few BUFFERS?
2) Why isn't the column indexed?
iii) Are both hardware configs the same, because this is a brute force
comparison?
* What happens when you rerun the queries on both platforms?

Quote:>SELECT *
>FROM rdl_instrument
>WHERE c_in_short_name = 'CAN 1292';

>I have made sure that the table is a single fragment, the indexes are
>single fragments and for all intent and purposes the Informix system
>is configured the same as the Oracle.

>Both Informix and Oracle indicate a full table scan is being
>performed.

>The table has 167 columns in it
>The row size is 1109 bytes

>I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
>respectively but this doesn't appear to solve the problem.

>I have enclosed below a copy of the ONCONFIG paramters that I think
>may have an impact.

>Any and all help anyone can provide, very welcome.

>Thanks

>Sean Kelsey

>==============================
>ONCONFIG

>BUFFERS      10000
>NUMAIOVPS    2
>PHYSBUFF     128
>LOGBUFF      128
>CLEANERS     20
>SHMVIRTSIZE  1000000
>SHMADD       256000
>RA_PAGES     10000
>RA_THRESHOLD 1000

>==============================
>The SQL to create the table is:-

>CREATE TABLE "informix".rdl_instrument
>    (
>    c_in_id float,
>    c_in_sedol varchar(7,0),
>    c_in_short_name varchar(8,0),
>    c_in_issuer varchar(18,0),
>    c_in_desc varchar(27,0),
>    c_in_it_code varchar(4,0),
>...
>...
>...
>    c_in_sub_lsc_code varchar(4,0),
>    c_in_lot_size integer,
>    c_in_usgaap_class varchar(6,0),
>    c_in_exsh_usgaap_class varchar(6,0)
>    )
>    IN tmis_01
>    EXTENT SIZE 250000 NEXT SIZE 20000
>    LOCK MODE PAGE;

>-- Creating index
>CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
>"informix".rdl_instrument
>    (
>    c_in_id "informix".btree_ops
>    )
>    USING "informix".btree
>    IN tmis_01
>    ENABLED;

>-- Creating index
>CREATE INDEX "informix".ix_rdl_instrument2 ON
>"informix".rdl_instrument
>    (
>    c_in_sedol "informix".btree_ops,
>    c_in_current_mkr "informix".btree_ops
>    )
>    USING "informix".btree
>    IN tmis_01
>    ENABLED;


 
 
 

Informix Query takes 10 times longer than Orrible

Post by Jack Parke » Sat, 01 Jun 2002 01:02:38


What version of Orrible?  What hardware?

A number of questions come to mind.  For starters, how long does it take to
"select count(*) from rdl_instrument where 1=1" on both systems?
RA_Pages and Threshold max out at 128.  Do you have any DS memory defined?
Are you getting light scans?  You're using AIO?  Why not KAIO?

Your table is about 893MB?  On one disk?  Orrible is reading that subsecond?
Must be some awesome disks on that box.

What is your desired outcome here?  To speed up the Informix query?  To make
it faster than the Orrible query?  To explain why one is slow and the other
is not?

cheers
j.

----- Original Message -----


Sent: Thursday, May 30, 2002 9:57 AM
Subject: Informix Query takes 10 times longer than Orrible

> IDS 9.30.FC1 on Solaris 8

> I have a table in Informix, with about 200000 rows in it. It is an
> exact copy of a table in the Oracle database with exactly the same
> indexes etc...

> We have UPDATED STATISTICS HIGH for the table on both systems.

> When we issue a query on an un-indexed column, Oracle brings back the
> information almost immediately (sub second) whereas Informix takes in
> excess of 30 seconds to return the same row (just a single row).

> SELECT *
> FROM rdl_instrument
> WHERE c_in_short_name = 'CAN 1292';

> I have made sure that the table is a single fragment, the indexes are
> single fragments and for all intent and purposes the Informix system
> is configured the same as the Oracle.

> Both Informix and Oracle indicate a full table scan is being
> performed.

> The table has 167 columns in it
> The row size is 1109 bytes

> I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
> respectively but this doesn't appear to solve the problem.

> I have enclosed below a copy of the ONCONFIG paramters that I think
> may have an impact.

> Any and all help anyone can provide, very welcome.

> Thanks

> Sean Kelsey

> ==============================
> ONCONFIG

> BUFFERS      10000
> NUMAIOVPS    2
> PHYSBUFF     128
> LOGBUFF      128
> CLEANERS     20
> SHMVIRTSIZE  1000000
> SHMADD       256000
> RA_PAGES     10000
> RA_THRESHOLD 1000

> ==============================
> The SQL to create the table is:-

> CREATE TABLE "informix".rdl_instrument
> (
> c_in_id float,
> c_in_sedol varchar(7,0),
> c_in_short_name varchar(8,0),
> c_in_issuer varchar(18,0),
> c_in_desc varchar(27,0),
> c_in_it_code varchar(4,0),
> ...
> ...
> ...
> c_in_sub_lsc_code varchar(4,0),
> c_in_lot_size integer,
> c_in_usgaap_class varchar(6,0),
> c_in_exsh_usgaap_class varchar(6,0)
> )
> IN tmis_01
> EXTENT SIZE 250000 NEXT SIZE 20000
> LOCK MODE PAGE;

> -- Creating index
> CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
> "informix".rdl_instrument
> (
> c_in_id "informix".btree_ops
> )
> USING "informix".btree
> IN tmis_01
> ENABLED;

> -- Creating index
> CREATE INDEX "informix".ix_rdl_instrument2 ON
> "informix".rdl_instrument
> (
> c_in_sedol "informix".btree_ops,
> c_in_current_mkr "informix".btree_ops
> )
> USING "informix".btree
> IN tmis_01
> ENABLED;

 
 
 

Informix Query takes 10 times longer than Orrible

Post by Paul Watso » Sat, 01 Jun 2002 01:42:36



> What version of Orrible?  What hardware?

> A number of questions come to mind.  For starters, how long does it take to
> "select count(*) from rdl_instrument where 1=1" on both systems?
> RA_Pages and Threshold max out at 128.  Do you have any DS memory defined?
> Are you getting light scans?  You're using AIO?  Why not KAIO?

> Your table is about 893MB?  On one disk?  Orrible is reading that subsecond?
> Must be some awesome disks on that box.

Ah, the joys of FS caching.  

> What is your desired outcome here?  To speed up the Informix query?  To make
> it faster than the Orrible query?  To explain why one is slow and the other
> is not?

> cheers
> j.

> ----- Original Message -----


> Sent: Thursday, May 30, 2002 9:57 AM
> Subject: Informix Query takes 10 times longer than Orrible

> > IDS 9.30.FC1 on Solaris 8

> > I have a table in Informix, with about 200000 rows in it. It is an
> > exact copy of a table in the Oracle database with exactly the same
> > indexes etc...

> > We have UPDATED STATISTICS HIGH for the table on both systems.

> > When we issue a query on an un-indexed column, Oracle brings back the
> > information almost immediately (sub second) whereas Informix takes in
> > excess of 30 seconds to return the same row (just a single row).

> > SELECT *
> > FROM rdl_instrument
> > WHERE c_in_short_name = 'CAN 1292';

> > I have made sure that the table is a single fragment, the indexes are
> > single fragments and for all intent and purposes the Informix system
> > is configured the same as the Oracle.

> > Both Informix and Oracle indicate a full table scan is being
> > performed.

> > The table has 167 columns in it
> > The row size is 1109 bytes

> > I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
> > respectively but this doesn't appear to solve the problem.

> > I have enclosed below a copy of the ONCONFIG paramters that I think
> > may have an impact.

> > Any and all help anyone can provide, very welcome.

> > Thanks

> > Sean Kelsey

> > ==============================
> > ONCONFIG

> > BUFFERS      10000
> > NUMAIOVPS    2
> > PHYSBUFF     128
> > LOGBUFF      128
> > CLEANERS     20
> > SHMVIRTSIZE  1000000
> > SHMADD       256000
> > RA_PAGES     10000
> > RA_THRESHOLD 1000

> > ==============================
> > The SQL to create the table is:-

> > CREATE TABLE "informix".rdl_instrument
> > (
> > c_in_id float,
> > c_in_sedol varchar(7,0),
> > c_in_short_name varchar(8,0),
> > c_in_issuer varchar(18,0),
> > c_in_desc varchar(27,0),
> > c_in_it_code varchar(4,0),
> > ...
> > ...
> > ...
> > c_in_sub_lsc_code varchar(4,0),
> > c_in_lot_size integer,
> > c_in_usgaap_class varchar(6,0),
> > c_in_exsh_usgaap_class varchar(6,0)
> > )
> > IN tmis_01
> > EXTENT SIZE 250000 NEXT SIZE 20000
> > LOCK MODE PAGE;

> > -- Creating index
> > CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
> > "informix".rdl_instrument
> > (
> > c_in_id "informix".btree_ops
> > )
> > USING "informix".btree
> > IN tmis_01
> > ENABLED;

> > -- Creating index
> > CREATE INDEX "informix".ix_rdl_instrument2 ON
> > "informix".rdl_instrument
> > (
> > c_in_sedol "informix".btree_ops,
> > c_in_current_mkr "informix".btree_ops
> > )
> > USING "informix".btree
> > IN tmis_01
> > ENABLED;

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
www.oninit.com          #
 
 
 

Informix Query takes 10 times longer than Orrible

Post by John Carlso » Sat, 01 Jun 2002 01:52:59


See comments below.

-----Original Message-----

Sent: Thursday, May 30, 2002 9:57 AM

Subject: Informix Query takes 10 times longer than Orrible

IDS 9.30.FC1 on Solaris 8

I have a table in Informix, with about 200000 rows in it. It is an
exact copy of a table in the Oracle database with exactly the same
indexes etc...

JC>  On the same hardware platform with the same OS installed on each
server?

We have UPDATED STATISTICS HIGH for the table on both systems.

When we issue a query on an un-indexed column, Oracle brings back the
information almost immediately (sub second) whereas Informix takes in
excess of 30 seconds to return the same row (just a single row).

SELECT *
FROM rdl_instrument
WHERE c_in_short_name = 'CAN 1292';

JC>  I'm not sure how Informix handles varchars.  I prefer chars in this
case; it may eliminate any chaining depending on total row size

I have made sure that the table is a single fragment, the indexes are
single fragments and for all intent and purposes the Informix system
is configured the same as the Oracle.

JC>  Hardware, etc?

Both Informix and Oracle indicate a full table scan is being
performed.

JC>  Maybe Orrible is better at table scans?

The table has 167 columns in it
The row size is 1109 bytes

I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
respectively but this doesn't appear to solve the problem.

JC>  Why so high?

I have enclosed below a copy of the ONCONFIG paramters that I think
may have an impact.

Any and all help anyone can provide, very welcome.

Thanks

Sean Kelsey

==============================
ONCONFIG

BUFFERS      10000
NUMAIOVPS    2
PHYSBUFF     128
LOGBUFF      128
CLEANERS     20
SHMVIRTSIZE  1000000
SHMADD       256000
RA_PAGES     10000
RA_THRESHOLD 1000

JC>  Why are the RA* variables so high?

==============================
The SQL to create the table is:-

JC>  . . . imcomplete.

JC>  . . .  partial table schema snipped . . .

"CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
Retail. This email message and all attachments may contain legally
privileged and confidential information intended solely for the use of the
addressee. If you are not the intended recipient, you should immediately
stop reading this message and delete it from the system. Any unauthorized
reading, distribution, copying, or other use of this message or its
attachments is strictly prohibited. All personal messages express solely the
sender's views and not those of WHSmith USA Travel Retail. This message may
not be copied or distributed without this disclaimer."

 
 
 

Informix Query takes 10 times longer than Orrible

Post by Murray Woo » Sat, 01 Jun 2002 07:06:04


Raw devices?  KAIO?

A huge table and only 10000 BUFFERS???  You want hundreds of thousands of
BUFFERS.  With RA_PAGES so high, what BUFFERS are you going to read ahead
into?  Put RA at 64/48 or maybe 128/?? but not higher.  Not sure what
Informix would do with these parameters but it could be clearing out
(overwriting) your BUFFERS before you axctually get to use them.

What do you need to set SHMVIRTSIZE to?

MW

-----Original Message-----


Sent: Friday, 31 May 2002 1:57 a.m.

Subject: Informix Query takes 10 times longer than Orrible

IDS 9.30.FC1 on Solaris 8

I have a table in Informix, with about 200000 rows in it. It is an
exact copy of a table in the Oracle database with exactly the same
indexes etc...

We have UPDATED STATISTICS HIGH for the table on both systems.

When we issue a query on an un-indexed column, Oracle brings back the
information almost immediately (sub second) whereas Informix takes in
excess of 30 seconds to return the same row (just a single row).

SELECT *
FROM rdl_instrument
WHERE c_in_short_name = 'CAN 1292';

I have made sure that the table is a single fragment, the indexes are
single fragments and for all intent and purposes the Informix system
is configured the same as the Oracle.

Both Informix and Oracle indicate a full table scan is being
performed.

The table has 167 columns in it
The row size is 1109 bytes

I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
respectively but this doesn't appear to solve the problem.

I have enclosed below a copy of the ONCONFIG paramters that I think
may have an impact.

Any and all help anyone can provide, very welcome.

Thanks

Sean Kelsey

==============================
ONCONFIG

BUFFERS      10000
NUMAIOVPS    2
PHYSBUFF     128
LOGBUFF      128
CLEANERS     20
SHMVIRTSIZE  1000000
SHMADD       256000
RA_PAGES     10000
RA_THRESHOLD 1000

==============================
The SQL to create the table is:-

CREATE TABLE "informix".rdl_instrument
        (
        c_in_id float,
        c_in_sedol varchar(7,0),
        c_in_short_name varchar(8,0),
        c_in_issuer varchar(18,0),
        c_in_desc varchar(27,0),
        c_in_it_code varchar(4,0),
...
...
...
        c_in_sub_lsc_code varchar(4,0),
        c_in_lot_size integer,
        c_in_usgaap_class varchar(6,0),
        c_in_exsh_usgaap_class varchar(6,0)
        )
        IN tmis_01
        EXTENT SIZE 250000 NEXT SIZE 20000
        LOCK MODE PAGE;

-- Creating index
CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
"informix".rdl_instrument
        (
        c_in_id "informix".btree_ops
        )
        USING "informix".btree
        IN tmis_01
        ENABLED;

-- Creating index
CREATE INDEX "informix".ix_rdl_instrument2 ON
"informix".rdl_instrument
        (
        c_in_sedol "informix".btree_ops,
        c_in_current_mkr "informix".btree_ops
        )
        USING "informix".btree
        IN tmis_01
        ENABLED;

 
 
 

Informix Query takes 10 times longer than Orrible

Post by Art S. Kage » Sat, 01 Jun 2002 22:12:34


OK here's what's going on.  You rowsize is 1109 bytes which means that
unless you are running on AIX or WinDoze your page size is 2K which
holds only 2024 bytes per row so you are reading only 1 row per page!
Therefore to prevent thrashing the buffer cache you would need at least
200,000 buffers to hold all of those data pages!  But you say, my Oracle
is configured with about the same cache!  Ya and I bet your Oracle
instance is using COOKED files and your system Filesystem buffer cache
is more like 1GB, which Orable is piggy backing on!  Increase BUFFERS to
300,000 or more!  Also just running HIGH on the table itself is not
enough.  For best results follow the guideline in the Performance Guide
or use my dostats utility!  Do you have KAIO enabled on your system?  If
not you will need many more AIO VPs (I'd recommend 6 rather than 2 even
if you do use KAIO and one per chunk + 4->6 if no KAIO).  Reduce the RA
parameters to 8 and 16 (or 16 & 32 or 64 if you have singleton drives
rather than faster arrays) that's enough.  Also as someone else pointed
out with all those VARCHAR columns, while you MAY be getting two or
three row per page for smaller rows you risk rows being forwarded
because they no longer fit if a row can grow over time (like if you
insert them with empty strings and then update the strings later (even
immediately afterwards!)  Forward pointers mean that each forwarded row
will require two or more IOs instead of one and during a table scan
encountering a forwarded row will cause the engine to fetch the
forwarding page out-of-sequence stoping the sequential reads if it is
not already in cache!  Normally not a big problem with a too small cache
you are thrashing those pages out-of-cache if they are needed again in
order (of if they were already fetched and are needed by a forward
again).  Ten times slower?  I'm surprised it's not 100 times slower!

So, summary:  More BUFFERS, aio resources if appropriate, reasonable RA
settings, dump the VARCHARS for CHAR columns.  Then I predict Informix
runs the query 12% faster than Orable the first time maybe 15% faster
once both caches are loaded (don't forget that it may take a system boot
to clear the system buffers of Orables COOKED pages!).

Art S. Kagel


> IDS 9.30.FC1 on Solaris 8

> I have a table in Informix, with about 200000 rows in it. It is an
> exact copy of a table in the Oracle database with exactly the same
> indexes etc...

> We have UPDATED STATISTICS HIGH for the table on both systems.

> When we issue a query on an un-indexed column, Oracle brings back the
> information almost immediately (sub second) whereas Informix takes in
> excess of 30 seconds to return the same row (just a single row).

> SELECT *
> FROM rdl_instrument
> WHERE c_in_short_name = 'CAN 1292';

> I have made sure that the table is a single fragment, the indexes are
> single fragments and for all intent and purposes the Informix system
> is configured the same as the Oracle.

> Both Informix and Oracle indicate a full table scan is being
> performed.

> The table has 167 columns in it
> The row size is 1109 bytes

> I have modified the RA_PAGES and RA_THRESHOLD to 10000 & 500
> respectively but this doesn't appear to solve the problem.

> I have enclosed below a copy of the ONCONFIG paramters that I think
> may have an impact.

> Any and all help anyone can provide, very welcome.

> Thanks

> Sean Kelsey

> ==============================
> ONCONFIG

> BUFFERS      10000
> NUMAIOVPS    2
> PHYSBUFF     128
> LOGBUFF      128
> CLEANERS     20
> SHMVIRTSIZE  1000000
> SHMADD       256000
> RA_PAGES     10000
> RA_THRESHOLD 1000

> ==============================
> The SQL to create the table is:-

> CREATE TABLE "informix".rdl_instrument
>    (
>    c_in_id float,
>    c_in_sedol varchar(7,0),
>    c_in_short_name varchar(8,0),
>    c_in_issuer varchar(18,0),
>    c_in_desc varchar(27,0),
>    c_in_it_code varchar(4,0),
> ...
> ...
> ...
>    c_in_sub_lsc_code varchar(4,0),
>    c_in_lot_size integer,
>    c_in_usgaap_class varchar(6,0),
>    c_in_exsh_usgaap_class varchar(6,0)
>    )
>    IN tmis_01
>    EXTENT SIZE 250000 NEXT SIZE 20000
>    LOCK MODE PAGE;

> -- Creating index
> CREATE DISTINCT INDEX "informix".ix_rdl_instrument1 ON
> "informix".rdl_instrument
>    (
>    c_in_id "informix".btree_ops
>    )
>    USING "informix".btree
>    IN tmis_01
>    ENABLED;

> -- Creating index
> CREATE INDEX "informix".ix_rdl_instrument2 ON
> "informix".rdl_instrument
>    (
>    c_in_sedol "informix".btree_ops,
>    c_in_current_mkr "informix".btree_ops
>    )
>    USING "informix".btree
>    IN tmis_01
>    ENABLED;

 
 
 

1. Procedure takes 10 times longer to process in SQL 7 vs 6.5

I just finished reading BOL, Transact-SQL Tips - talks all about temp tables
and dynamic execs. I don't know if this will help or hurt :-(

--
Jackie
(Please reply to the newsgroup)
******************************************************
Jackie Brophy        Tel: 508-647-7243
Business Systems Group    Fax:  508-647-7015

3 Apple Hill Dr.          http://www.mathworks.com
Natick, MA 01760-2098
*******************************************************

2. DB2/390: SMF not accessible

3. sql query taking a long time

4. Missing Net8 Assistant

5. Query Taking very long time in Production

6. Problem in importing data from Progress database to MS-SQL Server 7 database

7. DistributedRDBMS a summary (long)

8. Query takes a long time

9. Select query taking extremely long time

10. SQLXML query takes a long time to run

11. Query taking a long time...

12. Query Taking a Long Time