Partitioned view, GROUP BY

  • Hey,

    You will need to add an index on the COBDate column as well, as this forms one of your filters or SARG's. In terms of the original problem with a partioned view and ability to use variables vs constant, only way I see this being possible is to use the sp_executesql strategy. Otherwise, you not be able to get a value at runtime for the optimizer, thus each partition will be queried.

    Any other thoughts?

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hey,

    You will need to add an index on the COBDate column as well, as this forms one of your filters or SARG's. In terms of the original problem with a partioned view and ability to use variables vs constant, only way I see this being possible is to use the sp_executesql strategy. Otherwise, you not be able to get a value at runtime for the optimizer, thus each partition will be queried.

    In addition, have you considered using a covering index for the various queries?

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hey,

    Ok, I have been able to re-create this issue.

    So, just be sure I understand:

    1. Your requirement is daily partitions?

    2. You will load data into each daily partion on the date concerned?

    3. You need to run query?

    If this is the case, you may want pre-create your partitioning tables and alter the view to include the new tables, prior to loading. In addition, you will need to use sp_executesql to benefit from the execution plan created as a result, as this is the only way to circumvent the all partitions lookup. In addtion, you should create a covering index on COBDATE, ACCOUNT and AMOUNT to benefit from an index seek, which will speed up query execution. I have tested this and it works.

    Please let me know your thoughts.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hey,

    Ok, I have been able to re-create this issue.

    So, just be sure I understand:

    1. Your requirement is daily partitions?

    2. You will load data into each daily partion on the date concerned?

    3. You need to run query?

    If this is the case, you may want pre-create your partitioning tables and alter the view to include the new tables, prior to loading. In addition, you will need to use sp_executesql to benefit from the execution plan created as a result, as this is the only way to circumvent the all partitions lookup. In addtion, you should create a covering index on COBDATE, ACCOUNT and AMOUNT to benefit from an index seek, which will speed up query execution. I have tested this and it works.

    Please let me know your thoughts.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hey,

    Ok, I have been able to re-create this issue.

    So, just be sure I understand:

    1. Your requirement is daily partitions?

    2. You will load data into each daily partion on the date concerned?

    3. You need to run query?

    If this is the case, you may want pre-create your partitioning tables and alter the view to include the new tables, prior to loading. In addition, you will need to use sp_executesql to benefit from the execution plan created as a result, as this is the only way to circumvent the all partitions lookup. In addtion, you should create a covering index on COBDATE, ACCOUNT and AMOUNT to benefit from an index seek, which will speed up query execution. I have tested this and it works.

    Please let me know your thoughts.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hey,

    Ok, I have been able to re-create this issue.

    So, just be sure I understand:

    1. Your requirement is daily partitions?

    2. You will load data into each daily partion on the date concerned?

    3. You need to run query?

    If this is the case, you may want pre-create your partitioning tables and alter the view to include the new tables, prior to loading. In addition, you will need to use sp_executesql to benefit from the execution plan created as a result, as this is the only way to circumvent the all partitions lookup. In addtion, you should create a covering index on COBDATE, ACCOUNT and AMOUNT to benefit from an index seek, which will speed up query execution. I have tested this and it works.

    Please let me know your thoughts.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Apologies for repeat post, formatting not correct.

  • The steps you mentioned in your previous post is exactly what I am doing on a daily basis.

    I really don't see the advantage of partitioned view as I thought their purpose was to split data into various tables and access only the sub-table needed when querying/updating/inserting.

    Basically if i use sp_executesql I don't need to have a partitioned view as I can directly hardcode my table name into the SQL statement:

    -- @COBDate_Var_yyyy_mm_dd holds the representation of date with this format yyyy_mm_dd

    SELECT @sql = 'SELECT * FROM ut_irec' + @COBDate_Var_yyyy_mm_dd

    EXEC(@SQL)

    What is the point to use horizontal partitioning in this case?

    My issue can be sum up by: how do I access in a generic way a particular range of data isolated by a constraint (date in my case)? If you see any other way of doing let me know.

  • Hi,

    This code works perfectly and hits only the required partition:

    DECLARE @COBDate SMALLDATETIME

    DECLARE @sql VARCHAR(8000)

    SELECT @COBDate = '20080208'

    SELECT @sql = 'SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = '''

    + CONVERT(varchar(255),@COBDate,112) + ''' GROUP BY COBDate,Account'

    EXEC(@SQL)

    In addition, with the covering index, you gain an index seek on appropriate table.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • Hi,

    I have a partitioned view (uv_rules) to link tables organised by date (ut_rules_2008_02_29, ut_rules_2008_02_28...).

    When I run this following query, everything is working fine, that is to say that the sql engine will directly access the right sub table:

    SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = '20080229' GROUP BY COBDate,Account

    But unfortunately when I tried to used a variable for the date the sql engine is scanning all tables and so takes more time to run:

    DECLARE @COBDate SMALLDATETIME

    SELECT @COBDate = '20080229'

    SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = @COBDate GROUP BY COBDate,Account

    Any advice is welcomed!!

    Thanks


    Below are the execution plans for the respective queries:

    1st query

    SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = '20080229' GROUP BY COBDate,Account

    |--Compute Scalar(DEFINE:([Expr1052]=CASE WHEN [globalagg1054]=(0) THEN NULL ELSE [globalagg1056] END))

    |--Parallelism(Gather Streams)

    |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]) DEFINE:([globalagg1054]=SUM([partialagg1053]), [globalagg1056]=SUM([partialagg1055]), [Union1044]=ANY([Union1044])))

    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]), ORDER BY:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account] ASC))

    |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Amount]), [Union1044]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate])))

    |--Compute Scalar(DEFINE:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate]=[SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate], [SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]=[SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]))

    |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[CI_ut_rules_2008_02_29]), ORDERED FORWARD)

    2nd query

    SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = @COBDate GROUP BY COBDate,Account

    |--Compute Scalar(DEFINE:([Expr1052]=CASE WHEN [globalagg1054]=(0) THEN NULL ELSE [globalagg1056] END))

    |--Parallelism(Gather Streams)

    |--Hash Match(Aggregate, HASH:([Union1048]), RESIDUAL:([Union1048] = [Union1048]) DEFINE:([globalagg1054]=SUM([partialagg1053]), [globalagg1056]=SUM([partialagg1055]), [Union1044]=ANY([Union1044])))

    |--Concatenation

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-05 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_05].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_05].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_05].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_05].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_05].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_05].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_05].[CI_ut_rules_2008_02_05]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_05].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-06 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_06].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_06].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_06].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_06].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_06].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_06].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_06].[CI_ut_rules_2008_02_06]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_06].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-08 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_08].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_08].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_08].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_08].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_08].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_08].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_08].[CI_ut_rules_2008_02_08]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_08].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-14 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_14].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_14].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_14].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_14].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_14].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_14].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_14].[CI_ut_rules_2008_02_14]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_14].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-18 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_18].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_18].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_18].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_18].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_18].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_18].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_18].[CI_ut_rules_2008_02_18]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_18].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-19 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_19].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_19].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_19].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_19].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_19].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_19].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_19].[CI_ut_rules_2008_02_19]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_19].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-25 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_25].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_25].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_25].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_25].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_25].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_25].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_25].[CI_ut_rules_2008_02_25]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_25].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-26 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_26].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_26].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_26].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_26].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_26].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_26].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_26].[CI_ut_rules_2008_02_26]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_26].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-27 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_27].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_27].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_27].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_27].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_27].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_27].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_27].[CI_ut_rules_2008_02_27]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_27].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-02-29 00:00:00.000')))

    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]))

    | |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_02_29].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate])))

    | |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[CI_ut_rules_2008_02_29]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_02_29].[COBDate]=[@COBDate]) ORDERED FORWARD)

    |--Filter(WHERE:(STARTUP EXPR([@COBDate]='2008-03-03 00:00:00.000')))

    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([SCB IntRec].[dbo].[ut_rules_2008_03_03].[Account]))

    |--Stream Aggregate(GROUP BY:([SCB IntRec].[dbo].[ut_rules_2008_03_03].[Account]) DEFINE:([partialagg1053]=COUNT_BIG([SCB IntRec].[dbo].[ut_rules_2008_03_03].[Amount]), [partialagg1055]=SUM([SCB IntRec].[dbo].[ut_rules_2008_03_03].[Amount]), [SCB IntRec].[dbo].[ut_rules_2008_03_03].[COBDate]=ANY([SCB IntRec].[dbo].[ut_rules_2008_03_03].[COBDate])))

    |--Clustered Index Scan(OBJECT:([SCB IntRec].[dbo].[ut_rules_2008_03_03].[CI_ut_rules_2008_03_03]), WHERE:([SCB IntRec].[dbo].[ut_rules_2008_03_03].[COBDate]=[@COBDate]) ORDERED FORWARD)

  • Hi,

    If you use a variable in the query, the optimizer can't predict the runtime value, so it won't know which partition to access, thus optimizer will include all the partitions. As you observed, when using a constant, only required partition is accessed.

    Thank you,

    Phillip Cox

    MCITP - DBAdmin

  • Thanks Phillip,

    I understand, but one of the advantage of the partitioned view is to access dynamically one table at a time. So in my case I want to create a generic stored procedure to return the result for one day only, and I want to be able to reuse the code for a different day without hardcoding anything otherwise I miss the point of using a partitioned view.

    Is there a way to force the sql engine to read only one table? It seems pretty obvious that when my constraint is on the COBDate column (so unique for each table) and when my SELECT query specifies in the WHERE clause COBDate = @COBDate I only want to access one table??

    Thanks,

  • Hi,

    You can re-write the query using sp_executesql, as using this strategy will ensure sp_executesql gets an execution plan, which will be compile with a value for the varible and hit only the partition required.

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

  • There is really no way to create user defined function to access one table at time as below? Maybe I need to add something to the WHERE clause to help the sql engine to chose the right execution plan.

    DECLARE @COBDate SMALLDATETIME

    SELECT @COBDate = '20080228'

    SELECT * FROM udf_rules(@COBDate)

    -- directly access ut_rules_2008_02_28

    SELECT @COBDate = '20080229'

    SELECT * FROM udf_rules(@COBDate)

    -- directly access ut_rules_2008_02_29

    This seems pretty strange that my only option is to hardcode the date and run sp_executesql! Thanks for the help

  • Hi,

    Try this and let me know results:

    execute sp_executesql

    N'SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = @COBDate GROUP BY COBDate,Account',

    N'@COBDate smalldatetime',

    @COBDate = '20080229'

    Thanks,

    Phillip Cox

    MCITP - DBAdmin

Viewing 15 posts - 1 through 15 (of 25 total)

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