Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Index
Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
| |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB