sub calcStdDev {
  my ($dbh) = @_;
  
 
  #-------------------------------------------------------------------
  # Get the count of records in the hydro table
  my $sth_sel_hydro_count = $dbh->prepare(qq{
    SELECT Count(*) - 1 AS foo FROM hydro 
  });
  
  $sth_sel_hydro_count->execute;
  
  my @res = $sth_sel_hydro_count->fetchrow_array;
  my $count_hydro = $res[0];
  #-------------------------------------------------------------------
  
  
  #-------------------------------------------------------------------
  ## Three statements
  # First, to select the hydro data 100 rows at a time
  my $sth_sel_hydro = $dbh->prepare(qq{
    SELECT hydro_id, discharge, precipitation  
    FROM hydro 
    ORDER BY date_time 
    LIMIT 100 OFFSET ?
  });
  
  # Second, to insert std dev values
  my $sth_ins_stddev = $dbh->prepare(qq{
    INSERT INTO stddev (stddev_discharge, stddev_precipitation) 
    VALUES (?, ?)
  });
  
  # Third, to update the hydro table with the stddev id just created
  my $sth_upd_hydro = $dbh->prepare(qq{
    UPDATE hydro SET stddev_id = ? WHERE hydro_id IN (?)
  });
  #-------------------------------------------------------------------
  
  
  my $mnc = Math::NumberCruncher->new();
  
  my $ta = new Benchmark;

  for my $window (0 .. $count_hydro) {
    
###
    $sth_sel_hydro->execute($window);

    # Arrays to hold values
    my @hydro_id; my @discharge; my @precipitation;
    
    while (my $row = $sth_sel_hydro->fetchrow_arrayref) {
      push(@hydro_id, $row->[0]);
      push(@discharge, $row->[1]);
      push(@precipitation, $row->[2]);
    }
    
    $sth_sel_hydro->finish;
    
    
    # Returns the Standard Deviation of @array, which is a 
    # measurement of how diverse the data are
    my $sd_discharge = $mnc->StandardDeviation(\@discharge, 6);
    my $sd_precip    = $mnc->StandardDeviation(\@precipitation, 6);
    
    $sth_ins_stddev->execute($sd_discharge, $sd_precip);
    my $sd_id = $dbh->func('last_insert_rowid');
    
    # Create a comma-separated string suitable for SQL IN operator
###
    my $hydro_id_str = join(',', @hydro_id);
    
    $sth_upd_hydro->execute($sd_id, $hydro_id_str);
    
    # Print out the progress every 10,000 rows, and commit to the db
    if (! ($window % 10000) ) {
      my $tb = new Benchmark;
      print "$window [" . timestr(timediff($tb, $ta)) . "]\n";
      $ta = new Benchmark;
      $dbh->commit;
    }
  }
  
  $sth_ins_stddev->finish;
  $sth_upd_hydro->finish;
  $dbh->commit;
}

>perl std.pl
Started Sun Oct 22 12:38:58 2006...
0 [ 0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)]
10000 [100 wallclock secs (81.09 usr +  4.18 sys = 85.26 CPU)]
20000 [159 wallclock secs (136.73 usr +  5.18 sys = 141.90 CPU)]
30000 [228 wallclock secs (193.98 usr +  5.62 sys = 199.60 CPU)]
40000 [261 wallclock secs (247.19 usr +  5.67 sys = 252.85 CPU)]
50000 [315 wallclock secs (301.86 usr +  5.75 sys = 307.61 CPU)]
60000 [377 wallclock secs (356.05 usr + 13.11 sys = 369.16 CPU)]
70000 [497 wallclock secs (414.08 usr + 59.97 sys = 474.04 CPU)]
80000 [610 wallclock secs (471.53 usr + 88.27 sys = 559.80 CPU)]
[download]