in reply to Best way to match a hash with large CSV file

Any suggestions?

If you need to do something once, do it only once. That includes populating the database and preparing the query. You might even be able to load the CSV file into a database with a bulk loader program and skip that step in Perl.

From a design point of view, your function does way too much, which is why it does the same work over and over, and that's one reason it's too slow.

Improve your skills with Modern Perl: the free book.

  • Comment on Re: Best way to match a hash with large CSV file

Replies are listed 'Best First'.
Re^2: Best way to match a hash with large CSV file
by alphavax (Initiate) on Nov 03, 2011 at 18:12 UTC

    So you are saying is to do the load of the CSV to the DB and the prepare once, and just execute the queries for each value in the hash? using placeholders? Like: 

    $sth = $dbh->prepare( " SELECT * FROM csvtable WHERE SITE LIKE ? AND H
+OUR = ? " ); 
foreach my $key (keys %cell_peakhr)
    {
      $sth->execute( $key, $cell_peakhr{$key});
      @db_result = $sth->fetchrow_array()
      if (scalar(@db_result) < 1)
        {
            $db_result[0] = $date;
        $db_result[1] = $cell_peakhr{$key};
        $db_result[2] = $key;
             foreach my $count (3..$row_size-1)
            {
                $db_result[$count] = "-";            
            }#foreach
        }#if
        print OUT join(',',@db_result)."\n";
    }

    [download]
[reply]
[d/l]

      If you even need the database, yes. If your entire dataset fits into memory in a hash (as is likely), use a hash.

      Improve your skills with Modern Perl: the free book.

[reply]

In Section Seekers of Perl Wisdom