using XML in stored procedures taking up lots of time..

[gowtham]

HI
I'm using XML in my stored procedure to pass values nto it. I put the values from the XML into a temp table and then use it. but this is taking a lot of time. what is the alternative 4 this. passing values as comma seperated string is worser than this.


CREATE TABLE #tempUoM
(intSNo int identity(1,1),txtSalesOrderNo char(10),txtProjectCode char(8),
txtActivityCode char(8),txtUoMCode char(3),txtMonthlyDays char(3),
dtValidFrom smalldatetime,dtValidTo smalldatetime,fltNWTotal numeric)

IF NOT @txtXMLUoMDates IS NULL
Begin
EXEC sp_xml_preparedocument @idoc OUTPUT, @txtXMLUoMDates
If @@Error <> 0
begin
Rollback Tran
Return
end

INSERT INTO #tempUoM
(txtSalesOrderNo,txtProjectCode,txtActivityCode,tx tUoMCode,txtMonthlyDays,dtValidFrom,dtValidTo)
SELECT * FROM OPENXML (@idoc, 'Root/Parent', 1)
WITH
(txtSalesOrderNo char(10),txtProjectCode char(8),txtActivityCode char(8),txtUoMCode char(3),txtMonthlyDays char(3),dtValidFrom smalldatetime,dtValidTo smalldatetime)

EXEC sp_xml_removedocument @idoc

If @@Error <> 0
begin
Rollback Tran
Return
end


spAlconCfGetUoMNWDays
'<Root>
<Parent txtSalesOrderNo="0000000237" txtProjectCode="2Orafin" txtActivityCode="USDVLT" txtUomCode="D1" txtMonthlyDays="24" dtValidFrom="5/1/2004" dtValidTo="5/31/2004"/>
<Parent txtSalesOrderNo="0000000237" txtProjectCode="2Orafin" txtActivityCode="AFFRLT" txtUomCode="D1" txtMonthlyDays="24" dtValidFrom="5/1/2004" dtValidTo="5/31/2004"/>
<Parent txtSalesOrderNo="0000000237" txtProjectCode="2Orafin" txtActivityCode="AFUSLT" txtUomCode="D1" txtMonthlyDays="24" dtValidFrom="5/1/2004" dtValidTo="5/31/2004"/>
</Root>

[Rawhide]

You could use a table variable rather than a temp table. Using a temp table causes the sp to recompile frequently. Also, table variables are stored in memory and not written to the disk thus alleviating disk read/writes.

How big is the XML that you are passing in? Is it like the sample you pasted, or is it much larger?

One of the problems with the way that SQL Server implements XML imports is that it loads the entire document into memory. If the document is large, it uses a lot of memory. Of course, if you are passing in the values as a parameter, then whatever you pass in will be large and have the same effect on memory.

You should find a considerable improvement if you save the values as comma delimited in a text file and then use bcp to import into the table. You can pass in the path & name of the file as a parameter and use xp_cmdshell to execute bcp.