We help IT Professionals succeed at work.

Optimization tips for table with 6 million records

Medium Priority
Last Modified: 2012-05-06
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!
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.