Found it!

First, clean up all the preamble: The while loop at the top can be rewritten as: 

  open (FILE,$dir) || &error("Can't open $dir: $!");
  $sth=$dbh->prepare("insert into convert.sql_edits values (?, ?)");
  while (<FILE>) {
    chomp;
    ($id, $value)=split/\|/;
    $sth->execute($id, $value);
    push @total, $sth->rows;
  }
  $sth->finish;
  close FILE;

[download]
since you're not using any of the values read (other than via the database).

The subsequent SQL statement building can be simplified, since you're using $type and $field to determine the table name, and the rest of the SQL statement doesn't vary.

And having cleaned sheer volume of setup out of the way, and thus determined that it can't possibly be the cause of the problem, the only part left is: 

  $sth=$dbh->do($sql) ||
    &error("Unable to perform update statement:<br /><br />$sql<br /><
+br />$DBI::errstr\n");
  $updated=$sth->rows;
  $dbh->commit() ||
     &error("Unable to commit changes made by the update statement: <b
+r /><br />$sql<br /><br />$DBI::errstr\n");;
  $sth->finish;

[download]

But the documentation says the $dbh->do returns a row count, not a statement handle. So there you go.

Finally, why bother with the convert.sql_edits table anyway? For every record in the table being updated, get the counter value, look it up in @edits, and set the appropriate field. That'd probably save a lot of grief: you'd have one database call per record updated, rather than one per convert line, which may or may not be better, but they wouldn't all have to be deleted afterward, either.

--
Tommy
Too stupid to live.
Too stubborn to die.

In reply to Re: Re: Re: dbi update statement is very slow by tommyw
in thread dbi update statement is very slow by ctaustin

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.