Re: looking for speed

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 13 Oct 2004 23:02:01 +0100
Message-ID: <pi8rm0hd3l26jvba4s5v0tmut6vciv01lb@4ax.com>

On Tue, 12 Oct 2004 05:17:37 -0500, "Jerry Preston" <g-preston1_at_ti.com> wrote:

>I have a simple table with 25k plus entries. Currently I am using the
>following and it is very slow:
>
> my $sql="select Name, Address, ID
> from data_status
> where TRASHED = 'NOT'
> and NOT = '$not'
> and TP = '$area'";
>
> print "$sql<BR>" if $DEBUG;
> my $sth=$dbh->prepare($sql) || die "$DBI::errstr\n";
> $sth->execute() || die "Cannot execute Statement - build sort
>tables!!<br>$DBI::errstr";
>
> while( my ( $Name, $Address, $ID ) = $sth->fetchrow() ) {
> .
> .
> }

 Are you running this only once? If more than once, or unless you've got a good reason not to, use bind variables rather than embedding values, e.g.

my $sql=<<'';
select Name, Address, ID
from data_status

where  TRASHED = 'NOT'
and    NOT     = ?
and    TP      = ?

my $sth= $dbh->prepare($sql)

    or die "$DBI::errstr\n";
$sth->execute($not, $area)

    or die "Cannot execute Statement - build sort tables!!<br>$DBI::errstr";

 This avoids a hard parse, and stops you having to worry about SQL injection attacks.

>Is there any way to speed this up?

 There's the basics of tuning the statement - what's the execution plan?  That's generally where the biggest gains are to be had.

> while( my ( $Name, $Address, $ID ) = $sth->fetchrow() ) {
> .
> }

 See the DBI docs for alternatives.
 http://search.cpan.org/~timb/DBI-1.45/DBI.pm

 In particular the docs for fetchall_arrayref (for fetching in bulk), and bind_columns with fetch (bind variables to selected columns, and use fetch to update them for each row - may avoid some of the memory allocation overhead).

 fetchall_arrayref gives the following example as part of the docs:

"
If $max_rows is defined and greater than or equal to zero then it is used to limit the number of rows fetched before returning. fetchall_arrayref() can then be called again to fetch more rows. This is especially useful when you need the better performance of fetchall_arrayref() but don't have enough memory to fetch and return all the rows in one go. Here's an example:

  my $rows = []; # cache for batches of rows   while( my $row = ( shift(@$rows) || # get row from cache, or reload cache:

                     shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)||[]})

)
  ) {
    ...
  }

That can be the fastest way to fetch and process lots of rows using the DBI, but it depends on the relative cost of method calls vs memory allocation.

A standard while loop with column binding is often faster because the cost of allocating memory for the batch of rows is greater than the saving by reducing method calls. It's possible that the DBI may provide a way to reuse the memory of a previous batch in future, which would then shift the balance back towards fetchall_arrayref().
"

 Also consider the RowCacheSize attribute, which influences how many rows DBD::Oracle prefetches through OCI.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Wed Oct 13 2004 - 17:02:01 CDT