Re: PRO*C/C++ performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/02
Message-ID: <3394ef5f.10059534@newshost>#1/1

Are you using "sqlcheck=semantics" or including the "userid=user/pass" when you don't need to? You only need these options when invoking embedded pl/sql blocks (and even then, I have a method for avoiding that). The sqlcheck=semantics really slows things down.

If you need to call pl/sql AND you don't have pl/sql tables to pass AND you don't want to do sqlcheck=semantics then instead of coding:

exec sql execute
begin

       :n := foobar( :y );
   end;
   end-exec;

(which requires sqlcheck=semantics) you can code:

   strcpy( stmt.arr, "begin :n := foobar(:y); end;" );    stmt.len = strlen(stmt.arr);
   EXEC SQL PREPARE S FROM :stmt;
   EXEC SQL DECLARE C CURSOR FOR S;
   EXEC SQL OPEN S USING :n, :y;

Which won't. The only draw back is that you can't bind pl/sql tables dynamically. If you are going to call the above block over and over again, you might consider coding:

if ( first_time )
{

   	strcpy( stmt.arr, "begin :n := foobar(:y); end;" );
   	stmt.len = strlen(stmt.arr);
   	EXEC SQL PREPARE S FROM :stmt;
   	EXEC SQL DECLARE C CURSOR FOR S;

    first_time = 0;
   }
   EXEC SQL OPEN S USING :n, :y;

which will speed up runtime processing as well by reusing the parsed block over and over again.

On 2 Jun 1997 15:38:23 GMT, "Lambert Caljouw" <Lambert.Caljouw_at_boekhuis.nl> wrote:

>Got exactly the same problem in an AIX environment (minutes to precompile,
>seconds to compile).
>
>Stay sharp, Lambert
>
>> the precompilation takes a lot of time - between 2 to 6 minutes for
>> source with less than 4000 lines.
>> To put it in proportion - the compilation takes 5 seconds.
>> Did someone have a similar problem ?
>>
>> Thanks, Oren.
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 02 1997 - 00:00:00 CDT