The first reply is very important -- definitely prepare each statement once, and use it multiple times.

Apart from that, if you don't have a really vast quantity of distinct "$project_name" or "$oligos_type_id" values, you might consider loading all of these into a hash before you start reading the data file: 

my $proj_id_sth = $dbh->prepare( "select project_name,project_id from 
+projects" );
$proj_id_sth->execute;

my %proj_name_id;
while ( my $row = $proj_id_sth->fetchrow_arrayref ) {
    my ( $pr_name, $pr_id ) = @$row;
    $proj_name_id{$pr_name} = $pr_id;
}

[download]
and similarly for the "oligos_type_name / oligos_type_id" mapping (update: that is, using a separate but similar %oligos_name_id hash). That way, you execute each of those queries just once before reading the file, and then as you go ahead and read the file, you are just using each "name" value as a hash key to look up the "id" value for your insert.

One more thing to consider: instead of doing the inserts via DBI in your script, you could just output the rows of field values to be inserted to a tab-delimited (or other suitable) data file, then use your DB engine's native "import" or "loader" utility (sqlload for oracle, mysqlimport for mysql, etc) to apply the inserts based on your output list. That will be significantly faster than doing inserts with Perl/DBI. The difference becomes dramatic when you are looking at thousands of inserts in a single run.

In reply to Re: How do I improve the effeciency of my script by graff
in thread How do I improve the effeciency of my script by lomSpace

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.