Professional Web Applications Themes

Partitioned View Question - Microsoft SQL / MS SQL Server

I have a partitioned view on Orders data...there are twelve orders tables...one for each month of the year...the partitioning is done on the order date column. Everything works fine when my query contains an order date...the performance is excellent. If I try to run a query using only the Order ID (the Order Date is not part of the query) the performance is terrible. The Order ID is an identity column and I have an index on this column (it is not the primary key). This is what I have found so far. Currently only 3 of the 12 monthly ...

  1. #1

    Default Partitioned View Question

    I have a partitioned view on Orders data...there are twelve orders
    tables...one for each month of the year...the partitioning is done on
    the order date column. Everything works fine when my query contains an
    order date...the performance is excellent. If I try to run a query
    using only the Order ID (the Order Date is not part of the query) the
    performance is terrible. The Order ID is an identity column and I have
    an index on this column (it is not the primary key).

    This is what I have found so far. Currently only 3 of the 12 monthly
    Orders tables have data in them. When my partitioned view references
    all 12 tables it can take 20 minutes to perform the query using Order
    ID. When my view contains only the 3 tables that have data in them the
    query runs in 3 seconds...same query, same database, same server. I
    don't want to keep changing my view every month to add another table
    to it. I'd like to set my view up for the whole year's worth of Orders
    tables and let it do its thing.

    Why would the addition of empty tables to the view cause such a
    degradation in performance?

    TIA for any information.
    Jane Kelly Guest

  2. #2

    Default Re: Partitioned View Question

    Hi Jane,

    Don't use an orders table per month. Use one orders table, and record the
    month on the order record.

    One problem that you are likely having is the OrderId identifies a different
    row depending on what table it is on, in effect you don't have an OrderId
    with this schema, you have a JanOrderId, FebOrderId, etc.

    Creating 12 tables is a really, really, bad idea.

    John.

    "Jane Kelly" <jkellytaltrade.com> wrote in message
    news:bc2c5a63.0307111318.bc63cf7posting.google.co m...
    > I have a partitioned view on Orders data...there are twelve orders
    > tables...one for each month of the year...the partitioning is done on
    > the order date column. Everything works fine when my query contains an
    > order date...the performance is excellent. If I try to run a query
    > using only the Order ID (the Order Date is not part of the query) the
    > performance is terrible. The Order ID is an identity column and I have
    > an index on this column (it is not the primary key).
    >
    > This is what I have found so far. Currently only 3 of the 12 monthly
    > Orders tables have data in them. When my partitioned view references
    > all 12 tables it can take 20 minutes to perform the query using Order
    > ID. When my view contains only the 3 tables that have data in them the
    > query runs in 3 seconds...same query, same database, same server. I
    > don't want to keep changing my view every month to add another table
    > to it. I'd like to set my view up for the whole year's worth of Orders
    > tables and let it do its thing.
    >
    > Why would the addition of empty tables to the view cause such a
    > degradation in performance?
    >
    > TIA for any information.

    John Elliot Guest

  3. #3

    Default Re: Partitioned View Question

    Jane,

    It would help if you posted the table and view structure, including indexes,
    and the query you are using. checking with query yser what the execution
    plan is spending all it's time on should help too.

    Are you sure you created a "partitioned " view - you did add all the constraints ?
    I'm a bit puzzled about how you use OrderID - If it is an identity on each table
    then it repeats each month ? How then do you sensibly query the view on it without
    also querying on date? Is it's index used in the query ? How many rows per table ?
    Have you compared the execution times and query plans with differing numbers of
    empty tables in the query?

    Unlike John Elliot I have found partitioned views on dates very useful and performant,
    and great for rolling archives, but my queries almost always included the partioning
    condition, so I have not seen this problem.

    Regards
    AJ

    p.s. Disguising your email address to prevent spam getting out of control is a good idea
    when posting.

    "Jane Kelly" <jkellyDELETETHIStaltrade.com> wrote in message
    news:bc2c5a63.0307111318.bc63cf7posting.google.co m...
    > I have a partitioned view on Orders data...there are twelve orders
    > tables...one for each month of the year...the partitioning is done on
    > the order date column. Everything works fine when my query contains an
    > order date...the performance is excellent. If I try to run a query
    > using only the Order ID (the Order Date is not part of the query) the
    > performance is terrible. The Order ID is an identity column and I have
    > an index on this column (it is not the primary key).
    >
    > This is what I have found so far. Currently only 3 of the 12 monthly
    > Orders tables have data in them. When my partitioned view references
    > all 12 tables it can take 20 minutes to perform the query using Order
    > ID. When my view contains only the 3 tables that have data in them the
    > query runs in 3 seconds...same query, same database, same server. I
    > don't want to keep changing my view every month to add another table
    > to it. I'd like to set my view up for the whole year's worth of Orders
    > tables and let it do its thing.
    >
    > Why would the addition of empty tables to the view cause such a
    > degradation in performance?
    >
    > TIA for any information.


    Andrew John Guest

Similar Threads

  1. default view question.
    By Peter in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: December 15th, 01:43 PM
  2. View % question
    By Kathryn_Bernstein@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: October 4th, 12:47 PM
  3. view database question
    By Gonzosez in forum ASP Database
    Replies: 4
    Last Post: March 2nd, 07:13 PM
  4. Partitioned views
    By Amrit in forum IBM DB2
    Replies: 2
    Last Post: August 25th, 07:06 PM
  5. Resizing the partitioned C Drive??
    By Bob Pearce in forum Windows Setup, Administration & Security
    Replies: 1
    Last Post: July 23rd, 08:14 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