Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users inserting or updating data infrequently.

Now, I already have a stored procedure that I use to update the rows in the source table, one row at a time. It would be much simpler to include the MERGE logic in this stored procedure instead of having a separate stored procedure to move everything at once.

Given the usage, I don't see this being an issue, but I was curious what kind of overhead or problems can arise from using MERGE and HOLDLOCK for rows one at a time.

share|improve this question
How long does your MERGE statement take to run? Unless your hardware is a 286, it should finish in a fraction of a second, and your users will never see a blip. – Jon of All Trades Jun 13 '12 at 16:34
I haven't tested it yet, but I doubt it would take long. I'm somewhat new at this and using a lock on single transactions instead of batching them seemed like something that could be a bad practice. So, I decided to ask before I implemented anything. – Eugene M Jun 13 '12 at 16:55
Locking in a big transaction makes sense for some use cases. If all your statements are interdependent (say the last update won't make sense unless the first is also done) then you should leave them as a group. – JNK♦ Jun 13 '12 at 17:00

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.