SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Table Design in case of High data volume
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradiptakghosh
Starting Member

India
2 Posts

Posted - 10/31/2012 :  08:45:19  Show Profile  Reply with Quote
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
15678 Posts

Posted - 10/31/2012 :  09:30:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

India
2 Posts

Posted - 10/31/2012 :  10:48:48  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 10/31/2012 :  11:57:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 10/31/2012 :  12:36:31  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000