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

Speed of Partitioned Views Expand / Collapse
Author
Message
Posted Sunday, February 10, 2002 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:26 PM
Points: 69, Visits: 35
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/njacobson/speedofdpv1.asp


Post #2569
Posted Sunday, February 10, 2002 9:53 PM
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, July 31, 2007 8:20 AM
Points: 885, Visits: 1
Hi

I am a little confused about the examples listed and the use of temp tables etc. In classic partitioned views I would do something like:

create table [partition1] <...> filegroup [disk1]
create table [partition2] <...> filegroup [disk2]
create table [partition3] <...> filegroup [disk3]

then:

create view [allpartitions] as
select * from [partition1]
union all
select * from [partition2]
union all
select * from [partition3]

etc.. and of course be "federated" if the tables are on different servers. I cant work out from your examples where you are doing this?

I did just complete a 68hr week so I am probably half asleep!

Cheers

Ck








Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #27940
Posted Wednesday, February 13, 2002 12:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:45 PM
Points: 33,264, Visits: 15,424
Interesting article. I'd like to see more information about how you came to these conclusions, but it is a good explanation of your tests and results.

Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #27941
Posted Wednesday, February 13, 2002 6:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:26 PM
Points: 69, Visits: 35
I was planning on writing another article about partitioned views to provide a guide for getting the most performance from them.

My article on the speed of partitioned views was intended to just state the facts as tested. For example I don't see any point padding out the article with definitions of member tables as these are almost identical to the original table. (Only names of tables, contstraints are different. Indexes have the same names. Check constraints added for partitioning. Also everything is in BOL.)

I gave an example of the type of script used for testing and where I modified for additional tests. I can supply all scripts if you would like but the outcomes are as stated in my article.

The outcomes illustrate the differences in execution times between original table and equivalent partitioned view where number of records in each are 975000000.

The bottom line with local partitioned views is that you need to specify the range of the partitioning column in order to get speed performance improvements.

I hope this will assist people who may be interested in LPV as a solution to certain problems with very large tables.

Regards
Neil Jacobson




Post #27942
Posted Saturday, April 12, 2003 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 11, 2005 5:16 AM
Points: 47, Visits: 1
I'm also a little confused about examples.
I think it will be more interesting to rewrite all examples with indexed view (possible in 2K)
Hint will be awailable, speed of insert/update/delete is less significant in my case.




Post #27943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse