Stephane , thanks a lot ! actualy , i do not think that this is the case , because it would take relatively much more time to construct the result set (my SQL contains massive 'group by' and 'order by' and therefore the whole result set needs to be constructed before returning even the 1st row), than to skip over several records ot bring the next 5000 rows...
DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -----Original Message----- Sent: Wed, May 22, 2002 12:53 PM To: Multiple recipients of list ORACLE-L >Dear gurus ! >We are facing a severe performance problems here >that i have no idea of how >to address. >There is a C++ program , which uses OCI , that does >the following. >It initiates a long running SQL select statement, >and then fetches the >result set from the DB in buffers of 5000 records. >Now , the problem is that it takes significantly >(exponentially) more time >to fetch each subsequent result set. >I.e. , it takes a second to bring the first 5000 >records, it takes 2 >seconds to bring the next 5000 rows , it takes 3 >seconds to bring the third >set of 5000 rows and ....it takes a hour to bring >the 100th set of 5000 >rows. >My main question is : what actually happens within >the DB engine when you >execute a huge select and then fetch the results >buffer-by-buffer ? > >Thanks a lot !!! > > >DBAndrey > Hmm, I don't think that the problem is with the OCI. Normally (and that what the 'cursor' of old meant) you do at execution any processing (sorts included) which may be required to determine the result set, then move a kind of pointer forward that result set. Not much different from a succession of read() - the size of the buffer, performance questions apart, just means more or less moving a pointer. The behaviour you witness is more typical of a statement which would be executed again and again, skipping 0 lines on the first execution, 5000 on the second one, 10000 on the third one ... so as to always return 5000 lines. Wanna bet ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
