I would also like to add that the difference of 10 seconds between
DBD::Oracle and sqlldr (conv) is not constant always.
While testing I have observed instances (couple of times) when DBD::Oracle
was lagging just by 5 seconds which is not big. :)
Cheers,
Parag
On Sun, May 2, 2010 at 11:34 PM, Parag Kalra <paragka...@gmail.com> wrote:
> 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 <j...@jeffseger.com> 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 <sco...@pythian.com> 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 <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
>> >
>> >
>>
>>
>>
>> --
>> "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
>>
>
>
