Parag Kalra 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
I agree with that. I can see that the extra time perl is using most
likely has to do with getting the tuples back to perl. This is good
stuff as it shows that DBD::Oracle is comparable
or at least close in processing time to SQLLDR. Or course with Perl you
get a complete language so your potential for processing is much greater.
I do have a question about sqlldr does it keep a record of the 'failed'
inserts or does it just report at the end. In DBD::Oracle we get a set
of Tuples that we can recheck for errors is there
comparable in SQLLDR??
Cheers
John
Cheers,
Parag
On Sun, May 2, 2010 at 5:27 PM, Jeffrey Seger <j...@jeffseger.com
<mailto: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
<mailto: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 <mailto: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 <mailto: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 <mailto: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
