Testing DBD::Oracle array interface

[John Scoles]

Hi Shreedhar and the rest of the dev list

I have managed to add support for the Oracle Array Interface in DBD::Oracle.
The original code comes from an old patch that Kristian Nielsen had created
a while back but never seem to get into the to trunk.

I am just in the test design phase right now. (I want to do some benchmarks
for large inserts) but I figure it is stable enough that I would ask for a
little help from the list on testing this patch.

To be on the safe side I created a new branch on the SVN tree so the code
for this patch can be found at

http://svn.perl.org/modules/dbd-oracle/branches/array

I was hoping someone could do a benchmark type test on loading many thousand
or millions or records, as this seem to be what people want to use the
interface for.

Cheers John Scoles

----- Original Message -----
From: "Shreedhar Natarajan" <nata...@ncsa.uiuc.edu>
To: "John Scoles" <sco...@pythian.com>
Sent: Monday, May 29, 2006 10:03 AM
Subject: Re: oracle array interface

> Hi John,
>
> I am happy to do the testing. I was about to give up on perl/oracle. I
deal
> with large sets of data. I appreciate your working on it.
>
> In the meantime, to keep me productive on my research, I am considering
> using java and calling java from perl. Any thoughts on that?
>
> Thanks,
> Shreedhar
>
>
> At 09:51 AM 5/29/2006 -0400, you wrote:
> >I am sort of working on it right now. Slowly of course. The current one
> >works but all it does is iterate over you array and execute your
statements.
> >It makes your code tidy but does not go any faster that doing the
iteration
> >yourself.
> >
> >Look for a patch for Array Interface sometime within the next two
months.
> >
> >It would be great if you could do some testing with it as well
> >
> >Cheers.
> >John Scoles
> >
> >----- Original Message -----
> >From: "Shreedhar Natarajan" <nata...@ncsa.uiuc.edu>
> >To: <dbi-...@perl.org>
> >Sent: Monday, May 29, 2006 9:25 AM
> >Subject: oracle array interface
> >
> >
> > > Hi,
> > >
> > > I saw on the web that the Oracle array interface has not been
implemented
> > > properly. I am new to DBI. Has the implementation been made more
> >efficient?
> > >
> > > Thanks,
> > > Shree
> > >
> > >
>
>

[Scott T. Hildreth]

On Tue, 2006-06-06 at 08:20 -0400, John Scoles wrote:

> http://svn.perl.org/modules/dbd-oracle/branches/array

I am not an svn user (well we still use cvs at work),
shouldn't I be able to run,

'svn co http://svn.perl.org/modules/dbd-oracle/branches/array'

..or is that dir not able to be checked out?

--
Scott T. Hildreth <shil...@scotth.emsphone.com>

[Andy Hassall]

> I have managed to add support for the Oracle Array Interface in
> DBD::Oracle.
> The original code comes from an old patch that Kristian Nielsen had
> created
> a while back but never seem to get into the to trunk.
>

> I was hoping someone could do a benchmark type test on loading many
> thousand
> or millions or records, as this seem to be what people want to use the
> interface for.

I'm very interested in this, but for a slightly different reason - I
don't have huge data volumes, just hundreds to maybe a few thousand rows
at a time, but in one particular process I am having to connect over a
medium latency (120ms) transatlantic network link. The network overheads
for single rows inserts are killing performance here.

Tried compiling on Cygwin to start with; this failed as an earlier change
in LOB support didn't add some lines to oci.def - patch attached to fix
it (this applies to both trunk and array branch).

Have also now compiled and tested on Solaris; results from a simple test
inserting batches of 1000 rows to a remote database:

Rate singlerow arrayinterface
singlerow 4.76/s -- -87%
arrayinterface 35.7/s 650% --

So that looks quite promising.

thanks,
--
Andy Hassall / <an...@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[John Scoles]

Thanks Andy that is great. I am finding a few things as I test on other
boxes as well (Lunix and windows).
Would you be so kind as to send me details on your test rig that is one area
where my resources as a little limited.

Cheers.

[Andy Hassall]

I can test on Windows native (MS Visual Studio.NET 2003), Windows Cygwin,
and Whitebox Linux (an RHEL4 clone), with some combinations of 9.2, 10.2 and
Oracle XE.
I'm still setting things back up again on the Windows side after a hard
drive crash, so this is a bit limited at the moment.
I may also be able to do some limited testing on Solaris at work, but only
where it's related to the process I mentioned that would benefit from array
binding.

I have a script that does batch builds and tests under various OS, Oracle
client and NLS_LANG combinations, which I've run for the last couple of
releases of DBD::Oracle; if you're interested I can tidy that up and publish
it.

--
Andy Hassall :: an...@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool