0

I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records.

I currently do a complete drop and reload. I've tested just shredding and inserting new records, but haven't seen much of an improvement (tried left outer join and not exists and except). I use xquery and nodes(), use text(), use cross apply; the source table does not have a clustered index so I cannot create an XML index (vendor table that I cannot modify). Is there a faster way to get this finished in less than 3 hours?

The query is basically this:

WITH XMLNAMESPACES(DEFAULT 'blahblahblah')
INSERT INTO mytable
SELECT
  ID,
  Value1.Field1.value('element1/text())[1]','varchar(255)') AS myfieldvalue1
  Value2.Field1.value('element2/text())[1]','varchar(255)') AS myfieldvalue2
FROM my table
CROSS APPLY mytable.data.nodes('//path1') AS Value1(Field1)
CROSS APPLY mytable.data.nodes('//path2') AS Value2(Field2)

I can't really post a sample of the XML, but it is big for some records.