Tip

Performing incremental inserts/updates in SQL Server

Inserts and updates are two of the most common operations in SQL Server that cause locking. Updates usually cause row-level locking, while inserts can cause a whole table to be locked, especially if the table has an explicitly-defined primary key. If your database routinely involves mass updates or inserts, it's hard to keep the locks from stopping everything cold—especially if the table in question is used often.

One way to avoid locking tables during these insert operations is to subdivide a bulk insert operation into smaller operations. Depending on how the database is accessed and managed, there are a few ways to do this: through a specially-constructed stored procedure, or through an ASP page (or some other programmatic interface). The first may be more complicated to program, but may be easier to work with, since the same sort of work wouldn't have to be redone in other contexts—in other words, no matter what programming language or interface used, you wouldn't need to rewrite the progressive-insert functionality.

With a stored procedure, the data to be inserted could be loaded into a temporary table, and then gradually inserted into the target table by using a WAITFOR command between each insert operation. At the end of the operation, provided everything inserted successfully, the temporary table can simply be dropped. By contrast, a scripted page or standalone program could simply perform the insert gradually, with a forced pause

    Requires Free Membership to View

between each record to allow the system to catch up and properly interleave other operations.

A third option is to schedule a mass insert or update for a time when the database would normally be offline for maintenance, or when there would be few users active. This could be done either through one of the mechanisms described above to prevent timeouts, or via the DTS Import Wizard if the whole thing is being done in a one-time fashion.

The records to be inserted could be subdivided in various ways. For instance, a large number of records with a time/date stamp could have each day's or month's worth of data bulk-inserted separately.

(Additionally, if it is not crucial to see the most current committed data, SELECT statements that use the tables in question can use NOLOCK or READ UNCOMMITTED to reduce the amount of locking that does take place.)


Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!


This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.