SQL Server Performance

Partitioned Views Problem

Discussion in 'Analysis Services/Data Warehousing' started by pablo, Jun 30, 2006.

  1. pablo New Member

    Hello everybody

    We are having an issue with some queries running on a partitioned view in sql 2000.

    Queries run fine with the query plan only selecting the correct partition to execute against; however when the query contains a variable instead of a hardcode value the query scans all the partitons?

    select a
    from the_view
    where id_month = 200601
    ----------OK -----------

    DECLARE @p_month INT
    SET @p_month = 200601

    select a
    from the_view
    where id_month = @p_month
    ---------- KO -----------

    Our first solution was to make the code dynamic:
    EXEC ‘select a
    from the_view
    where id_month =#%92+@p_month

    But our query is more complex and longer and we have problems executing big queries with the Execute command.

    Does anybody have any other solution??

  2. gus New Member

    It has to do the way the query optimizer works.

    Take a look at this excelent paper.

    It always baffled me why with hardocoded values the query optimizer did a better job than with variables.
    Until I read that paper.

    Can't you use a SP with the parametrized id_month ?
  3. Raulie New Member

    Graphical execution plans for partitioned views can be misleading at first glance. It may appear that the query is scanning all partitions but in reality it is not. Look at the "Number of Executions" property in the plan it should be 0 for all partitions except the 200601 partition. SQL Server uses the same plan at run time to avoid recreating a new Auto Parameterized Execution Plan. At run time the Startup Filters in the plan are what SQL uses to determine which partition to go after.


Share This Page