Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ankit Mathur
Starting Member
24 Posts |
 Posted - 2007-11-01 : 03:39:03
|
| 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.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.I have a nonclustered, unique, primary key located on PRIMARY IDMay I also add that this is already an archive table. whereby everday last day's entry from the main table is inserted so as to enable us to maintain a decent performance for the live data from main table. Any data that has to be accessed later than a day would require me to come to this table.Another thing I'd like to add is that I don't only require this table for fetching month wise reports.Even single record may have to be taken out based on classification parameters. Its actually maintaining all the call records for a user.So user specific call record on a date are also used.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, design modification or, any other method. I hope I've provided sufficent information and am hoping that somebody could guide me towards a proper way to increase the table's response time.Ankit Mathur |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-11-01 : 05:00:50
|
| If your queries are "date driven", then put an index on the date. You could also look at (vertically) partitioned tables, where each weeks data is in a different partition - this will cause you a long term maintenance problem (you will need to keep adding partitions)- but may be a smaller one that you currently have. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 06:27:41
|
| "You could also look at (vertically) partitioned tables, where each weeks data is in a different partition"Wouldn't that be "horizontal" partitioning?"I have a nonclustered, unique, primary key located on PRIMARY ID"Why is it NON clustered? Do you have a clustered index on something else? If not create one (by converting the most index you currently have to Clustered)Do you do routine maintenance on the table? (Reindex/Defrag and Update statistics? If not add that. Consider using FULLSCAN for the Update Statistics, instead of just using the default sample size.Kristen |
|
|
|
Ankit Mathur
Starting Member
24 Posts |
Posted - 2007-11-02 : 02:19:03
|
| You are right Kristen,I too am thinking of adding a Clustered Index on the DateTime field as that's what most of the queries are based upon.Secondly, as for maintenance jobs on this table. I haven't been doing any on a routine basis but about once in a while I do use DBCC ReindexIs there any other useful command that I can routinely employ to increase the response time?BTW how would Update Statistics be useful in my context ?ThanksAnkit Mathur |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 04:15:02
|
"Is there any other useful command that I can routinely employ to increase the response time?"Check the query plan and see how the query is being performed, and then consider whether different indexes, or an index that better "covers" the query, would help.Make sure the indexes are Reindexed (or Defrag) regularly."BTW how would Update Statistics be useful in my context ?"IF the statistics are out of date them the query planner will be making wrong assumptions, and e.g. using an index which actually performs worse than it expected - or not using an index which could provide better performance Kristen |
|
|
|
|
|
|
|
|
How to increase table response time