REQ: Bulk XML Update and Insert Performance Advice

23 Mar 2006 3:03 PM

Mark S.

Hello,

I've been asked to rebuild an app. The app in question is a real time stats
engine. It uses C# to loop over each stat and send it to a SQL Server 2000
SP3 stored procedure. There the stat is parsed into it's different parts and
stored in 15 different tables using UPDATE and INSERTs.

Needless to say as usage has increased performance has gone down. I was
thinking about having C# transform the array of stats into XML and send it
to a stored procedure, never writing it to file. When I looked into the
different ways of writing the stored procedure, I discovered so many options
my head began to spin...

So, I turn to this group asking which is the best way to deal with BULK XML
INSERT UPDATES, keeping in mind the need for performance and data
manipulation .

1) Microsoft suggests:
http://support.microsoft.com/default.aspx?scid=kb;en-us;315968
CREATE PROC sp_UpdateXML @empdata nText AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata

--This code updates old data.
UPDATE Employee
SET
   Employee.FirstName = XMLEmployee.FirstName,
   Employee.LastName = XMLEmployee.LastName
FROM OPENXML(@hDoc, 'NewDataSet/Employee')
       WITH (EmployeeId Integer, FirstName varchar(100), LastName
varchar(100)) XMLEmployee
WHERE    Employee.EmployeeId = XMLEmployee.EmployeeId

--This code inserts new data.
Insert Into Employee
SELECT   EmployeeId, FirstName, LastName
    FROM       OPENXML (@hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
XMLEmployee
Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)

EXEC sp_xml_removedocument @hDoc
GO

2) SQLXMLBulkLoad COM object, SQLXML 3.0 Bulk Load, TextCopy.exe, BCP and/or
DTS

3) HOW TO: Update SQL Server Data by Using XML Updategrams
http://support.microsoft.com/default.aspx?scid=kb;en-us;316018

4) Upgrade to SQL Server 2005 (seriously, if there's a feature in this
release that solves the problem, I'd like to consider it.)

Thank you in advance,

Rick

23 Mar 2006 3:37 PM

David Browne

Show quote

"Mark S." <ma***@yahoo.com> wrote in message
news:eaEO1roTGHA.5108@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I've been asked to rebuild an app. The app in question is a real time
> stats engine. It uses C# to loop over each stat and send it to a SQL
> Server 2000 SP3 stored procedure. There the stat is parsed into it's
> different parts and stored in 15 different tables using UPDATE and
> INSERTs.
>
> Needless to say as usage has increased performance has gone down. I was
> thinking about having C# transform the array of stats into XML and send it
> to a stored procedure, never writing it to file. When I looked into the
> different ways of writing the stored procedure, I discovered so many
> options my head began to spin...
>
> So, I turn to this group asking which is the best way to deal with BULK
> XML INSERT UPDATES, keeping in mind the need for performance and data
> manipulation .
>
> 1) Microsoft suggests:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;315968
> CREATE PROC sp_UpdateXML @empdata nText AS
> DECLARE @hDoc int
> exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
>
> --This code updates old data.
> UPDATE Employee
> SET
>   Employee.FirstName = XMLEmployee.FirstName,
>   Employee.LastName = XMLEmployee.LastName
> FROM OPENXML(@hDoc, 'NewDataSet/Employee')
>       WITH (EmployeeId Integer, FirstName varchar(100), LastName
> varchar(100)) XMLEmployee
> WHERE    Employee.EmployeeId = XMLEmployee.EmployeeId
>
> --This code inserts new data.
> Insert Into Employee
> SELECT   EmployeeId, FirstName, LastName
> FROM       OPENXML (@hdoc, '/NewDataSet/Employee',1)
> WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
> XMLEmployee
> Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)
>
> EXEC sp_xml_removedocument @hDoc
> GO
>
>
> 2) SQLXMLBulkLoad COM object, SQLXML 3.0 Bulk Load, TextCopy.exe, BCP
> and/or DTS
>
> 3) HOW TO: Update SQL Server Data by Using XML Updategrams
> http://support.microsoft.com/default.aspx?scid=kb;en-us;316018
>
> 4) Upgrade to SQL Server 2005 (seriously, if there's a feature in this
> release that solves the problem, I'd like to consider it.)
>

XML support in SQL Server 2005 is much improved. You don't have to use the
sp_xml_preparedocument, OPENXML, sp_xml_removedoucment any more. Instead
just convert the document to an XML type and query against it with XQuery.

David