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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Table Design in case of High data volume

Author  Topic 

pradiptakghosh
Starting Member

2 Posts

Posted - 2012-10-31 : 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

15732 Posts

Posted - 2012-10-31 : 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.
Go to Top of Page

pradiptakghosh
Starting Member

2 Posts

Posted - 2012-10-31 : 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
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-31 : 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.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-31 : 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
Go to Top of Page
   

- Advertisement -