partitioned view goofiness

ok, i have a partitoned view of 5 years worth or
partitioned monthly data. each undertable has a check
constraint on a date column
when i run this I get a proper execution plan (note i am
reviewing the actual execution plan, not the estimated)
select * from viewname
where datecol = cast('20041101' as datetime)
but when I run either of these, my execution plans show I
hit all 60 undertables (I tried with cast AND convert and
get the same)
declare @.dt datetime
set @.dt = '20041101'
select * from viewname
where datecol = @.dt
declare @.dt datetime
set @.dt = cast('20041101' as datetime)
select * from viewname
where datecol = @.dt
what am I doing wrong? I want to put the date as a
parameter in a stored procedure but the results are
terrible because the server hits all 60 undertables.
Help pleaseHi
Although it is not the same example as documented in "The Guru's Guide to
SQL Server Architecture and Internals" by Ken Henderson ISBN 0-201-70047-6
goes does talk about query plans for partitioned views and he shows that
although you may see the other partitions in the plan they should have 0
executions.
John
"Buzzer" <anonymous@.discussions.microsoft.com> wrote in message
news:062801c53953$76239780$a501280a@.phx.gbl...
> ok, i have a partitoned view of 5 years worth or
> partitioned monthly data. each undertable has a check
> constraint on a date column
> when i run this I get a proper execution plan (note i am
> reviewing the actual execution plan, not the estimated)
> select * from viewname
> where datecol = cast('20041101' as datetime)
> but when I run either of these, my execution plans show I
> hit all 60 undertables (I tried with cast AND convert and
> get the same)
> declare @.dt datetime
> set @.dt = '20041101'
> select * from viewname
> where datecol = @.dt
> declare @.dt datetime
> set @.dt = cast('20041101' as datetime)
> select * from viewname
> where datecol = @.dt
>
> what am I doing wrong? I want to put the date as a
> parameter in a stored procedure but the results are
> terrible because the server hits all 60 undertables.
> Help please
>|||Sorry John, that is not the case. Each undertable
contributes a healthy percentage to the overall
performance of the execution plan and the query. Yes as
you mentioned, the rows are zero, but each has a cost.
The problem I believe revolves around the datetime issue.
When I compare the actual execution plans of using "cast
('20041101' as datetime)" versus "@.dt" in the where
clause, the difference is about 90% to 10% where the 90%
is the for the variable @.dt.
Maybe someone from Microsoft can help. Anyone?

>--Original Message--
>Hi
>Although it is not the same example as documented in "The
Guru's Guide to
>SQL Server Architecture and Internals" by Ken Henderson
ISBN 0-201-70047-6
>goes does talk about query plans for partitioned views
and he shows that
>although you may see the other partitions in the plan
they should have 0
>executions.
>John
>"Buzzer" <anonymous@.discussions.microsoft.com> wrote in
message
>news:062801c53953$76239780$a501280a@.phx.gbl...
I
and
>
>.
>|||On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
<anonymous@.discussions.microsoft.com> wrote:
>Sorry John, that is not the case. Each undertable
>contributes a healthy percentage to the overall
>performance of the execution plan and the query. Yes as
>you mentioned, the rows are zero, but each has a cost.
>The problem I believe revolves around the datetime issue.
>When I compare the actual execution plans of using "cast
>('20041101' as datetime)" versus "@.dt" in the where
>clause, the difference is about 90% to 10% where the 90%
>is the for the variable @.dt.
>Maybe someone from Microsoft can help. Anyone?
Well, it's not "data sniffing" correctly.
I wonder if you put each of the cases into a separate stored procedure
and do your query plans and comparisons, if that would work better.
Josh|||[Sorry about responding to the reply, but the original is not
on msnews.microsoft.com]
The "show execution plan" percentages are estimates, so unless
you have confirmed the 90%/10% figure you quote with real
timings and table access, it's meaningless.
Also, you say "each has a cost", but the fact is that "show execution
plan" does not show actual costs. It only shows estimated cost.
The actual cost of access to tables that are not accessed is probably
zero.
If you have *actual* costs to show there is a difference here, as
opposed to estimated costs, let us know. Estimated costs are not
something you need to worry about.
The reason you see this is fairly simple. The optimizer
can figure out which table contains '20041101', so it shows
the specific table that will be accessed.
When you use @.dt, the optimizer still knows the first thing to do
is find out what table to access, but the optimizer doesn't evaluate
@.dt itself - it creates a plan where the first step is to choose the
correct table. Most likely, the only real difference in cost here
is that a tiny bit of the work (picking the table) is done by the
optimizer in one case and the query processor in the other. More
than likely, you will have the same actual cost in each case.
Just don't assume that the costs and percentages in "show
[actual] execution plan" are for real. The only actual values
this shows are for Rowcount and Executes. The rest are
the same estimates you get with estimated execution plan.
Steve Kass
Drew University
JRStern wrote:

>On Tue, 5 Apr 2005 05:20:05 -0700, "Buzzer"
><anonymous@.discussions.microsoft.com> wrote:
>
>Well, it's not "data sniffing" correctly.
>I wonder if you put each of the cases into a separate stored procedure
>and do your query plans and comparisons, if that would work better.
>Josh
>
>