I assume that last line should be push @array_claim, $TOALL?

If you are trying to do what I think you are (create an array containing all the 'ACCOUNT#' values from a query), try this:

## This assumes your select begins 'SELECT [ACCOUNT#]', 
## that is, ACCOUNT# is the first column returned
@array_claim = map { $$_[0] } @{$sth->fetchall_arrayref};
[download]

Read up on map to understand how that works.

FYI, fetchrow_hashref is the slowest of the fetchrow_ methods, so you want to avoid it unless you have no better choice.

Hi,
This is what I have, and when I run the code it is very slow. And it is only slow when the code inside the while loop runs if I commented out the while loop the code is very fast.
Here is what I have:


while ( @array_c = $sth->fetchrow()){

$TOALL = &cleaner ($pointer->{'ACCOUNT#'});

push @array_c,$TOALL; # Array that has all account numbers 

}
print "<br><font color=red><b> $array_c </b></font>";
[download]


After that I have to use the values there from the @array_c to do another DB query
Thanks

After that I have to use the values there from the @array_c to do another DB query

In addition to the previous comments..
Can you combine that second (looped) DB query into the first query?
That will considerably reduce the overall time since there will be fewer calls to the DB.

That code doesn't make sense. You are attempting to invoke fetchrow() and store its result in @array_c. Then you try to dereference $pointer, which you have not set.

My suggestion stands. Change the number of the index in my original code to match the position at which your 'ACCOUNT#' field is returned, or find that by doing:

my $index = map { next unless $_ eq 'ACCOUNT#'; $_ } @{$sth->{NAME}};
##Now, my original code:
@array_c = map { $$_[$index] } @{$sth->fetchall_arrayref()};
[download]

Anima Legato
.oO all things connect through the motion of the mind

The fetch-while DBI part of the question was discussed a few days ago: bind_columns OR fetchall_arrayref.

  my $sth = $dbh->prepare( $sql );
  $sth->execute;
  my ($pointer);
  my $rv = $sth->bind_columns(\$pointer);
  while ($sth->fetch) {
      # do something with $pointer
  }
[download]

for (1 .. $sth->do("COUNT SOMETHING")) {
  cleaner($TOTALLCLAIM);
}
[download]

Anyway, assuming that 'ACCOUNT#' is an actual result column, you meant $TOALL instead of $TOTALLCLAIM, and you can write your SQL query so you only get that column from the DB (if you haven't done so already that will probably get you the biggest performance increase) - asking for an array will probably be slightly faster:

while (my ($accountno) = sth->fetchrow_array) {
   push @array_claim,$accountno;
}
[download]

Usually you can gain the most performance in database related programs by carefully analyzing and (re)writing your queries and datamodel, though.

Usually you can gain the most performance in database related programs by carefully analyzing and (re)writing your queries and datamodel, though.

I would like to echo this. I once came onto a project and took reports that were running in 240s and by redoing the schema and the SQL, had those reports running in under 10s. That's an 24x speedup without changing a single part of the Perl code.

I later improved the Perl code and took the reports to under 3s, but that's only a 3x speedup. That means that dealing with the database had 8x the impact as dealing with the Perl.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Where in your code I am looking for the colunm "ACCOUNT#" in the database?

I was assuming $pointer->{'ACCOUNT#'} referred to an actual column.

my ($accountno) = sth->fetchrow_array;
[download]

Assigns the first column in the result to $accountno, so if you make sure that that ACCOUNT# is the first (or only) row that will do it.
"What should it profit a man, if he should win a flame war, yet lose his cool?"

The efficiency will depend on the SQL - specifically, how much you are SELECTing. Since you only use the statement to produce an array of account numbers, you should only select that column.

What is $TOALLCLAIM? It looks like a different name for $TOALL, which should be lexical, or else omitted entirely.

Assuming 'ACCOUNT#' is a legal column name in your db, and that your DBD supports the method, the whole thing might be written,

my @array_claim = map {
    cleaner($_)
} @{$dbh->selectcol_arrayref( 'select ACCOUNT# from datable')};
[download]

Sorry there isn't $TOALLCLAIM, it's $TOALL

That seems to be a strong indication that fetching the data via DBI is not a problem at all (and neither is the SQL itself). Could it be that the cleaner subroutine is the slowest part?

What is cleaner doing? If this is a simple operation you could try to do it in SQL directly and select the clean value. That should speed things up.