We help IT Professionals succeed at work.

Optimization tips for table with 6 million records

Medium Priority
531 Views
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!
Bryan
Comment
Watch Question

Commented:
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.

HTH
Commented:
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
http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

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
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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.

OR

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.