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 >
bind_param_array_file.pl
Description: Binary data
