Optimizing a Stored Procedure

Congratulations to Anatole V. Farci, a senior software engineer in Portland, Oregon, and Joshua Grant, a database developer for WestJet in Calgary, Alberta, Canada. Anatole won first prize of $100 for the best solution to the October Reader Challenge, "Optimizing a Stored Procedure." Joshua won second prize of $50. Here's a recap of the problem and the solution to the October Reader Challenge.

Problem:

Tom, the database developer for a company that sells its products online, has a database that contains customer information and order details. For simplicity, assume that the database is similar to SQL Server 2000's Northwind sample database. The following script creates the sample data that we can use to solve Tom's problem:

USE NorthwindGOSELECT * INTO _Customers FROM CustomersSELECT * INTO _Orders FROM OrdersALTER TABLE _Customers ADD PRIMARY KEY(CustomerId)ALTER TABLE _Orders ADD PRIMARY KEY(OrderId), FOREIGN KEY (CustomerId) REFERENCES _Customers(CustomerId)GO

Tom wants to optimize the stored procedure ProcessCustomerOrders, which accepts an XML stream and processes the customer orders. The Web application code invokes the stored procedure, which performs the following actions:
1. Adds new customers
2. Adds new orders for customers
3. Updates contact information for existing customers
4. Updates shipping details for existing orders

Listing 1 shows how the ProcessCustomerOrders stored procedure uses the sample tables. The Web application can contain any number of orders (up to 100) when it invokes the stored procedure. While monitoring database performance under heavy load conditions, Tom notices that the connections that call the stored procedure consume a lot of memory on the server. He also notices that under heavy-stress conditions, some of the calls fail, which indicates that the XML document couldn't be created successfully.

Tom needs to improve the stored procedure's overall performance and retain the XML input capability. How can Tom modify the stored procedure to avoid the memory-consumption problem? And should Tom check any environment-related problems to prevent the call failures from happening frequently?

Solution:

Tom improves the stored procedure's efficiency by minimizing the frequent OPENXML calls. Because OPENXML is DOM-based, the XML's internal representation is kept in memory until Tom calls sp_xml_removedocument to release the XML document handle that sp_xml_preparedocument returns. Tom first uses OPENXML to retrieve the data he needs, stores it in temporary tables, and releases the XML handle. The whole process ensures that the memory the XML document uses is released quickly and is available for SQL Server. As part of creating the temporary table, Tom also modifies the XPath expression for the Orders element to make it fully qualified and selects only the columns that he requires for the transaction. The stored procedure in Listing 2 incorporates these changes.

Upon investigation, Tom finds out that the SQLXML 2.0 version on the SQL Server machine is old and contains several allocation bugs that Microsoft fixed in a later release. So, Tom updates the SQLXML 2.0 installation to fix the bugs and reduce memory-related errors. Listing 3 shows a sample usage of the stored procedure to demonstrate how it works.

NOVEMBER READER CHALLENGE:

Now, test your SQL Server savvy in the November Reader Challenge, "Preventing Deletes" (below). Submit your solution in an email message to [email protected] by October 21. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Hank, a database programmer, designs and maintains the human resources department's SQL Server 2000 database. Hank wants to prevent deletions from specific audit tables that contain sensitive data. He's already denied DELETE permissions on the tables to all users. What else can Hank do to prevent accidental deletions from the tables? The solution must prevent DELETE statements from removing rows from the tables. Assume that the database contains a table called EmployeesArchive that's similar to the Employees table in the Northwind database. The following script contains the audit table the problem requires.

USE northwindGOSELECT * INTO EmployeesArchive FROM EmployeesGO