I have a table with 2 billion rows of which one column contains XML data. Actually am writing a SQL to extract data from the XML column ad populate in to another table. it really takes lot of time.. processing 3k rows per second. My XML query has about 30 columns mostly extracting data with xpath statements like below. Am using SQL server 2012 standard edition, so no table partitioning is possible.
SELECT Id, a1 = CASE SortOrder WHEN 0 THEN Contents.value('(/a/Code)[1]', 'varchar(255)') ELSE NULL END, a2 = CASE SortOrder WHEN 1 THEN Contents.value('(/a/Code)[1]', 'varchar(255)') ELSE NULL END, a3 = Contents.value('(/b/c)[1]', 'varchar(255)'), a4 = Contents.value('(/d/e/f[@category="aa"]/@categoryDate)[1]', 'datetimeOffset'), a5 = Convert(varchar(max),STUFF((SELECT ',' + OTH.value('(.)', 'varchar(150)') FROM Contents.nodes('/d/e/f[@category="g"]/SelectedCategories/SelectedCategory') AS Node(OTH) FOR XML PATH('')), 1, 1, '')) + ' & ' + ISNULL(Contents.value('(/d/e/f[@category="g"]/Freeform)[1]', 'varchar(255)'), ' ' ) FROM tbl
The query resides in SP and called from SSIS data flow task. I have added "Option MAXDOP 1" to avoid multiple threads.
Hardware is Intel Xenon CPU with 4 cores and 32 GB RAM. DFT has 70 MB buffer size and 12000 rows as buffer rows. Table is around 60 GB
- Moved by Olaf HelperMVP 22 minutes ago Moved from "Database Engine" to a more specific forum for a better response