I have a table that has 220 M rows. Also the table has a xml column that has XML data. I have configured single file for data and another ndf for index. Table's primary key is guid column. When am querying the XML contents using xpath query and load the flatten XML data to another table it takes around 30 hrs to load the data. Since am in standard edtition, partition is not an option. Need some advice on same
1. how to improve data load performance.
2. I have a non-clustered index configured on the table which will be rebuilt after load.
Sql-server – How to optimise XPath Querying over a large dataset
performancesql serverxml
Related Question
- Sql-server – Does Clustered Index on GUID create more fragmentation than Non Clustered Index
- Sql-server – partitioning a table on a column that does not belong to the current clustered index
- Sql-server – How to return a null from an empty XML tag in SQL server using Xpath only
- Sql-server – Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns
- Sql-server – How to improve execution time for queries by multiple fields of different data types on a single table
- Sql-server – If PK is Non-Clustered index, how to identify clustered index candidate
Best Answer
Create an XML index on the column, you will have to create a primary index and then a secondary index on the path or value you are querying.
CREATE PRIMARY XML INDEX PXML_NAME ON SCHEMA.TABLE (XMLCOLUMN);then create a secondary index for the { VALUE | PATH | PROPERTY } you are querying with Xpathhttps://msdn.microsoft.com/en-us/library/ms191497.aspx and https://msdn.microsoft.com/en-us/library/bb934097.aspx