Batching Inserts

One of the most critical elements of a multiuser application is efficient use of the network. After you convert to client/server architecture, all processing is no longer performed on a local computer with Microsoft® Access. The fastest client/server applications optimize for fewer network roundtrips.

Think of the network as a bottleneck that takes a performance hit every time it is used. Instead of updating record #1 and then updating record #2, send one message to the server that updates both records at the same time. For example, you need to change the discount rate of these three customers because of a new contract. Instead of:

UPDATE customer SET discount = 10 WHERE CustomerID = 5

  

and then

UPDATE customer SET discount = 10 WHERE CustomerID = 15

  

and then

UPDATE customer SET discount = 10 WHERE CustomerID = 72

  

look for opportunities to batch updates together. Use:

UPDATE customer SET discount = 10 WHERE CustomerID = 5 OR 15 OR 72

  

Now you have only one statement and one network hit. This query is three times faster than the first three queries.

If you always update two records as a set, batch them together. Instead of:

INSERT orders (ID, qty) VALUES ('AAA', 17)

  

and then

INSERT RunningTotal(ID,qty) VALUES ('AAA', qty + 17)

  

use a stored procedure to do both at the same time. Use logic in the stored procedure on the server to update both tables. Here is a sample stored procedure:

CREATE  PROC sp_NewOrder

(@CustID Char(3), @QtyIn INT)

AS

INSERT orders (ID, qty)

VALUES (@CustID, @QtyIn)

  

INSERT RunningTotal(ID,qty)

VALUES (@CustID, @QtyIn)

  

When it is time to send the record, use a pass-through query:

sp_NewOrder('AAA',17)

  

Now you are sending 21 characters on one network trip instead of 79 characters on two network trips.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.