> Hello group,
>    I'm considering creating a Partitioned view on a Sales table that
> contains up to 20 Millions records.  I'm doing some test and have a
> result I'm questionning when looking at the Execution Plan generated.
>
> First, let me explain a bit my structure.  In my Sales table, I have
> Ex-Factory sales information and Externally provided information.
> There distinguised using an identifier (0=Ex Factory, 1=External).
>
> I've created 8 Tables
>    SalesTSA1999            Check Constraint (Origin=1 And BilledDate
> BETWEEN '19990101' And '19991231')
>    SalesTSA2000            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20000101' And '20001231')
>    SalesTSA2001            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20010101' And '20011231')
>    SalesTSA2002            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20020101' And '20021231')
>    SalesXFactory1999    Check Constraint (Origin=0 And BilledDate
> BETWEEN '19990101' And '19991231')
>    SalesXFactory2000    Check Constraint (Origin=0 And BilledDate
> BETWEEN '20000101' And '20001231')
>    SalesXFactory2001    Check Constraint (Origin=0 And BilledDate
> BETWEEN '20010101' And '20011231')
>    SalesXFactory2002    Check Constraint (Origin=0 And BilledDate
> BETWEEN '20020101' And '20021231')
> and a view that include all theses tables using a UNION ALL:
> viewSalesPartitioned
>
> Then, I'm issuing the following Query:
> Select Sum(Amount)
> From viewSalesPartitioned
> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No
> worry about Time, it's all set to 00:00:00
>
> When looking at the Execution Plan in Query Analyzer I can see the
> execution plan is "almost" getting the perfect query.  I mean, on
> each table I can see a "Filter Cost 0%" Except for SalesXFactory2002
> where there's a table scan with a cost of 6%.  In my opinion it
> should do a filter like on the other tables based on the check
> constraint on the column Origin.  Maybe there's something I don't
> understand properly (I'm just starting using the Execution Plan
> analysis which is sometimes a bit confusing for me...) Any help would
> be appreciated.   Thanks!

> Christian Hamel wrote:
>> Hello group,
>>    I'm considering creating a Partitioned view on a Sales table that
>> contains up to 20 Millions records.  I'm doing some test and have a
>> result I'm questionning when looking at the Execution Plan generated.
>>
>> First, let me explain a bit my structure.  In my Sales table, I have
>> Ex-Factory sales information and Externally provided information. There
>> distinguised using an identifier (0=Ex Factory, 1=External).
>>
>> I've created 8 Tables
>>    SalesTSA1999            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '19990101' And '19991231')
>>    SalesTSA2000            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20000101' And '20001231')
>>    SalesTSA2001            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20010101' And '20011231')
>>    SalesTSA2002            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20020101' And '20021231')
>>    SalesXFactory1999    Check Constraint (Origin=0 And BilledDate
>> BETWEEN '19990101' And '19991231')
>>    SalesXFactory2000    Check Constraint (Origin=0 And BilledDate
>> BETWEEN '20000101' And '20001231')
>>    SalesXFactory2001    Check Constraint (Origin=0 And BilledDate
>> BETWEEN '20010101' And '20011231')
>>    SalesXFactory2002    Check Constraint (Origin=0 And BilledDate
>> BETWEEN '20020101' And '20021231')
>> and a view that include all theses tables using a UNION ALL:
>> viewSalesPartitioned
>>
>> Then, I'm issuing the following Query:
>> Select Sum(Amount)
>> From viewSalesPartitioned
>> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No
>> worry about Time, it's all set to 00:00:00
>>
>> When looking at the Execution Plan in Query Analyzer I can see the
>> execution plan is "almost" getting the perfect query.  I mean, on
>> each table I can see a "Filter Cost 0%" Except for SalesXFactory2002
>> where there's a table scan with a cost of 6%.  In my opinion it
>> should do a filter like on the other tables based on the check
>> constraint on the column Origin.  Maybe there's something I don't
>> understand properly (I'm just starting using the Execution Plan
>> analysis which is sometimes a bit confusing for me...) Any help would be
>> appreciated.   Thanks!
>
> Indexing on a character or bit value that allows only two values will
> normally not add any performance benefit because the column selectivity is
> too low. You might be better off using the date as the patitioning column
> without the Origin. You could also try adding a hint to force SQL Server
> to use the clustered index.
>
> I would also change the check constraints to avoid any possibility of a
> time portion triggering an error:
> Check Constraint (Origin=1 And BilledDate >= '20000101' And BilledDate <
> '20010101')
>
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

"Christian Hamel" <chamel@NoSpAmMeRaLlOwEd.CoM> wrote in message
news:O%23AEf7exFHA.3720@TK2MSFTNGP11.phx.gbl...
> Hello group,
>    I'm considering creating a Partitioned view on a Sales table that
> contains up to 20 Millions records.  I'm doing some test and have a result
> I'm questionning when looking at the Execution Plan generated.
>
> First, let me explain a bit my structure.  In my Sales table, I have
> Ex-Factory sales information and Externally provided information.  There
> distinguised using an identifier (0=Ex Factory, 1=External).
>
> I've created 8 Tables
>    SalesTSA1999            Check Constraint (Origin=1 And BilledDate
> BETWEEN '19990101' And '19991231')
>    SalesTSA2000            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20000101' And '20001231')
>    SalesTSA2001            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20010101' And '20011231')
>    SalesTSA2002            Check Constraint (Origin=1 And BilledDate
> BETWEEN '20020101' And '20021231')
>    SalesXFactory1999    Check Constraint (Origin=0 And BilledDate BETWEEN
> '19990101' And '19991231')
>    SalesXFactory2000    Check Constraint (Origin=0 And BilledDate BETWEEN
> '20000101' And '20001231')
>    SalesXFactory2001    Check Constraint (Origin=0 And BilledDate BETWEEN
> '20010101' And '20011231')
>    SalesXFactory2002    Check Constraint (Origin=0 And BilledDate BETWEEN
> '20020101' And '20021231')
> and a view that include all theses tables using a UNION ALL:
> viewSalesPartitioned
>
> Then, I'm issuing the following Query:
> Select Sum(Amount)
> From viewSalesPartitioned
> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No worry
> about Time, it's all set to 00:00:00
>
> When looking at the Execution Plan in Query Analyzer I can see the
> execution
> plan is "almost" getting the perfect query.  I mean, on each table I can
> see
> a "Filter Cost 0%" Except for SalesXFactory2002 where there's a table scan
> with a cost of 6%.  In my opinion it should do a filter like on the other
> tables based on the check constraint on the column Origin.  Maybe there's
> something I don't understand properly (I'm just starting using the
> Execution
> Plan analysis which is sometimes a bit confusing for me...)
> Any help would be appreciated.   Thanks!
>
>

>
> "Christian Hamel" <chamel@NoSpAmMeRaLlOwEd.CoM> wrote in message
> news:O%23AEf7exFHA.3720@TK2MSFTNGP11.phx.gbl...
>> Hello group,
>>    I'm considering creating a Partitioned view on a Sales table that
>> contains up to 20 Millions records.  I'm doing some test and have a
>> result
>> I'm questionning when looking at the Execution Plan generated.
>>
>> First, let me explain a bit my structure.  In my Sales table, I have
>> Ex-Factory sales information and Externally provided information.  There
>> distinguised using an identifier (0=Ex Factory, 1=External).
>>
>> I've created 8 Tables
>>    SalesTSA1999            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '19990101' And '19991231')
>>    SalesTSA2000            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20000101' And '20001231')
>>    SalesTSA2001            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20010101' And '20011231')
>>    SalesTSA2002            Check Constraint (Origin=1 And BilledDate
>> BETWEEN '20020101' And '20021231')
>>    SalesXFactory1999    Check Constraint (Origin=0 And BilledDate BETWEEN
>> '19990101' And '19991231')
>>    SalesXFactory2000    Check Constraint (Origin=0 And BilledDate BETWEEN
>> '20000101' And '20001231')
>>    SalesXFactory2001    Check Constraint (Origin=0 And BilledDate BETWEEN
>> '20010101' And '20011231')
>>    SalesXFactory2002    Check Constraint (Origin=0 And BilledDate BETWEEN
>> '20020101' And '20021231')
>> and a view that include all theses tables using a UNION ALL:
>> viewSalesPartitioned
>>
>> Then, I'm issuing the following Query:
>> Select Sum(Amount)
>> From viewSalesPartitioned
>> Where Origin=1 AND BilledDate BETWEEN '20020101' AND '20021231' --No
>> worry
>> about Time, it's all set to 00:00:00
>>
>> When looking at the Execution Plan in Query Analyzer I can see the
>> execution
>> plan is "almost" getting the perfect query.  I mean, on each table I can
>> see
>> a "Filter Cost 0%" Except for SalesXFactory2002 where there's a table
>> scan
>> with a cost of 6%.  In my opinion it should do a filter like on the other
>> tables based on the check constraint on the column Origin.  Maybe there's
>> something I don't understand properly (I'm just starting using the
>> Execution
>> Plan analysis which is sometimes a bit confusing for me...)
>> Any help would be appreciated.   Thanks!
>>
>>
> Partitioned views only support a single partitioning column.
>
> So the check constraints should be just on the date.  And in the query
> plan you shouldn't even see the other partitions.  What you're currently
> seeing is not true partition elimination.  The query is just hitting each
> PK index and quickly eliminating the partitions which don't contain the
> that column.
>
> Partitioned views allow the data in a large table to be split into smaller
> member tables. The data is partitioned between the member tables based on
> ranges of data values in one of the columns. The data ranges for each
> member table are defined in a CHECK constraint specified on the
> partitioning column.
> http://msdn2.microsoft.com/ms248875
>
> If you add an index on Origin, or perhaps add Origin as well as BilledDate
> to your primary key, you should be able to eliminate the table scan.  You
> will get partition elimination to aviod hitting the other time period, but
> normal index seek behavior to eliminate the other Origin.
>
> David
>
>
>