| Author |
 Topic  |
|
|
pradiptakghosh
Starting Member
India
2 Posts |
 Posted - 10/31/2012 : 08:45:19
|
We have a table Structure in our database which holds the Country, Section, Hourly Ad-request Data.
Structure is similar to CountryID(int), SectionID(int), Hour(int), TotalRequest(bigint).
On per day basis we are currently accumulating 75,000 new rows in the table.
Now our client wants to break-down the data at City Level.
Structure will be CityID(int), SectionID(int), Hour(int), TotalRequest(bigint).
After analysis we found it will increase no. of rows 400 time/day basis. (ie. 30000000 new rows/day (approx.)
So it is practically impossible to put it in a single table. What is the best possible solution in case of table design?
We are using SQL Server 2008 Standard edition.
Thanks & Regards,
Pradipta Ghosh |
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 10/31/2012 : 09:30:41
|
| 30 million rows a day is certainly possible to put into one table. Or you could use multiple tables and create a partitioned view over them. If you can upgrade to Enterprise Edition you could use table partitioning, which overall is probably the best solution. |
 |
|
|
pradiptakghosh
Starting Member
India
2 Posts |
Posted - 10/31/2012 : 10:48:48
|
quote:
Originally posted by robvolk
you could use multiple tables and create a partitioned view over them.
The problem is we are using SQL Server 2008 Standard edition. and client is reluctant to buy Enterprise edition. So table partitioning is not possible in this moment.
Could you please tell me in detail about partitioned view.
Thanks & Regards,
Pradipta Ghosh |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 10/31/2012 : 11:57:22
|
There are details here, also read the related links: http://msdn.microsoft.com/en-us/library/ms190019.aspx
This information is also in SQL Server Books Online. The important consideration for maximum performance is the CHECK constraint, that is required in order for the query optimizer to do proper partition elimination. There are other limitations on INSERTing into a partitioned view as well.
Regarding Enterprise Edition costs, frankly if they're doing this kind of scale of data they can afford it. There are many other performance enhancements in Enterprise Edition as well. If they plan on any kind of growth they're likely going to have to get it anyway. |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1765 Posts |
Posted - 10/31/2012 : 12:36:31
|
1) As an alternative method - can this data be archived?. It is relatively straightforward to create multiple tables in different filegroups. Could you run a nightly batch job migrating the data to timepartitioned tables?
2) Is all the data required all time - or will it be summarised?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
| |
Topic |
|
Table Design in case of High data volume
