From daemon Wed Dec 27 08:50:51 1995 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65v3.0/1.1.8.2/01Aug94-0142PM) id AA31417; Wed, 27 Dec 1995 08:50:43 -0500 Message-Id: <9512271350.AA31417@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by ccvm.sunysb.edu (IBM VM SMTP V2R3) with BSMTP id 1479; Wed, 27 Dec 95 08:27:07 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 9403; Wed, 27 Dec 1995 08:27:03 -0500 Date: Wed, 27 Dec 1995 19:00:30 -0500 Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: "PL. Rukmani" Subject: Re: Pro*C problems X-To: ORACLE-L@ccvm.sunysb.edu To: Multiple recipients of list ORACLE-L In-Reply-To: from "Sridhar Panatulla" at Dec 27, 95 04:31:10 pm > > Dear Oraclites > Here is a problem encountered by one of my friends while using > Pro *C > The ORACLE version being used is V7.0.13.1.0 > Pro*C version 1.5.7.0.1 > PL/SQL version 2.0.15.1.0 > > PROBLEM: > 'X' rows to be retrieved from table1. 'X' rows to be retrieved from table2. > 'X' rows to be retrieved from table3 ( Master-Detail Query ). > > A. Which of the following options will give better performance in terms of > execution time,error handling and dynamic SQL methods.In addition to these, > any other option exist for better performance?. > > Note 'X' is greater 5000. > > Option 1 > Declaring three cursors and using FOR loops 'X' times. > > Option 2 > Using host arrays of dimension 'X' for each column and > fetching in a batch. > Option 2 is always better than Option 1. Even if there is any restriction in the array size, arrays of the maximum allowed size can be repeatedly used till all the records are fetched. Since it is a master-detail query, you can consider using a single SQL statement that joins the tables and use host array for the fetch. > Option 3 > Using a PL/SQL block inside Pro*C program. This option > can be split in two ways: > a. Declaring a PL/SQL table for each column and equating > with host array. > b. Declaring three cursors and using FOR loops for 'X' times. > No gain in using a PL/SQL block. PL/SQL blocks are mainly used to combine a set of SQL statements and pass it on to the server in one pass thereby reducing traffic. Since your requirements are only simple queries, PL/SQL will not help much I guess. > Option 4 > > Calling a PL/SQL stored procedure from Pro*C and retrieving all the > column values using PL/SQL tables as parameters of the stored procedure. > Using a stored procedure also does not reduce the work load in this situation and might actually add some more overheads. > B. Is there any limitation in the dimension of the host array while > passing it to a PL/SQL stored procedure? > > I don't have any knowledge of the limitation of host array while passing it to a PL/SQL procedure. But an Oracle Bulletin reports the maximum dimension of host array allowed in any host program is 32,767. Best of Luck Sridhar, Rukmani.