Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views and Horizontal Portioning


Views and Horizontal Portioning

Author
Message
Jambu Krishnamurthy
Jambu Krishnamurthy
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 98
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjambu/2936.asp
DBA_Rob
DBA_Rob
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 336
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.
dewashington
dewashington
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 29
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.



Alexey Tikhomirov
Alexey Tikhomirov
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
vadba
vadba
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 407
Table partitions are new to SQL Server 2005. Many companies still use SQL Server 2000, so it is still worth discussing partitioned views.



noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
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
Jeremy Brown
Jeremy Brown
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 310

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.





gajjela
gajjela
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Good information for a newbie. Good work.
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3678 Visits: 3115
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
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Overall it is very decent article. Chers to the author. Smile



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search