1

I would like to speed up this Insert. If I comment out the insert line then the query takes a few seconds, but if when I add the insert, it appears to be only processing about 100 rows per minute. How do I speed this up?

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = XMLData FROM InventoryStockXML WHERE id =1  --1st row
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

;With xmlnamespaces (default 'http://www.epicor.com/Mfg/100')
INSERT INTO dbo.xmltest (PartNum,WarehouseCode,OnhandQty,Date_Value,Plant)
Select
    N.value('PartNum[1]', 'varchar(50)') PartNum,
    N.value('WarehouseCode[1]', 'varchar(10)') WarehouseCode,
    N.value('OnhandQty[1]', 'decimal(22,8)') OnhandQty,
    Convert(date,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)) AS Date_Value,
    @xml.value('(/ReportDataSet/Plant/Plant)[1]', 'varchar(20)') Plant
from 
    @xml.nodes('/ReportDataSet/StkPart') as T(n)

EXEC sp_xml_removedocument @hDoc --takes it out of memory
GO

Here is the test file I am using: http://wikisend.com/download/637998/Test.XML

Below is how the code to load the file:

DECLARE @SQL1 NVARCHAR (MAX),@xmlFileName1 VARCHAR(300)
select  @xmlFileName1 = 'C:\XMLInventoryFiles\Stock\Test.XML'
set @sql1 ='
INSERT INTO InventoryStockXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS XMLData, GETDATE() XMLDate
FROM OPENROWSET(BULK ''' + @xmlFileName1 + ''', SINGLE_BLOB) AS x'
execute (@sql1)
Kevin
  • 298
  • 1
  • 4
  • 22
  • Does ``dbo.xmltest`` have many indexes or triggers? Are other users acessing that table at the same time? What happens if you replace it with a table variable? Does that speed it up? – acfrancis Oct 31 '13 at 21:23
  • Are you on SqlServer2008? Take a look here ([link](http://stackoverflow.com/a/3979266/2523686)), may be you have similar problem. – i-one Oct 31 '13 at 22:55
  • @i-one Holy Cow, thank you! This is indeed the problem, however I am on 2008 R2 SP1. What gives? It it said to be fixed on that version. When I change to a Select into, it only take seconds to run. If you submit the answer, I will mark it as complete. – Kevin Nov 01 '13 at 13:17

0 Answers0