Professional Web Applications Themes

select from partitioned view, all member tables are accessed - Microsoft SQL / MS SQL Server

didi, Did you get the partitioned view working properly. Re my reply yesterday ?   index ...

  1. #1

    Default select from partitioned view, all member tables are accessed

    didi,

    Did you get the partitioned view working properly. Re my
    reply yesterday ?


     
    index 
    mike Guest

  2. #2

    Default Re: select from partitioned view, all member tables are accessed

    "didi" <com> wrote in message
    news:1b7001c3618d$e552d5c0$gbl... 

    *one* of the columns - you mean the *first* one?

    [snip]

    HTH,
    </wqw>


    Vlad Guest

  3. #3

    Default Re: select from partitioned view, all member tables are accessed

    Didi,

    I get problems with updatable partitioned views when
    1. I have a composite primary key
    2. The check constraint is unnamed.

    It gets much worse...

    Dear Vlad, Tibor, Mr. Ben-Gan, Brian Moran, BP Margolin, Erland, Tom
    Moreau and other gurus I have learned from...

    Ok, try this... execute the partition script at the bottom of this
    message.
    It creates the partitioned tables and the corresponding view. Remember
    to strip the last UNION ALL please...

    Then run an execution plan on the two statements below.
    SELECT CustomerId FROM vwCustomersXSourcesXCollections WHERE
    CustomerId IN (12, 12)
    SELECT CustomerId FROM vwCustomersXSourcesXCollections WHERE
    CustomerId = 12

    The first statement plan scans 1 table, the second all tables.
    I set statistics io on, and it claims zero scans of the other tables,
    but I am skeptical because it's execution takes longer.

    Insert some data into the view...one record suffices.
    INSERT INTO vwCustomersXSourcesXCollections
    (
    CustomerId,
    SourceCheckSum,
    CollectionId,
    Source
    )
    VALUES
    (
    12,
    CHECKSUM('dog'),
    21,
    'dog'
    )

    Run profiler and turn on SQL statement begin end, scan start stop, and
    lock acquired. Turn on duration colunm. Set filter to your SPID in
    Query yzer.

    You will see the second execution dances much longer, even though
    STATS IO claims zero scans take place. You can really see the
    difference if you rerun the partition and set last = 10,000, which
    will generate 100 tables for the partition (closer to my reality).

    Using stored procedure makes no difference, any single SARG suffers a
    performance penalty, and multiple sargs, even if the same parameter is
    used as the second SARG, performs as I would expect.

    -- begin partition creation script
    DECLARE last int
    SET last = 1000

    DECLARE gap int
    SET gap = 100

    DECLARE range int
    SET range = gap

    DECLARE crlf char(2)
    SET crlf = CHAR(13) + CHAR(10)

    PRINT 'SET QUOTED_IDENTIFIER ON' + crlf
    PRINT 'GO' + crlf
    PRINT 'SET ANSI_NULLS ON' + crlf
    PRINT 'GO' + crlf + crlf

    WHILE (range <= last)
    BEGIN
    PRINT 'CREATE TABLE appCustomersXSourcesXCollections_' +
    CONVERT(varchar,range) + crlf
    PRINT '(' + crlf
    PRINT ' CustomerId int NOT NULL ,' + crlf
    PRINT ' SourceCheckSum int NOT NULL ,' + crlf
    PRINT ' CollectionId int NOT NULL ,' + crlf
    PRINT ' Source varchar (200) NOT NULL,' + crlf
    PRINT ' CONSTRAINT PK_appCustomersXSourcesXCollections_' +
    CONVERT(varchar,range) + ' PRIMARY KEY CLUSTERED' + crlf
    PRINT ' (' + crlf
    PRINT ' CustomerId,' + crlf
    PRINT ' SourceCheckSum,' + crlf
    PRINT ' CollectionId,' + crlf
    PRINT ' Source' + crlf
    PRINT ' ) ON [COLLECTIONS],' + crlf
    PRINT ' CONSTRAINT CK_appCustomersXSourcesXCollections_' +
    CONVERT(varchar,range) + ' CHECK (CustomerId > ' +
    CONVERT(varchar,range - gap) + ' AND CustomerId <= ' +
    CONVERT(varchar,range) + ')'
    PRINT ') ON [COLLECTIONS]' + crlf
    PRINT 'GO' + crlf + crlf

    SET range = range + gap
    END

    PRINT 'GO'
    PRINT 'SET QUOTED_IDENTIFIER OFF' + crlf
    PRINT 'GO' + crlf
    PRINT 'SET ANSI_NULLS ON' + crlf
    PRINT 'GO' + crlf

    -- create the view
    -- remember to delete the last union all before executing
    SET gap = 100
    SET range = gap

    PRINT 'SET QUOTED_IDENTIFIER ON' + crlf
    PRINT 'GO' + crlf
    PRINT 'SET ANSI_NULLS ON' + crlf
    PRINT 'GO' + crlf

    PRINT 'CREATE VIEW dbo.vwCustomersXSourcesXCollections AS' + crlf
    WHILE (range < last)
    BEGIN
    PRINT 'SELECT CustomerId, SourceCheckSum, CollectionId, Source FROM
    Partitions.dbo.appCustomersXSourcesXCollections_' +
    CONVERT(varchar,range) + crlf
    PRINT 'UNION ALL' + crlf
    SET range = range + gap
    END
    PRINT 'GO'
    PRINT 'SET QUOTED_IDENTIFIER OFF' + crlf
    PRINT 'GO' + crlf
    PRINT 'SET ANSI_NULLS ON' + crlf
    PRINT 'GO' + crlf
    GO
    Privlin Guest

  4. #4

    Default Re: select from partitioned view, all member tables are accessed

    Oops:

    SELECT CustomerId
    FROM vwCustomersXSourcesXCollections
    WHERE CustomerId = 12

    vs
    the correct way:

    SELECT CustomerId
    FROM vwCustomersXSourcesXCollections
    WHERE CustomerId = CAST(12 AS INT)

    Bye,
    Delbert Glass

    "Privlin Merck" <com> wrote in message
    news:google.com... 


    Delbert Guest

Similar Threads

  1. partitioned view doesn't work!
    By mike in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 13th, 01:00 PM
  2. Updateable partitioned view problem
    By Rekha in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 31st, 01:14 PM
  3. distributed partitioned view??
    By chet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 05:34 AM
  4. Partitioned View Question
    By Jane Kelly in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 12th, 11:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139