Improve performance by partitioning

Question

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.

Answer 1

Can partitioning the table help reduce the completion time?

No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.

If you want to improve performance you need to identify the bottleneck and address them appropriately. Is it a bad plan? Missing index? Slow IO? Slow CPU? Not enough RAM? Poor code? USe a methodology like Waits and Queues to identify the bottleneck.

Answer 2

You might improve performance with partitioning. You will need to experiment on a development server and see if it is something that can benefit you.

You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

Partitioned Tables and Indexes (msdn)

Answer 3

Can partitioning the table help reduce the completion time?

Yes - if you have sufficient CPU and IO resources to achieve query parallelism and partition the table on a key that will result in a set number of partitions having a roughly equal number of rows in them at all times. This approach uses a principle of spreading the workload across the partitions much like a MPP architecture. The drawback of the approach is you lose the ability to perform fast switching of partitions for deletion of data. We have used this approach in a DB2 z/OS based billing system for 15 years, where our "orders" table is partitioned by a random account number that hashes between the fixed number of partitions (200). Our batch billing process uses query parallelism to spread workload between up to 32 worker threads and thus completing the query to bill 1/20'th of the accounts in 1/32 the time it would take if a serialized process were used on a non-partitioned table.

Almost all SQL Server advice focuses on partition elimination for query performance and fast switching to achieve quick purge of inactive data and in most cases that is appropriate. But your question is a good one as it shows that there are edge cases where a different approach to partitioning can be beneficial.