> Working with an Oracle database, there are several ways to fetch rows
> from table. In terms of performance which best? Here is a snippet from
> an existing script, are there ways to improve it?
If you can, it is usually most effecient to handle one row at a time.
Perl puts a lot of effort and memory into building a hash for each
line and adding that hash to $aref. That can be very significant if
you are fetching more than a few thousand rows.
> my $sql = qq { SELECT a.duns_num, b.name, c.last_upd, c.start_dt,
> c.end_dt FROM $schema.s_org_ext a, $schema.s_org_ext b,
> $schema.s_quote_soln c
> WHERE c.serv_accnt_id = b.row_id AND c.inv_accnt_id = a.row_id AND
> c.action_cd = 'EnergyServices'
> AND c.last_upd >= ? AND c.last_upd < ? $id
> ORDER BY c.start_dt};
> my $sth = $dbh->prepare($sql);
> my $rc = $sth->execute($strt,$end);
> my $aref = $sth->fetchall_arrayref({});
> foreach (@$aref) {
> $$_{STARTTIME} = $$_{START_DT};
> $$_{STOPTIME} = $$_{END_DT};
> $$_{STATUS} = 'A';
> }
Consistent indenting makes the SQL much easier to read.
The loop is not needed if you alias the appropriate columns.
SELECT a.duns_num, b.name, c.last_upd,
c.start_dt starttime, c.end_dt stoptime, 'A' status
FROM $schema.s_org_ext a, $schema.s_org_ext b,
$schema.s_quote_soln c
WHERE c.serv_accnt_id = b.row_id
AND c.inv_accnt_id = a.row_id
AND c.action_cd = 'EnergyServices'
AND c.last_upd >= ?
AND c.last_upd < ? $id
ORDER BY c.start_dt
You might instead return $sth then fetch and process each row
separately.
> return($aref);
>
> Would an ref cursor be better?
Probably.
If you don't need to refer to multiple rows at one time, definitely.
I normally return $sth and use bind_columns() and fetch() to minimize
the work DBI has to do with the column values.
# This method depends on the order of the returned columns,
# not their names, so aliases would not be needed.
my ( $duns_num, $name, $last_upd, $starttime, $stoptime, $status );
$sth -> bind_columns( \( $duns_num, $name, $last_upd, $starttime,
$stoptime ) ); # Add $status if you added 'A' to the SELECT
while ( $sth -> fetch ) {
$status = "A";
# do something with the row
}
# assuming you didn't set $dbh->{RaiseErrors} earlier
die "Fetch failed, $DBI::errstr" if $DBI::err;
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
