bulk loading with DBI

Discussion in 'Perl' started by Greg H, Jul 17, 2003.

  1. Greg H

    Greg H Guest

    Hi,

    I'm just wondering if anyone has experience with bulk loading data into an
    Oracle database with DBI.

    My options are to use DBI or Oracle's sqlloader. I'd rather do it using DBI,
    but I'm not sure what kind of perfomance hit I'll incur. I need to load 2
    tables on a daily basis (~50MB).

    Any comments?

    - Greg.
     
    Greg H, Jul 17, 2003
    #1
    1. Advertising

  2. Greg H

    AdrianK Guest

    SQLLoader will generally be faster.

    Pretty much trial an error really

    For bulk inserts you should use DBI with placeholders.

    Bit of pseudo code, might help...

    my $sth = $dbh->prepare (SQL insert :param1, :param2 etc)
    my $count = 0;

    loop
    $sth->bind_param(":param1", $param1);
    $sth->bind_param(":param2", $param2);
    ...
    ...
    eval { $sth->execute() };
    if ($@) {
    print "Error.......";
    };
    ...
    ...
    if ($count>50) { # Change this value depending on threshold,
    rollback segment size etc
    $self->doCommit($dbh);
    $count=0;
    }
    $count++;

    end loop

    $dbh->commit



    HTH
    AdrianK

    "Greg H" <> wrote in message news:<eVqRa.1340$>...
    > Hi,
    >
    > I'm just wondering if anyone has experience with bulk loading data into an
    > Oracle database with DBI.
    >
    > My options are to use DBI or Oracle's sqlloader. I'd rather do it using DBI,
    > but I'm not sure what kind of perfomance hit I'll incur. I need to load 2
    > tables on a daily basis (~50MB).
    >
    > Any comments?
    >
    > - Greg.
     
    AdrianK, Jul 17, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    531
    Juha Laiho
    Jul 22, 2004
  2. Daniel Berger

    Problem loading driver with dbi, 1.8.x

    Daniel Berger, Mar 1, 2004, in forum: Ruby
    Replies:
    0
    Views:
    91
    Daniel Berger
    Mar 1, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    175
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    178
  5. Tim Haynes
    Replies:
    3
    Views:
    146
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page