• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Optimization tips for table with 6 million records

I currently have a SQL Server 2005 database whose table contains receipt information. Currently the table contains several million records. A basic select query for a specific row takes a bit of time. There is an index on the table.

My question is this, is there anything that can be done, in general to improve the performance of this table and similar systems in SQL Server 2005.  Anything regarding specific ways to establish an index, database configuration changes, etc.

I understand this is a bit vague but I am looking for one or more ideas that I can apply to the database in general.

Thanks for the help!
1 Solution
You could look into Table Partitioning; splitting the table into physical years as it were.

Not a quick implement and takes some thinking/planning; however could be beneficial with that many rows.

Your indexes depends on the kind of query to make it faster. if you are not sure what kind of index to create check the execution plan. it will give you some suggestions in GREEN color.
You can also consider partitioning you table . here is a good article
Raja Jegan RSQL Server DBA & ArchitectCommented:
Please find below some of my approaches:

1. Table partitioning.
2. Archiving of old records into another table if it is not required by user queries.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now