Speed problem in Pro*C with dynamic SQL update

Hi Guys,

     I am having a problem with Oracle Pro*C and I cannot see why,
     as you can see I have tried a few things and got nowhere useful.

I am running on a HP-UX B.11.00 B 9000/800, and using Oracle 8.1.6 database.

I had this problem in "real" code but I have created a dummy table and examples code to show this problem

SQL> desc emp;

Name                                      Null?    Type
----------------------------------------- -------- -------------------------
---
EMPNO                                              NUMBER
ENAME                                              VARCHAR2(15)
DEPTNO                                             NUMBER

Example code:

EXEC SQL INCLUDE SQLCA; int main (int argc, char *argv[])
{

    char *username = "scott/tiger";
    char *sql_stmt = "update emp set empno = 1 where empno = 1";

    int loop;

    EXEC SQL CONNECT :username;

    for (loop=0; loop != 20; loop++)
    {

        EXEC SQL EXECUTE IMMEDIATE :sql_stmt;

        printf ("%d\n", sqlca.sqlcode);
    }

    EXEC SQL COMMIT RELEASE;     return (0);
}

If I execute the code with NO rows in the database, or where the where condition will not be met it takes it takes:

real       17.97
user        9.62
sys         1.81

That's nearly 20 seconds for 20 iupdates. (When this is run in production with 1000's of updates!!!)
When the program is run you can see it pause as it does every EXECUTE IMMEDIATE statement.
(Proving where the delay is occurring)

If the where condition can be met it takes

real        0.98
user        0.68
sys         0.11

Less than 1 second.

So its not the parsing of the statement that takes the time, as that would be the same in both cases.
And it isn't spending the time scanning the table, because it can be empty.

It is purely dependant on whether on not the condition is met.

I put 20 updates in a file and tried it in SQL*Plus

$ sqlplus scott/tiger

SQL*Plus: Release 8.1.6.0.0 - Production on Wed Feb 28 09:00:28 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> _at_x

And SQL*Plus did the updates (if the row exists or not) in under a second.

(So its not a "big" problem oracle has with updating rows that don't exist)

I tried it as just embedded SQL rather than Dynamic SQL Method 1 (Which I cannot do in the real application)

EXEC SQL INCLUDE SQLCA; int main (int argc, char *argv[])
{

    char *username = "scott/tiger";
    char *sql_stmt = "update emp set empno = 1 where empno = 1";

    int loop;

    EXEC SQL CONNECT :username;

    for (loop=0; loop != 20; loop++)
    {

        EXEC SQL update emp set empno = 1 where empno = 1;

        printf ("%d\n", sqlca.sqlcode);
    }

    EXEC SQL COMMIT RELEASE;     return (0);
}

And, again, it ran in under a second (if the row exists or not):

real        0.95
user        0.19
sys         0.03

Now more weird stuff

If I change the Dynamic SQL Method 1 SQL into PL/SQL as in:

EXEC SQL INCLUDE SQLCA; int main (int argc, char *argv[])
{

    char *username = "scott/tiger";
    char *sql_stmt = "begin update emp set empno = 1 where empno = 1; end;";

    int loop;

    EXEC SQL CONNECT :username;

    for (loop=0; loop != 20; loop++)
    {

        EXEC SQL EXECUTE IMMEDIATE :sql_stmt;

        printf ("%d\n", sqlca.sqlcode);
    }

    EXEC SQL COMMIT RELEASE;     return (0);
}

Now it runs in about a second (If the row exists or not)

real        0.98
user        0.18
sys         0.05

But the problem here is I cannot tell if the update worked or not, the sqlca.sqlcode is if it executed the pl/sql block OK, which it did, not if the update worked or not.

In the real application it would attempt to update a row (that could be there) and if it isn't then insert one.
So I would have a problem with this "solution".

Any ideas why this is happening, and how to fix it?

Thanks,

     Gary. Received on Wed Feb 28 2001 - 20:24:01 CET