Views and Horizontal Portioning
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjambu/2936.asp
-
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.
-
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.
-
What for writing about partitioned views if it is old mechanism? Now is partitioned tables! And partitioned views work not so correctly as possible
-
Table partitions are new to SQL Server 2005. Many companies still use SQL Server 2000, so it is still worth discussing partitioned views.
-
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 -
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.
-
Good information for a newbie. Good work.
-
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
-
Overall it is very decent article. Chers to the author. 🙂
-
Good article.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Aren't partitioned views being deprecated anyway? I'm pretty sure I read somewhere where they are being deprecated in 2005 or 2008, but can't find where I read that so I don't have a link. But it is good to know that the partitioned view won't be an option after SQL Server 2000.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply