I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.
Can partitioning the table help reduce the completion time?
If so, can I partition an empty table by order year and then insert into the orders table from the CTE. Or will I need to create a partition on the items table since the CTE is rolled up from the items table?
Also my estimated number of rows differ significantly from the actual number of rows.