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.
-
Just be aware, that when you split your data and indexes on 2 different files, your table will become read-only as soon as you lose your index file. Is your primary key clustered? Clustering on guid is not the best solution!– DenisTApr 21, 2015 at 12:18
Add a comment
|
1 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 Xpath
https://msdn.microsoft.com/en-us/library/ms191497.aspx and https://msdn.microsoft.com/en-us/library/bb934097.aspx
-
Is there any way to create multiple files and load specific data in to the files with out partitioning? Apr 21, 2015 at 9:21
-
you can always use 'poor mans partitioning' create a set of tables all with the same indexes and then create a view on top of those which selects the relevant columns from the tables with union all. You can also index the view as well so you can use different schema for inserting the data and then show the 'correct' table with the view.– SpörriApr 21, 2015 at 9:25