[Phoenix-pm] PERL DBI

[Loo, Peter # PHX]

Hi Scott,

Thanks a bunch.  I was reading exactly what you had recommended below
when I received your email.  Yes, I have a drop indexes statement in the
program to do just that before doing the INSERTs.  You guys are the
BEST.

I love PERL.
 
Peter Loo

-----Original Message-----
From: Scott Walters [mailto:scott at illogics.org] 
Sent: Monday, March 13, 2006 10:30 AM
To: Loo, Peter # PHX
Cc: phoenix-pm at pm.org
Subject: Re: [Phoenix-pm] PERL DBI

Hi Peter,

Use placeholders:

    my $question_marks = join ', ', '?' x @column_names;  # one ? for
each value
    my $sth = $dbh->prepare(qq{insert into foo (@column_names) values
($question_marks)}) or die $dbh->errstr;
    while(my @row = $old_database->fetchrow_array) {  # read from old
database
         $sth->execute(@row); # insert into new database
    }

If you use placeholders, you never need to quote or escape data, and you
don't suffer from SQL command length limitations, and the database
doesn't have to parse potentially megs of data just to parse the SQL
command, and it doesn't have to parse the SQL more than once at all --
you prepare it once and then execute it as many times as you like for a
huge speed increase.  If you're loading a lot of data, you'll also want
to drop indexes first and the rebuild them later.  This is a huge time
savings over incrementally updating them for millions of records.

I might have missed the point of your question -- if so, please clarify,
and I aplogize.

Cheers,
-scott

On  0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
> 
>    Hi,
>    
>    
>            $columnsToAssign = $columnsToAssign . ", \"" .
@$d_arrayref[0]
>    . "\"";
>            }
>          else {
>            $columnsToAssign = $columnsToAssign . ", " .
@$d_arrayref[0];
>            }
>    
>    Is there an easier way?
>    
>    
>    
>    Thanks.
>    
>    
>    
>    Peter Loo
>    
>    


This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information.  Any unauthorized
review, use, disclosure or distribution is prohibited.  If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.


This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information.  Any unauthorized review, use, disclosure or distribution is prohibited.  If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.