When should I be concerned about transaction size?

Question

I have a feature where we need to merge user records. The user chooses a user to keep and and a user to discard.

We have 59 relations to user records in our database so for each one I need to do:

UPDATE (Table) set UserNo=(userToKeep) WHERE UserNo=(userToDiscard)

and then DELETE the userToDiscard and their user prefs (118).

Should I be worried about the transaction size? This is MS-SQL 2005.

Is there anything I could do?

Thanks

Answer 1

It depends on how large your user table is, and what indexes you have in place.

Answer 2

Have you tested how long the process actually takes? How often are users merged?

If you have indexes on the user ID in each of this table (and I would think that would be the natural thing to do anyway) then even with 59 tables it shouldn't take too long to perform those updates and deletes. If you only actually merge users a couple times a week then a little blip like that shouldn't be an issue. At worst, someone has to wait an extra couple seconds to do something once or twice a week.

Another option would be to save these user merge requests in a table and do the actual work in a nightly process (or whenever "off-hours" is for your application). You would need to make it clear to the users of your application though that merges do not take effect immediately. You would also need to account for a lot of possible contingencies: what if the same user is set to merge with two different users that night, etc.

Answer 3

Merging users does not sound like feature that would be used very often. Given that, there's 98% probability you shouldn't worry about transaction size (remaing 2% reserved for possible deadlocks)

Answer 4

Generally transactions should be the smallest size that they need to be to minimize contention and possible deadlock situations. (Although making them too small can cause overhead as well) Would the queries that go against these tables give incorrect results if some of the rows were changed first and others later? Depending on your application, this could cause a business problem.

Any idea how many rows will be updated in each table? If each user could have millions of rows in a table, you might need to be more careful than if there are a handful of rows in each table.