Mass Updates

[DClyde]

I have an app that semi-recently converted to being database driven, this C++ app is an asset editor that knows about all of our assets and lets you edit any of them.

In some situations a user will open the app, they will claim lets say 200 assets to edit and edit them, but when they save them we go through each asset and call save on them individually. Although this is holding up ok now each asset saving individually seems to be a lot of queries that end up happening, one for asset data, one for asset metadata some assets have additional data in other table ending up with lets say 400-800 update queries for these 200 assets. This doesnt seem to be increadibly efficient in general and doesn't scale well. I am looking for ways people approach this problem and optimize the number of queries run, suggestions?

200 is a lot for some of our users, and small for others, some of them may commonly work on a set of 500-2000 assets.

 

[SQLSister]

If they individually update each record with different information one at a time, it is probably best to just have them save each one as they move to the next. This save could even be in the background. That way you don't lose changes if the power or connection is lost between when they start editing and when they send the whole group to the database. You also wouldn't have as many blocking issues.

If they have 200 records they want to make the same change to, then use an update stament that works in a set-based fashion instead of updating one at a time. For instance if they want a price increase of 10% for all type b assests then the SQL update would be something like:

update assests
set price = price*1.1
where assesttype = 'b'

This would update all at the same time rather than just one at time.

So to fully address methodology, it would be necessary to understand exactly how the users are making their changes now and what code you are using to send those updates to the database. To move to a more set-based update might mean substantially changing how your user interface works. For instance right now they probably call up a set of records and then go through and make individual changes. To do an action on a large group of records you might want to have form that asks what records they want to update (similiar to your current search) and then lets them mark which fields they want to update and puts the new value into a field on the form. To account for all the possibilities, there might be some quite complex code to build the set-based code. That's ok, it would only have to be written once and would save a lot of the users time and so might be cost effective for the company to do. I just want you to be aware that moving from one record at a time processing to set-based processing can become very complex depending on exactly what the users need to do. There would probably also be a great deal of user re-training involved.



"NOTHING is more important in a database than integrity." ESquared