Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael