Replace or update data?

Hi,

I have products in a database each of which have varying amounts of data describing them.

Some products have variations e.g. color size, and some have price bands e.g. quantity 1-10 = $5 : 11+ = $6. This extra data is stored in seperate tables to the main products table. The rows in these other tables reference a product ID in the main table.

The question is, if an administrator were to update the product data but only change, say, the name or cost of a single product variation or the price of a single price band is it worth keeping track of exactly which item of data was changed and update that piece of data in the database or would it be better to just scrub all the data in the extra tables for the current product and re-insert all of it fresh??

Cheers,

I.From an efficiency point of view you should just update the value(s) that have changed. Inserts can be an expensive operation, particularly if there are triggers or indices to deal with.

If you're using DataSets along with a DataAdapter this will keep track of which row(s) changed, which were deleted/inserted and so call the appropriate SQL to refresh the database.|||Would you recomend cacheing the dataset in session state or viewstate?|||By caching do you mean saving the dataset for use during a postback? If so then it depends. Session state will lead to quicker response times as the cached data doesn't need to make a roundtrip to the client. But using session state will eat up your server's RAM and could make the site less responsive.

If you don't think the use of server side memory wil be an issue go with session state. Be sure to clear out the DataSet once you don't need it anymore. The .Net Framework will do this eventually but it would help to clear it out as soon as its not needed. (I assume you're not using a clustered web server which would make using session state a little trickier for complex objects like DataSets.)

If by caching you mean allowing the DataSet to be used by multiple users then you should place it into the Cache object. The Cache object is visible to all users and they share the same data.|||Yeah, for use during a postback.

Another concern I have is the cost of using a dataset update.

Upon update, a sql query is executed for each row in the dataset that's been modified/added/deleted. Would it not be better to wrap all the data in XML and send it direct to the database all in one go? Of course, doing that would mean manually checking to determine the operation required for each row.

Thanks,

WT.|||I think the DataSet update with a DataAdapter will be about as efficient as you can get, at least without doing some more coding. I haven't tried the XML route but it seems like you'd be adding the overhead of serializing the data to XML and then de-serializing it back to get it into the database.|||Thanks for your help.

I think a good reason for using viewstate for saving data inbetween postbacks is that viewstate doesn't time out.

Also, although I'll be trying this approach for product variations, I think that my original example of price bands might be better suited to deleting all the current data and replacing it.

This is because the price bands need to be consistent with one another and they also need to be complete. Imagine that whilst someone is editing a set of price bands, another user deletes all of them and gives the product a single price. If the first user then adds a new price band, upon update of the dataset, the new price band will be added and no other updates will take place. This would result in the situation of there being a single price band for the product and no price band indicating the price of 1 item. (this was deleted by the second user.)

Similarly, imagine that two different users add a new price band to a product's current set. The bands have the same lower bound but a different price. The first band is inserted but the second can't be because a unique contraint at the database forbids two bands having the same lower bound. So in this case, extra code would be needed tp recognise that an update needs to be used instead of an insert.

So in this case, I think that a 'last one in wins' approach is best to updating these price bands rather than trying to merge different sets together.

What do you think?

WT.