Speed of Partitioned Views

Question

Speed of Partitioned Views

neiljacobson

SSCommitted

Points: 1717
More actions
February 10, 2002 at 12:00 am

#151662

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/njacobson/speedofdpv1.asp

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ckempste SSCoach Points: 17983 More actions · Answer 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"

Steve Jones - SSC Editor SSC Guru Points: 736524 More actions · Answer 2

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

neiljacobson SSCommitted Points: 1717 More actions · Answer 3

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

Yukas Mr or Mrs. 500 Points: 521 More actions · Answer 4

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.