SQL Server Performance Forum – Threads Archive

Partitioned Views Problem

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 =#%[email protected]_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?? thanks

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 ?

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. Raulie

]]>