SQL Server XML Performance Tips

SQLXML 2.0 offers three options to use XML documents to modify a SQL Server database. They include OPENXML, Updategrams, and Bulk Load. Each of these have their own performance-related pros and cons.

As you might guess, the best performance is offered by using Bulk Loads, assuming that it will work for you. If you need to insert very large numbers of XML documents as defined by a mapping schema, then Bulk Loads can be very efficient. But in many instances you can't use the Bulk Load option because it is only limited to INSERTs, not other types of data modifications.

The next most efficient way to use XML documents to modify a SQL Server database is to use the Updategrams option. Updategrams are more efficient that OPENXML because all XML parsing and SQL statement generation is not performed by SQL Server, but in the client or middle-tier. Like Bulk Loads, Updategrams can't be used in all instances. Updategrams usually require a mapping schema and also require that XML input be in a special format, which may or may not be practical.

The least efficient, but most flexible way to modify SQL Server data using XML documents, is to use OPENXML. The performance problem is a result that an OPENXML stored procedure is parsed into a DOM by a parser that runs in the SQL Server process, and uses SQL Server memory until it is released. While OPENXML works OK for low-volume applications, it does not scale well. [2000] Added 5-9-2002

*****

Here are some ways you can help to maximize the performance of Updategrams:

• If you use the sql:key-fields annotation, be sure you select a primary key for each table. If you don't, all columns in the before element are included in the WHERE clause, and can cause the search to take much longer than necessary.
• If you are going to update or delete data a record, and you don't care what the before value is, you can help boost performance of Updategrams by not including all the columns included in the before element in the WHERE clause.
• If an Updategram is used to make unrelated updates, the updates should be split into separate synch blocks. This reduces the transaction size, and helps to boost performance.

[2000] Added 5-9-2002

*****

The "Advanced" tab of the "Configure SQLXML Support in IIS" configuration dialog box has a check box to turn "caching" on and off. When on (the default option), allows Mapping Schemas to be cached and used over and over, helping to boost overall performance. Don't turn this option off, unless you are in a development or testing environment. [2000] Added 5-9-2002