Sq server 2008 partition versus partitioned view performance comparison

I have a database on a SQL Server 2008 standard installation that contains a table of over two hundred million rows.  I split the data into many smaller tables, one for each year since 2006 and created a partitioned view. (This is how you do partitioning in sql 2000)

The result was a huge improvement in performance over using a single table when querying the data for a single month (and therefore consequently in a single partition) but would I have gained much more using an enterprise edition of SQL Server and doing the partition properly? I decided to test the theory.

So I set up a copy of the db on a SQL 2008 R2 enterprise box  using the old style partitioning view and another copy of the db using the 2008 style partitioned table with partition function and schema. Each table had an index in the partitioned view so I also created an index in the partition file group on the new style.

One immediately obvious difference is that the new way of doing things is so much easier to manage from both a setup and maintenance perspective but what about performance?

I created a query in a single window with the query plan on and copied and pasted it with just the view reference changed to the partitioned table.

e.g.

SELECT x,aggregate(y) from view where date between a and b

SELECT x,aggregate(y) from table where date between a and b

I ran it and checked the query plans were identical (they were) both performing an index seek and returning identical data but the time split was 81% versus 19% in favour of the new methodology. A lot more than I had expected.

A resounding success to the proper way of doing things with 4.25 times the performance!

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s