Re: Insert Query with place holders Vs SQLLDR

Ok I increased the ora_array_chunk_size size as shown below (I hope its
correct)

# Connecting to database
my $dbh = DBI->connect("dbi:Oracle:$dbname", $uname, $passwd,{
      PrintError => 0,
      RaiseError => 0,
      ora_array_chunk_size => 1000000
  } ) or die "Could not connect to the database\n";

Also removed the section to drop and create table from all the 3 scripts.
Here are the new results with same set of records:

sqlldr conv    - 27 seconds
DBD::Oracle - 37 seconds
sqlldr direct  - 02 seconds

The bottom line is sqlldr if used in direct mode is incomparable but
DBD::Oracle is almost as efficient as sqlldr used in conventional mode

Cheers,
Parag



On Sun, May 2, 2010 at 5:27 PM, Jeffrey Seger <[email protected]> wrote:

> I think you have to keep the time reading records and setting up the
> arrays in the comparison, as SQLLDR has to read and parse too.  I do
> agree that this could be sped up by changing the chunk size though.
>
> I'd also take out dropping and re-creating the table, or at the very
> least, time that operation separately.  If you are calling TRUNCATE in
> your SQLLDR script, then call TRUNCATE in your Perl script as well.
>
>
>
>
> On Sun, May 2, 2010 at 7:58 PM, John Scoles <[email protected]> wrote:
> > 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 <[email protected]>
> 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 <[email protected]>
> 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 <[email protected]>
> 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
> >
> >
>
>
>
> --
> "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
>