Pro*C Performance Problem

From: Paul Masi <pmasi_at_carlee.llnl.gov>
Date: 28 Apr 1994 17:04:04 GMT
Message-ID: <2poqa4$2sl_at_lll-winken.llnl.gov>

I am having performance problems using Pro*C 1.5 and Oracle7.

Below is a Pro*C pgm that shows the problem. It consists of a single cursor that returns one column from a table (not a view). Its performance varies greatly depending on which column is returned and what the local host variable datatype is. Some tests I've run and the approximate clock time it takes them to run.

Pgm call Oracle column returned Host datatype Clock Time

--------  ----------------------   -------------    ----------
bug7 1 30 <ora_number_col NUMBER(7)   long             5 secs
bug7 1 30 <ora_date_col>  DATE        long             5 secs
bug7 1 30 <ora_date_col>  DATE        char (VARCHAR)   40 secs

bug7 1 30 <ora_vchr_col> VARCAR2(80) char (VARCHAR) 40 secs

Anyone know what is happenning here and what I can do to resolve it? Please send email and I will post a summary.

Thanks,

--
Paul Masi
Lawrence Livermore National Laboratory
Internet:  pmasi_at_llnl.gov
Phone:     (510) 422-4501

/***** begin pgm *****/


#include <stdio.h>


#include <string.h>





#define DEBUG          0



EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR uid[20],
	    host_date_char[10],
	    host_vchr2[80]; 

 long   host_long; 
 long   host_date_char;

 long    prime99_account,
	 prime00_account;

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE gl_accounts CURSOR FOR
	SELECT  /* <ora_number_col> */
                /* <ora_varchar2(80)_col **/
                /* to_char(<ora_date_col>) **/
                <ora_date_col> 
		FROM    <Table>
		WHERE   <indexed_number_col> between :prime00_account
		                        and :prime99_account ;

EXEC SQL INCLUDE SQLCA.H;

main(argc, argv)
     int argc;
     char *argv[];
{
     int lo_arg, hi_arg, prm_acct;

	lo_arg = atoi(argv[1]);
	hi_arg = atoi(argv[2]);

	strcpy(uid.arr,"/_at_xxDBxxded);
	uid.len=strlen(uid.arr);
					/* Connect to Oracle */
	EXEC SQL CONNECT :uid ;
	if (sqlca.sqlcode)
           {
	   fprintf(stderr,"sqlcode=%ld\n", sqlca.sqlcode);
           fprintf(stderr,"Error, cannot connect to Oracle.\n");
	   exit(-1);
	   }

	for (prm_acct=lo_arg; prm_acct <= hi_arg; prm_acct++)
	{
		prime99_account = prm_acct*100+99;
		prime00_account = prm_acct*100;

        	EXEC SQL OPEN gl_accounts;
		do
	   	{  
           	EXEC SQL FETCH gl_accounts INTO
				:host_date_char;
 /***
				:host_date_long;
                      :host_vchr2;
				:host_long;
			       ***/

/**
		host_vchr2.arr[host_vchr2.len] = '\0';
		printf("host_vchr2 = %s\n", host_vchr2.arr);
**/
		} while (sqlca.sqlcode == 0);  

        	EXEC SQL CLOSE gl_accounts;
	}
}
/***** end pgm ****/

Precompile:
(Pro*C: Release 1.5.9.0.1 - Production on Thu Apr 28 08:42:47 1994)

/oracle/prod7/bin/proc ireclen=132 oreclen=132 select_error=no dbms=v7 hold_cursor=yes mode=oracle iname=bug7.pc

Compile & Link:
cc -Bdynamic -L/oracle/prod7/lib -o bug7 bug7.c /oracle/prod7/lib/libsql.a /oracle/prod7/lib/osntab.o -lsqlnet -lora /oracle/prod7/lib/libpls.a -lsqlnet -lnlsrtl -lcv6 -lcore -lnlsrtl -lcv6 -lcore -lm

Received on Thu Apr 28 1994 - 19:04:04 CEST