[Pdx-pm] DBI middle ground

Jeff Zucker jeff at vpservices.com
Mon Jan 29 09:02:08 PST 2007


Michael Rasmussen wrote:
> I'm querying a large data set.  The current code works something like:
>
>    while( @lease_information = $lease->fetchrow_array) {
>          modification of data, plugging in defaults where currently null
>          ensuring constraints are met
>          # another_db_sth is an update command
>          $another_db_sth->execute(@lease_information);
>    }
>
> I don't have control over the source database, so the changes need to be made in
> my code.
>
> This takes longer to execute than I'd like.  I can't just do a fetchall or
> selectall because the dataset is larger than my available memory.  Nor have I
> found a middle ground.
>
> Is there one?  If so, that I may study up, what are references to it?
>
>   

I'll assume that you are using placeholders and doing your prepares 
outside of the loops.  Have you tried bind_cols (not binding 
placeholders on the way in, but binding values on the way out).  The DBI 
docs say the fastest way is ftechall_arrayref with bind cols, though it 
may or may not help much in your case.  Tim Bunce's OSCON talk always 
features performance tips, you might google for it, the slides are 
online somewhere.  I don't know what database you are using but 
functions or UDFs might help e.g. let the SQL deal with defaults and 
NULLs (though depending on your RDBMS that may take *more* time).  Good 
luck!

-- 
Jeff


More information about the Pdx-pm-list mailing list