Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Pro*C, host array performance

Pro*C, host array performance

From: Graham Turner <turnergw_at_logica.RemoveSpam.com>
Date: 18 Jun 2001 15:23:29 GMT
Message-ID: <992877808.973730@ernani.logica.co.uk>

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 Received on Mon Jun 18 2001 - 10:23:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US