That seems about right a millon records in less than a min is very fast. This could be spead up a bit by setting 'ora_array_chunk_size' to a larger value than the default 1000.
If you can run it again with 10000 and 100000 or even 1000000 to see what we get. I couldn't tell from your code but you are only testing the time it takes to do the insert and not the time to set up the arrays?? cheers John SColes On Sun, May 2, 2010 at 7:44 PM, Parag Kalra <paragka...@gmail.com> wrote: > Ok here is the thing. > > This afternoon I coded few scripts and created a test setup to validate the > benchmarking results of the various tools and following are the results: > > Environment Settings > Operating System: Windoze XP SP3 (sorry couldn't test it Nix as it is a > prod box) > Database: Oracle 10g 10.2.0.1.0 > Perl: 5.10.1 > DBD-Oracle: 1.21 > Total records used for testing: 1048576 > > Time of insertion taken by sqlldr in conventional mode - 35 > seconds > Time of insertion taken by sqldr in direct mode - > 06 seconds > Time of insertion taken by DBD::Oracle using the array interface - 46 > seconds > > PFA the Perl script used for testing. Let me if any of you interested in > viewing the sqlldr control scripts. > > Cheers, > Parag > > > > On Sun, May 2, 2010 at 12:27 PM, Jeffrey Seger <j...@jeffseger.com> wrote: > >> My experience is that DBD::Oracle using the array interface is >> comparable performance-wise to SQLLDR in conventional path mode. >> However, SQLLDR can also be called in direct path mode, which is even >> faster, but you should really know what you are doing before using >> that. It has implications beyond the performance aspect that you need >> to be aware of. >> >> If you want/need/understand using direct path, then SQLLDR is the way >> to go. If you want better integration with the rest of your program >> then DBD::Oracle is the way to go. >> >> On Sun, May 2, 2010 at 4:20 AM, Parag Kalra <paragka...@gmail.com> wrote: >> > Hi All, >> > >> > I want to know which one of these algorithm would be the most optimized >> > solution to insert large number of records and Why: >> > >> > 1. Preparing an Insert query once and executing it with place holders >> for >> > the entire set of records >> > 2. Executing Oracle's sqlldr command and uploading the same set of >> records >> > >> > Cheers, >> > Parag >> > >> >> >> >> -- >> "Champions do not become champions when they win the event, but in the >> hours, weeks, months and years they spend preparing for it. The >> victorious performance itself is merely the demonstration of their >> championship character." -T. Alan Armstrong >> >> "The Ow that can be expressed is not the true Ow." - Ao Tzu >> > > -- Miss MySQL Conference 2010? No problem. Access Pythian speaker session videos at http://bit.ly/mysql2010
