Speeding Up Inserts and Updates with Bulk Operations

You can perform bulk operations, such as multiple-record insert, update, and delete operations, in one of two ways: You can use an SQL pass-through query or you can use the Microsoft Jet query processor. The Microsoft Jet query processor provides additional functionality, but can be somewhat slower than the SQL pass-through method for bulk operations.

The difference between using the Microsoft Jet query processor and an SQL pass-through query is in the way Microsoft Jet prepares for a bulk operation. When executing a bulk operation through its own query processor, Microsoft Jet builds a keyset for all records that will be affected by the operation. Then, the engine executes the operation for each record in the keyset, one record at a time.

The benefits of using the Microsoft Jet query processor are:

• It allows for partially successful bulk queries.
  
  
• It allows you to perform some bulk operations that may not be available on the remote database server.

The only drawback is the speed of the operation. You have to determine if the advantages offered by the Microsoft Jet query processor outweigh the possible performance penalties. If you find that performance is your highest goal, consider using pass-through queries.

See Also For information on SQL pass-through queries, see “Using SQL Pass-Through Queries” earlier in this chapter.