Re: Pro*C, host array performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jun 2001 11:24:01 -0700
Message-ID: <9glh0102ot0@drn.newsguy.com>

In article <992877808.973730_at_ernani.logica.co.uk>, turnergw_at_logica.RemoveSpam.com says...
>
>I seem to have a major performance problem when using host arrays to read in
>data, using Oracle 8.1.6.
>
>I have a table of approximately 300,000 rows - It's a very simple table,
>comprising a single unique ID and four other columns - two integers, two dates.
>Doing a 'select * from' this table in Sqlplus outputs all the rows in about 40
>seconds. Using 'exp' to export the table as a binary chunk takes about 10
>seconds - and yet, in Pro*C, using a simple cursor and host arrays to read the
>data in blocks of 1000 rows, it takes 15 minutes to get all the rows! A test
>routine to insert the same set of rows, also using host arrays, takes only
>about 2 minutes!
>
>I can see no reason for the massive discrepancy between selecting the
>data in sqlplus, and in pro*c. There is no other complicated processing going
>on, the program simply reads the rows and copies them into another structure.
>Adding diagnostics to the program shows that the time taken is almost entirely
>used by the Oracle reads, not the extra processing.
>
>Has anyone else come across a similar problem? Is there a better way of doing
>this? I simply need to read the whole table into memory, but it will have to
>be a lot quicker than this.
>
>Any advice greatly appreciated!
>
>Graham
>
>

sounds like you might not be using null indicators. I loaded up:

create table t

( id int,
  c1 int,
  c2 int,

  c3 date,
  c4 date
)
/

begin

    for i in 1 .. 300000
    loop

        insert into t values ( i, i, i, sysdate+i, sysdate+i );     end loop;
end;
/
commit;

and then ran:

static void process()
{
exec sql begin declare section;

int id[100];
short id_i[100];
int c1[100];
short c1_i[100];
int c2[100];
short c2_i[100];
varchar c3[100][25];
short c3_i[100];
varchar c4[100][25];
short c4_i[100];

exec sql end declare section;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    exec sql declare c1 cursor for
    select id, c1, c2, c3, c4 from t;

    exec sql open c1;
    while(1)
    {

      /* SQL FETCH C1 into :id:id_i, :c1:c1_i, :c2:c2_i, :c3:c3_i, :c4:c4_i; */
        EXEC SQL FETCH C1 into :id, :c1, :c2, :c3, :c4;
        if ( sqlca.sqlcode > 0 ) break;

    }
    printf( "sqlca.sqlerrd[2] = %d\n", sqlca.sqlerrd[2] );     EXEC SQL CLOSE C1;
}

when I did not use indicators (when I used the code as above) it ran about 4.5-6 times slower then when using indicators (even if the columns were not NULL)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 18 2001 - 13:24:01 CDT