I have a database table with numerous fields including "category". The source of this table is an xml file which the system receiving occasionally. Each xml file contains data for one category. The table needs to be updated with the new category data from the xml file. The xml file includes all data for that category, not just the changes.
I see two possible ways of handling this:
-
First deleting all rows from the table where category = categoryID, and then doing inserts based on all the xml data. Obviously the deletion and insertion operations would be contained within a transaction.
-
For each record in the xml, first do a select to test if that record exists. If it does exist, update the data with that from the xml. If it doesn't exists, insert it.
Obviously, the second approach avoids deleting the data first but involves many more db queries although this could be mitigated by doing an initial select to a hash table, and just querying the hash. The big downside to the second approach is how to handle deletions, i.e. records that no longer appear in the xml and should be removed from the table.
What is the best practice for handling this kind of operation?
Thanks.
Best Answer
Since you have the primary key of each record, I highly recommend using the approach of examining each record individually.
Primary key lookups are extremely fast in any professional-grade database.
Updating a record based on primary key has a very granular lock level and is fast.
By inserting or updating rather than delete/insert, you maintain the existence of data: at no time is it possible to query the
categorytable and come up empty (unless you are querying a category that you have not gotten around to inserting yet).In short, do not worry about performing extra queries here because the queries will execute just about as fast as possible. I also assume categories are relatively fixed in quantity, as compared to say a logging event or banking transaction which is always increasing the amount of data.