> If you can, it is usually most effecient to handle one row at a time.
If you are working with Oracle, or any database for that matter,
one of the cardinal performance rules for programming is to do
it in SQL in possible. It is much faster than anything you
can write in Perl, or any other language for that matter.
This is due to the data staying in the database, and not
being sent to/from the application.
If it can't be done in a SQL statement, do it in a PL/SQL
block on the database if possible, or via stored procedure.
It depends on just how important the performance is, and how
much data you have to process.
There are exceptions of course, and if you're not working
with large amounts of data, it may not be worth the extra
trouble.
Use the RowCacheSize dbh attribute to efficiently fetch
rows from Oracle - empirical testing suggests that 100
is a good starting value for that.
Set the ora_check_sql sth attribute to 0 to avoid extra
parses in Oracle.
*Always* use bind variable as has already been suggested.
The performance penalty for not doing so is huge.
HTH
Jared
On Mon, 2003-08-18 at 09:36, Michael A Chase wrote:
> > 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.
>
