Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Views and Horizontal Portioning Expand / Collapse
Author
Message
Posted Wednesday, March 21, 2007 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:54 AM
Points: 195, Visits: 80
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjambu/2936.asp
Post #353020
Posted Thursday, April 12, 2007 7:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:04 PM
Points: 469, Visits: 264
A good overview of views.  One area I would caution on the use of horizonal partitioning - if your views encompasses a lot of tables (60+ in our case) and you are joining to another similar view, the optimizer can give some non-optimal plans.  I believe this is because the optimizer can only evaluate a certain number of tables in the time it has to determine the best plan.  To get around this we have some smaller views that only look at 12 tables at a time (one table per month) and write queries based on that.  This has helped some queries that never finished before being killed to become queries that would run in less than a minute.
Post #357831
Posted Thursday, April 12, 2007 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 22, 2009 11:43 AM
Points: 1, Visits: 26
I realize you tried to limit this article to the use of VIEWS and Horizontal Partitioning but you should have expanded the title to include TABLE PARTITIONING.  By using TABLE Partitioning you don't have to deal with maintaining a VIEW to access the partitioned data.  Otherwise, very good overview on Partitioned Views.


Post #357843
Posted Thursday, April 12, 2007 8:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 7, 2010 1:38 AM
Points: 1, Visits: 5
What for writing about partitioned views if it is old mechanism? Now is partitioned tables! And partitioned views work not so correctly as possible
Post #357888
Posted Thursday, April 12, 2007 9:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:09 PM
Points: 977, Visits: 277
Table partitions are new to SQL Server 2005. Many companies still use SQL Server 2000, so it is still worth discussing partitioned views.




Post #357910
Posted Thursday, April 12, 2007 9:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,259, Visits: 2,029
Tables partitioning are not *that* great either. Be really careful before you jump into that, thinking that "peformance" is its primary feature, it is *not*.

In addition when replication is involved the "switch" clause won't work, so you end up goin back to partitioned views

Cheers,




* Noel
Post #357923
Posted Thursday, April 12, 2007 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 199, Visits: 307

Not sure if a discussion about table partitioning versus partitioned views is relevant to the content of the article.  Having said that, I have recently been involved in a situation where both methods were employed in a very large operational data store used to service both online transactional application lookup, as well as daily analysis in several three dimensional cubes.

The point is there is a place for both strategies.

This is directly from a Microsoft whitepaper addressing partitioning strategies.

In SQL Server 2005, queries against partitioned tables can use a new operator called demand parallelism. Demand parallelism is influenced by the system resources and MAXDOP settings.

Queries that use partitioned tables will compile much faster than equivalent queries that use partitioned views. Query compilation time is proportional to the number of partitions when using partitioned views, while compilation is not affected by partition count when using partitioned tables.

Queries against partitioned views might perform better in some cases. The following are examples of such cases:

•The smallest unit of parallelism, when demand parallelism is chosen by the optimizer, is a partition. The performance of queries against a single partition in a SQL Server 2005 partitioned table might suffer because the degree of parallelism is limited to one. The same query against a partitioned view might perform better because of better parallelism within a partition.
 
•Partitioned views might perform better when the number of partitions is less than the number of processors because of better use of the available processors through parallelism. When the number of partitions is greater than the number of processors, the performance of queries against partitioned tables can still suffer if the data is not evenly distributed across the partitions.
 
•Queries against partitioned views might perform better when there is a skew in the distribution of data among the partitions.

In other words, one method may or may not be the best way to achieve partitioning over another.  Each method has its benefits and drawbacks.  For example, with table partitioning you are physically limited to 1000 partitions.  Partitioned views have no such limitation.  In the case of a large data warehouse, its entirely possible to have more than 1000 physical partitions on a given dimension.  For example, a date dimension could very well be split into more than 1000 partitions...

You can even have the situation where BOTH methods may be the course of action (partitioned view on a table partition).  Now I don't even know if this is possible, but for simplicity's sake, we won't make anyone's brain bleed right now.  The simple fact of the matter is, the best way is the way that fits your specific need.  And you don't get there without testing.  Testing itself can be a huge struggle as well, given the volume and specific type of data you may be dealing with.  The goal is, as always should be, to make the smallest possible change with the maximum beneficial return.




Post #358020
Posted Friday, April 13, 2007 2:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2007 7:59 AM
Points: 2, Visits: 2
Good information for a newbie. Good work.
Post #358395
Posted Saturday, April 21, 2007 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:23 PM
Points: 2,907, Visits: 1,830
Don't forget that when you do schema changes to underlying tables your should do sp_refreshview on the dependent views.

It is worth mentioning the WITH CHECK option. If you have an updateable view with a WHERE clause in it the option prevents you inserting data that would conflict with the WHERE clause


LinkedIn Profile
Newbie on www.simple-talk.com
Post #360132
Posted Tuesday, March 25, 2008 5:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Overall it is very decent article. Chers to the author. :)


Post #473944
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse