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.
