I have written a number of Stored Proc's that use the OpenXML function in SQL 2K. The XML Doc's that are used in the procedures will contain between 500 and 3500 rows worth of information that get used to do both Mass Inserts (Insert Into ...), and Updates of numerous other tables. The DB Size is currently about 1 gb and the primary tables involved contain 550,000 and 1.1 million rows respectively. These tables do contain clustered indexes. My problem is that the Stored Procedures are taking far too long to complete at this point in addition to the fact that they are currently adversely impacting performance.

I am really trying to understand what my options are at this point to increase performance. This DB will be queried periodically via an ASP Application, and I am worried about the performance. I understand that partitioning the Table may be an alternative, but I hesistate to do this since my query times are very adequate. The only time I seem to have problems is when we are loading these large XML Documents. The process of loading these documents is typically done at various times throughout the day and they will typically vary size and volume depending on client field activity.

Is BizTalk more efficient? Are Stored Procs the way to go? I have heard about a new XML Bulk Load, but it appears that this is only able to load to one table and only insert, Is this correct?

-- Anonymous, April 08, 2002

Answers

Dave,

Sorry for the delay in answering. I was on vacation in Ireland through the 24th.

Consider using updategrams. They were designed as a higher scalability alternative to OpenXML.

If you use parameterized updategrams and have caching enabled, then SQLXML will cache the processed updategram (and schemas) and SQL Server will cache the parameterized SQL queries. You should observe great performance benefits in this case.

For large inserts, XML Bulk Load will provide better performance than anything else other than raw SQL Bulk Load (within 15% of the performance of raw SQL bulkload). XML Bulk Load is designed to deal with multiple tables.

For mass inserts, you may obtain better performance if you drop the indexes and created later.

You might want to take a look at a great article:

SQL Server XML and Web Application Architecture http://msdn.microsoft.com/library/techart/d51webapparch.htm

Hope this helps,

Eric

-- Anonymous, April 25, 2002