February 28, 2017 at 10:11 am
Hello Friends,
This is regarding Partitioning of a very large table(88 million rows and 25 columns). And this data is for 350 different products. I have a clustered index on ProductId. I just need to build a report which does a select operation(may be joins) on this table …also no DML operations need to be performed on this table…
First of all I am using SQL server 2008 R2 Standard edition(No Enterprise edition) so table partitioning is not available as far as I know.
So I am thinking of utilizing the partitioned view approach by having 35 tables and each table having 35 product’s data in each. My product list does not grow rapidly may be 1 a month….
Is my approach correct? Or is there any other better approach available?
Regards,
abi
February 28, 2017 at 10:20 am
You'll save yourself and future-you a lot of headache by developing an analysis cube and reporting from it rather.
It should be faster, sleeker and less admin-overhead than dividing into views, maintaining performance.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 28, 2017 at 10:24 am
abhisheka.dalal - Tuesday, February 28, 2017 10:11 AMHello Friends,This is regarding Partitioning of a very large table(88 million rows and 25 columns). And this data is for 350 different products. I have a clustered index on ProductId. I just need to build a report which does a select operation(may be joins) on this table …also no DML operations need to be performed on this table…
First of all I am using SQL server 2008 R2 Standard edition(No Enterprise edition) so table partitioning is not available as far as I know.
So I am thinking of utilizing the partitioned view approach by having 35 tables and each table having 35 product’s data in each. My product list does not grow rapidly may be 1 a month….Is my approach correct? Or is there any other better approach available?
Regards,
abi
Explain to me the need for partitioning here. With a clustered index on ProductID you will only hit the products you need when you access the table. And 88M rows is NOT a very large table. I could run that on my daughter's laptop, and she is 11 years old. 🙂
Also, I think you meant 10 tables with 35 products each, which equals the 350 products you have?
I am also curious about why ProductID is the primary column you are concerned with and which is the clustered index key. This sounds like an "orders" type of table with that many rows for that few products. If so, my experience has shown that reporting is normally done by date (and/or customer). If that is the case you need to go back to the drawing board regarding indexing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 28, 2017 at 10:39 am
TheSQLGuru - Tuesday, February 28, 2017 10:24 AMabhisheka.dalal - Tuesday, February 28, 2017 10:11 AMHello Friends,This is regarding Partitioning of a very large table(88 million rows and 25 columns). And this data is for 350 different products. I have a clustered index on ProductId. I just need to build a report which does a select operation(may be joins) on this table …also no DML operations need to be performed on this table…
First of all I am using SQL server 2008 R2 Standard edition(No Enterprise edition) so table partitioning is not available as far as I know.
So I am thinking of utilizing the partitioned view approach by having 35 tables and each table having 35 product’s data in each. My product list does not grow rapidly may be 1 a month….Is my approach correct? Or is there any other better approach available?
Regards,
abiExplain to me the need for partitioning here. With a clustered index on ProductID you will only hit the products you need when you access the table. And 88M rows is NOT a very large table. I could run that on my daughter's laptop, and she is 11 years old. 🙂
Also, I think you meant 10 tables with 35 products each, which equals the 350 products you have?
I am also curious about why ProductID is the primary column you are concerned with and which is the clustered index key. This sounds like an "orders" type of table with that many rows for that few products. If so, my experience has shown that reporting is normally done by date (and/or customer). If that is the case you need to go back to the drawing board regarding indexing.
Thank you for correcting me there ..yes i meant 10 tables with 35 products in each.
Also my reporting is done on product id and not Date.
February 28, 2017 at 10:41 am
Henrico Bekker - Tuesday, February 28, 2017 10:20 AMYou'll save yourself and future-you a lot of headache by developing an analysis cube and reporting from it rather.
It should be faster, sleeker and less admin-overhead than dividing into views, maintaining performance.
thank you for the suggestion but i am not very well versed in ssas cubes...
February 28, 2017 at 11:31 am
abhisheka.dalal - Tuesday, February 28, 2017 10:41 AMHenrico Bekker - Tuesday, February 28, 2017 10:20 AMYou'll save yourself and future-you a lot of headache by developing an analysis cube and reporting from it rather.
It should be faster, sleeker and less admin-overhead than dividing into views, maintaining performance.thank you for the suggestion but i am not very well versed in ssas cubes...
And as such you would have only a slim hope of success pursuing it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply