I have to get data from many tables and combine them into a single one. The final table will have about 120 millions rows. I'm planning to insert the rows in the exact order needed by the big table indexes.
我必须从许多表中获取数据并将它们组合成一个表。决赛桌将有大约1.2亿行。我打算按照大表索引所需的确切顺序插入行。
My question is, in terms of performance: Is it better create the indexes of the new table from the start, or first make the inserts and at the end of the import create the indexes ?
我的问题是，在性能方面：从一开始就更好地创建新表的索引，还是先插入并在导入结束时创建索引？
Also, would it make a difference if, when building indexes at the end, the rows are already sorted in terms of indexes specifications ?
另外，如果在最后构建索引时，行已根据索引规范进行排序，那会不会有所作为？
I can't test both cases and get an objective comparison since the database is on the main server which is used for many other databases and applications which can be heavy loaded or not on different moment of times. I can't restore the database to my local server either, since I don't have full access to the main server yet.
我无法测试这两种情况并得到客观的比较，因为数据库位于主服务器上，主服务器用于许多其他数据库和应用程序，这些数据库和应用程序可能在不同的时刻负载不重。我无法将数据库还原到本地服务器，因为我还没有完全访问主服务器。
1 个解决方案
#1
1
I suggest that copy date in first and then create your indexes. If you insert records on the table that have index, for each insert, SQL Server refresh table index. but when you create index after insert all record to your table, SQL Server don't need to refresh table index for each insert, and rebuild index one way.
我建议首先复制日期，然后创建索引。如果在具有索引的表上插入记录，则对于每个插入，SQL Server刷新表索引。但是当您将所有记录插入表后创建索引时，SQL Server不需要刷新每个插入的表索引，并以一种方式重建索引。
You can use SSIS in order to copy data from source tables to destination. SSIS use balk insert and have good performance. also if you have any trigger on destination database, I suggest that disable that before start your convert.
您可以使用SSIS将数据从源表复制到目标。 SSIS使用balk insert并具有良好的性能。如果您在目标数据库上有任何触发器，我建议在开始转换之前禁用它。
When you create index each time on your table, rows stored in terms of your index.
每次在表上创建索引时，按行索引存储的行。
#1
1
I suggest that copy date in first and then create your indexes. If you insert records on the table that have index, for each insert, SQL Server refresh table index. but when you create index after insert all record to your table, SQL Server don't need to refresh table index for each insert, and rebuild index one way.
我建议首先复制日期，然后创建索引。如果在具有索引的表上插入记录，则对于每个插入，SQL Server刷新表索引。但是当您将所有记录插入表后创建索引时，SQL Server不需要刷新每个插入的表索引，并以一种方式重建索引。
You can use SSIS in order to copy data from source tables to destination. SSIS use balk insert and have good performance. also if you have any trigger on destination database, I suggest that disable that before start your convert.
您可以使用SSIS将数据从源表复制到目标。 SSIS使用balk insert并具有良好的性能。如果您在目标数据库上有任何触发器，我建议在开始转换之前禁用它。
When you create index each time on your table, rows stored in terms of your index.
每次在表上创建索引时，按行索引存储的行。