After reading all the comments I modified the test to turn auto commit off and commited once after the loop is over (not really necessary since orcale will do an implicit commit for me once I disconnect). Since one of our perl profiling tool shows most of the ptocessing time was spent in the DBI::commit procedure instead of any of the DBD methods I even wrote a stored procedure to only do commit and call that from the perl code. Unfortunately, the processing time was exactly the same for all these variations.
BTW in the real app, all the other sql statement that we have control over are using bind variables. Thanks for all your comments again, Tim To > -----Original Message----- > From: Reidy, Ron [mailto:[EMAIL PROTECTED] > Sent: Saturday, April 17, 2004 1:26 PM > To: [EMAIL PROTECTED]; Lincoln A. Baxter; Janet Goldstein > Cc: Dbi-Users > Subject: RE: performance problem with dbd oracle > > > In looking at your posted code: > > 1. Set AutoCommit => 0. You are committing after each insert > (or update, or delete) statement. Commits are very expensive. > If you would trace your session (using event 10046), you would > see exactly why it is so expensive. > > Other comments below: > > -- > Ron Reidy > Sr. DBA > Array BioPharma, Inc. > > > -----Original Message----- > From: Tim To [mailto:[EMAIL PROTECTED] > Sent: Fri 4/16/2004 4:27 PM > To: Lincoln A. Baxter; Janet Goldstein > Cc: Dbi-Users > Subject: RE: performance problem with dbd oracle > Thanks for the suggestion. > I do realize that bind varibles are important to performance and > the hashing > algorithm that Oracle uses to identify sql statement in the library cache. > Unfortunately a portion of the sql statements are generated by a > third party > perl app and if we try to take over we have other problems :( > Also I thought > bind variables shouldn't be that important when it comes to > insert statement > since there's no execution plan invovled?? I could be wrong here). > > You are wrong here. Bind variables a very inportant. > > Anyways, the comparison plsql code is also not using any bind variables so > if the bottleneck is bind variable related (i.e. within Oracle) it should > affect the pl/sql code the same way - shouldn't it?? > > Tim > > > -----Original Message----- > > From: Lincoln A. Baxter [mailto:[EMAIL PROTECTED] > > Sent: Friday, April 16, 2004 3:01 PM > > To: Janet Goldstein > > Cc: Dbi-Users > > Subject: RE: performance problem with dbd oracle > > > > > > On Fri, 2004-04-16 at 09:10, Jones Robert Civ TTMS Keesler wrote: > > > Code? Also, using sqlloader, the native program for > > inserting data, > > > has been cited as being faster than using DBI for straight insertions. > > > > If you are not using bind variables (placeholders for your data), then > > it will be quite slow, since every statement would then have to be > > parsed by oracle. > > > > Thats why you were asked for your code. > > > > Lincoln > > > > > > > > -----Original Message----- > > > From: Tim To [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, April 15, 2004 12:53 PM > > > To: [EMAIL PROTECTED] > > > Subject: performance problem with dbd oracle > > > > > > > > > Hi, > > > How can I improve performance when using oracle dbd? We have an urgent > > > performance problem where inserting data into an Oracle 9i > > database using > > > DBI/DBD oracle is at least five times slower than stright sql > > using sqlplus. > > > Any suggestion is appreciated. > > > > > > DBD::oracle 1.15 > > > DBI 1.4 > > > PERL 5.8.0 > > > running on solaris 9 > > > Oracle 9.2.0.1 > > > > > > Thanks, > > > Tim To > > > > > > > > > > > This electronic message transmission is a PRIVATE communication > which contains > information which may be confidential or privileged. The > information is intended > to be for the use of the individual or entity named above. If you > are not the > intended recipient, please be aware that any disclosure, copying, > distribution > or use of the contents of this information is prohibited. Please > notify the > sender of the delivery error by replying to this message, or notify us by > telephone (877-633-2436, ext. 0), and then delete it from your system. >
