Optimization tips for table with 6 million records

Posted on 2009-02-11
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!
Question by:bryan_z
    LVL 13

    Expert Comment

    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.

    LVL 8

    Accepted Solution

    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
    LVL 57

    Expert Comment

    by:Raja Jegan R
    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now