Index or Partition

[dhulbert]

I have a few tables of over 50 Million rows which obviously take a while to query.

From a performance point of view will a query run faster on a table that it partitioned of indexed.

For instance Financial year is a regular report variable would I be better with an index on FinYear or a table partitioned by FinYear?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

What version of SQL Server are you using?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhulbert]

We're running 2008R2

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

Here's my take on the situation...

I assume you are asking this question because you are experiencing performance problems. A table with 50 million rows is not *necessarily* large. Under certain conditions, you could query this table in a blink of an eye, or it could take an excruciating long time.

Personally, I would tackle this problem first by analyzing the queries that use the table. Some of the queries may already be fast, but some are likely to be slow. You should examine the execution plans for each of those queries and determine if any of them are doing table scans or index scans. If they are, it's likely you can fix your performance problems by altering your indexes or creating new indexes for the table.

If your existing queries are already performing index seeks or clustered index seeks and you are still experiencing performance problems, then your next step should be to investigate table partitioning. Partitioning improved a lot since the old days of SQL2000, so it is not nearly so difficult to implement. Of course, partitioning will likely cause you more maintenance in the long run, which is why I suggest you attempt to fix your problems with better indexes (if possible).

On a table with 50 million rows, you need to be careful about changing and/or adding indexes. Indexes will slow down operations like inserts, updates, and deletes, but will speed up select queries. In some situations, indexes can also speed up inserts, updates and deletes, depending on the query itself. You should also realize that every index you add will cause your database files to become larger, so you will need to consider the database size as well.

Bottom line, attempt to fix your problem with indexes because the maintenance is easier, otherwise, fix your problem with partitioning.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom