Hi all:
For those who've been following the saga, I'm working on an application that needs to load a data file consisting of approximately 29,000 to 35,000 records in it (and not short ones, either) into several tables. I'm using MySQL as the database.
I've noticed a really horrible performance difference between INSERTing rows into the table and UPDATEing rows with new data when they already exist in the table. For example, when I first start with an empty table, the application inserts around 29,600 records in something less than 6 minutes. But, when I use the second file, updating that same table takes over 90 minutes.
Here's my question: I had assumed -- probably wrongly -- that it would be far more expedient to only update rows where data had actually changed; moreover, that I should only update the changed fields in the particular rows. This involves a large number of if statements, i.e.,
if($old_row["field_a"] !== $new_row["field_66"] {
$update_query .= "field_a = '" . mysql_real_escape_string($new_row["field_66"]) . "',";
}
Eventually, I wind up with a query similar to:
UPDATE table_01 SET field_a = 'New value here', updated=CURDATE() WHERE primary_key=12345
I thought that, to keep the table updating to a minimum, this approach made the most sense. However, seeing the two hugely different performance times has made me question whether or not it would be faster to simply update every field in the table and eliminate all of these test conditions.
And, before someone comments that indexes on the table can cause performance hits, I DROP nearly all of the indexes at the start of the processing, only keeping those indexes necessary to do the original INSERT or the subsequent UPDATE, and then add all of the extra "steroid" indexes (you know -- the performance-enhancing ones <g>) after all of the INSERTs and UPDATEs have been finished.
So, long story short (oops -- too late!), what's the concensus among the learned assembly here? Is it faster to just UPDATE the record if it already exists regardless of the fact that maybe only one or two out of 75 or more fields changed versus testing each one of those 75 fields to try and figure out which ones actually changed and then only update those?
I look forward to reading all of your thoughts.
Sincerely,
Jon
