Speed of Partitioned Views

  • neiljacobson

    SSCommitted

    Points: 1717

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

  • ckempste

    SSCoach

    Points: 17983

    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: 719720

    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

    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

    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.

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

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