First off I apologize if this is question has been beaten to death. I've looked around for a simple answer and could not find one.
Given a database that will not have it's PKEY or indices modified, is it generally faster to INSERT or UPDATE data. And if there is a performance difference is it substantial?
I have a situation where I can easily do one or the other to the same effect. For example, I have a journaling schema with a limited number of "states" for an "entry". Currently each state is it's own table so I just insert them as they occur. But I could easily have a single "entry" table where the row is updated with column information for states (after the entry's initial insertion).
Not a big deal but since it's so easy for me to take either approach I was wondering if one was more efficient (for a large DB) than another.
Thanks
If you think of an update as a delete-insert operation (glossing over the fine points of what has to be done for ACID), it seems pretty clear that an update involves more work than an insert. Measuring that impact on performance is probably a bit more challenging, because it's going to be dependent on the specific table and the contents of the row, among other things. --