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!
Bryan
bryan_zAsked:
Who is Participating?
 
k_rasuriConnect With a Mentor 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
0
 
St3veMaxCommented:
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.