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 |
|
ElArZ
Starting Member
1 Post |
 Posted - 2011-01-12 : 04:18:21
|
| Hi,I have a call accounting software which just saves a history of call made and received into a database.My problem is that i have only 1 table for outgoing calls and 1 table for incoming calls. But sometimes these tables get way too big (millions of records) and they become slow to query when generating reports.I can try archiving data but i need to be able to generate reports on archived data as well as new data and sometimes i even have millions of records per month so even if i archive every 3 months, i still have a lot of data.My solution was to split the outgoing and incoming tables into 12 tables each, representing the 12 months.So depending on the call date, i insert the call into the corresponding table and if i need to generate a report for 1 month, then i only have to query 1 table so 1/12 of the data.Do you think this solution will give better performance? Any other methods i can use?Thank you. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-01-12 : 04:22:46
|
| Use table partitioning.You can create 12 different partition in 12 different filegroup.More info refer to the link belowhttp://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspxPBUH |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-12 : 07:38:48
|
| Have you tried indexing the date column(s)? Millions of records are not a lot for SQL Server to handle as long as the table is indexed correctly. |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-01-12 : 07:53:23
|
| You should show an example of the slow query, and generate the script for your main indexes. |
|
|
|
|
|
|
Database structure optimization