the pinned procedure would not be faster than the lone insert statement using bind variables and multiple executes. on the sql end they both do the same thing. static sql or dynamic sql with bind variables in the pl/sql block is hashed and reused as well.
what possibly makes you think having pl/sql do inserts, could be faster than sql doing inserts? the procedure pinned or not just adds additional overhead I believe. it still passes the sql inside the pl/sql block off to the sql engine to be either reparsed or found in the sqlarea. if there is lots of sql to be passed off, it can be done in one trip at least, but with a sole insert this isn't the case. pinning a procedure that exclusively does single inserts is probably not the way to go from a db stand point. someone asked something similar about sql selects from java vs pl/sql calls from java that do the select (sound familiar??) I assume it applies to inserts as well. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:499097979114 The response is at the above link. the question goes like this: QUESTION IS: I am getting better performance with the java code calling the SQL than the one calling the stored proc. Why is that ? The "select" statement in both is the same. I expect the PL/SQL SP one to perform better. Answer goes like: #1. PL/SQL that just opens a ref cursor and returns it and does nothing could not possibly be faster then just SQL. I would expect them to be about the same. PL/SQL cannot magically make SQL go faster, you have not reduced the amount of data on the network, there are no "time based" efficiencies to be gained this way. The advantages of the stored procedure are in the management and flexibility of it all. There is absolutely no change that PLSQL executing your SQL can cause a select to return data faster (or slower) You should use the stored procedure, it is just a better programming technique, is more secure and protects you from changes in the long run ..... hope this column helps. tom kyte is great. Job __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
