MSSQLTips

Hi,

 I've a table with about 15,379,759 records in the past six months.

 I only maintain a data of about last six months in this table & move earlier part to another table.

Now with a growing database this table too increasing manifold & its getting very time-consuming to retrieve a few set of records say a week-wise consolidated data for last one month as the query has to go all the way up & down several times.

So I wanted to know a better way to organize this table. I don't want to break the table into too many parts.

 Any suggestions would be welcome either to somehow improve table response time or, ways to improve query or, any other method.

Thanks

Ankit Mathur 

Ankit Mathur,

In SQL Server 2005 partitions are available to split the data in a single table to multiple independent sets of disk to improve the IO performance of the query as well as logically separate the data.  This might be a good first set of technology to look into to see if this will resolve the issue.

Here is a tip on SQL Server 2005 partitions - http://www.mssqltips.com/tip.asp?tip=1200.

If this is not helpful, please provide some additional information related to the queries and disk layout to see what else we can to do help you out.

Thank you,
The MSSQLTips.com Team

Hi,

Thanks for the reply.

But I'm working on SQL Server 2000 & need a solution for the same problem in SQL Server 2000.

My OS is Windows Server 2003

I'm sorry I forgot to mention this beforehand.

Would appreciate if any fellow member can guide me to a solution.

Ankit Mathur

Ankit,

Here are a few options to consider:

• View - You could create a view to serve as a level of abstraction between your programming code and the physical table location.  If you have enough spindles you could create file groups on each disk drive and then have your table split across each of the file groups on the different disk drives.  You may need a few different physical sets of disks to see the IO benefit.
• Split data from non-clustered indexes - If you have the disk drives, split your table and clustered index on one set of disks and then place your non-clustered indexes in another file group on another set of disk drives.  You may need less physical disks than the first option to see some IO benefit.
• Code logic - If you do not have the spindles to separate your database or your indexes, you could have the most recent data in 1 table (the last week) and then duplicate the data in the large table.  For the queries that only need the recent data they could query the small table with the last week's worth of data and for the larger queries they could query the larger table.  This probably will not resolve the IO problem, but it might improve the queries that only need to access the last week's worth of data.
• Index review - Review your indexes to ensure all of the needed indexes are created and maintained.
• Code review -  Review your query plans to see if you can improve the plan at all.

Let us know if this is helpful or not.

BTW - VLDB's are always interesting challenges.  If you have other problems and solutions you have crafted they would probably make for valuable tips for the remainder of the community.

Thank you,
The MSSQLTips.com Team