[Phoenix-pm] PERL DBI
Loo, Peter # PHX
Peter.Loo at source.wolterskluwer.com
Mon Mar 13 13:37:13 PST 2006
Here is what it took to run:
Mon Mar 13 13:05:34 MST 2006
Mon Mar 13 14:31:27 MST 2006
SQL> select count(*) from dssppv.tmp_falcon_projections;
COUNT(*)
----------
2413059
Peter Loo
Wolters Kluwer Health
(602) 381-9553
-----Original Message-----
From: Scott Walters [mailto:scott at illogics.org]
Sent: Monday, March 13, 2006 2:36 PM
To: Loo, Peter # PHX
Cc: phoenix-pm at pm.org
Subject: Re: [Phoenix-pm] PERL DBI
> This is sad.
No, this is not sad. Dump/undump programs are hand-tuned C designed to
do exactly one thing and do it very quickly. What you're doing would be
nearly as slow in any language (or nearly as fast). That aside, if it
runs for
24 hours and you have to do it once, what are you crying about? Was the
project notice 48 hours to move from one database system to another?
Are
you doing daily syncs? Go do something else. Presumably the program
prints status info. If not, make it, just to detect possible
pathological conditions.
-scott
On 0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
>
> Hi Scott,
>
> I kicked off the program at 13:05:34 and it is still running. We are
> only dealing with 2,000,000 rows. This is not going to work. I have
> to think of something. Dumping the data into and external table in
> Neteeza on a NFS mount and using SQL_LOAD in Oracle will definitely be
faster.
> This is sad.
>
> Peter Loo
> Wolters Kluwer Health
> (602) 381-9553
>
> -----Original Message-----
> From: Scott Walters [mailto:scott at illogics.org]
> Sent: Monday, March 13, 2006 2:14 PM
> To: Loo, Peter # PHX
> Cc: phoenix-pm at pm.org
> Subject: Re: [Phoenix-pm] PERL DBI
>
> Peter,
>
> The change in speed would be trivial then, I'm guessing, but there's
> always Devel::Prof if you don't want a guess.
>
> You're suffering from round-trip time right now more than throughput,
> I'm also guessing.
>
> -scott
>
> On 0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
> >
> > Scott,
> >
> > How about if I changed the fetchrow_array to fetchrow_arrayref? I
> > think that will help. I am currently using the same database to
> > test (Oracle production table to an Oracle temp table). I am not
> > using Neteeza (source database) yet.
> >
> > Peter Loo
> > Wolters Kluwer Health
> > (602) 381-9553
> >
> > -----Original Message-----
> > From: Scott Walters [mailto:scott at illogics.org]
> > Sent: Monday, March 13, 2006 1:29 PM
> > To: Loo, Peter # PHX
> > Cc: phoenix-pm at pm.org
> > Subject: Re: [Phoenix-pm] PERL DBI
> >
> > Hi Peter,
> >
> > Was it running slow before? I can't imagine that this would make it
> > slower...
> >
> > You could get a little parallelism if you used threads and made a
> > Thread::Queue to pass rows from a reader thread to a writer
thread...
> > but Perl threads are pretty grumpy.
> >
> > Just a thought.
> >
> > Cheers,
> > -scott
> >
> > On 0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com>
wrote:
> > >
> > > Hi All,
> > >
> > > Alright, I got it working, but it is running so slow. I am
> > > wondering if there is anyway to speed this up? Here is the syntax
> > > I
> am using:
> > >
> > > $bindVars = join ', ', ('?') x @column_names;
> > > $d_sth = $d_dbh->prepare(qq{insert into
> > > dssppv.tmp_falcon_projections ($columnNames)
> > > values ($bindVars)}) || die
> > > "$DBI::errstr\n";
> > > $s_sth = $s_dbh->prepare("select * from
> > > dssppv.p_falcon_projections") || die "$DBI::errstr\n";
> > > $s_sth->execute() || die "$DBI::errstr\n";
> > > while ( my @row = $s_sth->fetchrow_array ) {
> > > $d_sth->execute(@row);
> > > }
> > >
> > >
> > >
> > > Peter Loo
> > > Wolters Kluwer Health
> > > (602) 381-9553
> > >
> > > -----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.
> >
> >
> > 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.
>
>
> 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.
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.
More information about the Phoenix-pm
mailing list