Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Re: dbi: moving big data among databases (prepared statements)

by atcroft (Abbot)
on Jun 16, 2018 at 18:25 UTC ( #1216789=note: print w/replies, xml ) Need Help??

in reply to dbi: moving big data among databases (prepared statements)

It is my understanding that the purpose of preparing the statement is to reduce the work you are done each iteration. As written, you are preparing the statement each time, which will add a little time to each pass. Perhaps a better way to do this would be:

# Recommend not using '*' but specifying the field names, # to future-proof code. my $query1 = "SELECT * FROM database1.table WHERE ( id = ? );"; my $query2 = "INSERT INTO database2.table ( value1, value2 ) VALUES ( ?, ? ) ;"; my $sth1 = $dbh1->prepare( $query1 ) or die $dbh1->errstr; my $sth2 = $dbh2->prepare( $query2 ) or die $dbh2->errstr; foreach my $id ( @big_data_arrray ) { # Original code had an error # $sth2->execute was called in both cases $sth1->execute( $id ) or die $dbh1->errstr; while ( my @row = $sth1->fetchrow_array ) { $sth2->execute( @row[0 .. 1] ) or die $dbh2->errstr; } $sth1->finish; }

Instead of performing 2*N (N=number of items in @big_data_array) prepare calls, only 2 prepare calls are used.

Hope that helps.

Replies are listed 'Best First'.
Re^2: dbi: moving big data among databases (prepared statements)
by Anonymous Monk on Jun 16, 2018 at 19:00 UTC

    Following up: Every time your 'select *' gets compiled, the DB has to query the metadata to expand the '*'. A smart database will cache this information, but the work still has to be done. If it's a lot of data and a slow link, a colleague and I found that using prepared statements (prepare_cached(), actually) reduced a database query that took more than a day (to move 24 hours' data) to one that took an hour or so.

    There is another reason to use prepared queries: they are resistant to SQL injection attacks. I would post a link, but Perl Monks censored it. Google "Bobby Tables" for what I mean.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1216789]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (8)
As of 2021-01-20 13:28 GMT
Find Nodes?
    Voting Booth?