Partitioned view perfomance problem

Hi,
Working with partitioned views in SQL Server 2000, I have come across a
performance problem.
I have two different price tables with similar set of 1.5M records for two
different clients.
Create table Table1
(
Col1 int not null,
Col2 nvarchar(200),
ColN int not null check(ColN=1)
)
GO
Alter table Table1 WITH CHECK
Add constraint pk_Table1_Constraint PRIMARY KEY CLUSTERED
(
Col1,
ColN
)
GO
Create table Table2
(
Col1 int not null,
Col2 nvarchar(200),
ColN int not null check(ColN=2)
)
GO
Alter table Table2 WITH CHECK
Add constraint pk_Table2_Constraint PRIMARY KEY CLUSTERED
(
Col1,
ColN
)
GO
Create view View1
AS
Select Col1, Col2, ColN From Table1
UNION all
Select Col1, Col2, ColN From Table2
--1
Create Procedure Procedure1
@.par1 int
As
Select * from View1 where ColN=@.par1
Go
Create Procedure Procedure2
@.par1 int
As
Declare @.p nvarchar(4000)
set @.p=N'Select * from View1 where ColN=' + CAST(@.par1 as varchar(10))
exec sp_executesql @.p
Go
Exec Procedure1 1
Go
Exec Procedure2 1
Go
If I do statistics io check on both procedures, none of them are scanning
unwanted tables,
so partitioned view is working fine. Startup filter is also being shown in
query plan.
But, the cost and time comparison to two gives totally results.
Procedure1 is much more costlier than Procedure2. Why?
SaranshHi
This looks like you may have an issue with parameter sniffing. You may want
to check out Ken Henderson's book "The Guru's Guide to SQL Server
Architecture and Internals" ISBN 0-201-70047-6 which has a good description
of this and also a section on partitioned views.
John
"Saransh" wrote:

> Hi,
> Working with partitioned views in SQL Server 2000, I have come across a
> performance problem.
> I have two different price tables with similar set of 1.5M records for two
> different clients.
>
> Create table Table1
> (
> Col1 int not null,
> Col2 nvarchar(200),
> ColN int not null check(ColN=1)
> )
> GO
> Alter table Table1 WITH CHECK
> Add constraint pk_Table1_Constraint PRIMARY KEY CLUSTERED
> (
> Col1,
> ColN
> )
> GO
> Create table Table2
> (
> Col1 int not null,
> Col2 nvarchar(200),
> ColN int not null check(ColN=2)
> )
> GO
> Alter table Table2 WITH CHECK
> Add constraint pk_Table2_Constraint PRIMARY KEY CLUSTERED
> (
> Col1,
> ColN
> )
> GO
> Create view View1
> AS
> Select Col1, Col2, ColN From Table1
> UNION all
> Select Col1, Col2, ColN From Table2
> --1
> Create Procedure Procedure1
> @.par1 int
> As
> Select * from View1 where ColN=@.par1
> Go
> Create Procedure Procedure2
> @.par1 int
> As
> Declare @.p nvarchar(4000)
> set @.p=N'Select * from View1 where ColN=' + CAST(@.par1 as varchar(10))
> exec sp_executesql @.p
> Go
> Exec Procedure1 1
> Go
> Exec Procedure2 1
> Go
> If I do statistics io check on both procedures, none of them are scanning
> unwanted tables,
> so partitioned view is working fine. Startup filter is also being shown in
> query plan.
> But, the cost and time comparison to two gives totally results.
> Procedure1 is much more costlier than Procedure2. Why?
> Saransh|||John,
First of all I would like to thank you for prompt reply.
I tried parameter sniffing but it did not works for me. What I observe is
that clustered indexes of all the underlying tables in partitioned views are
scanned and then STARTUP filter is applied. Because of this the cost is
higher althought statistics io does not shown any unwanted table scan. Can u
give some example code which will execute a query plan not adding the cost o
f
unwanted clustered index scans?
Vikas M.
"John Bell" wrote:
> Hi
> This looks like you may have an issue with parameter sniffing. You may wan
t
> to check out Ken Henderson's book "The Guru's Guide to SQL Server
> Architecture and Internals" ISBN 0-201-70047-6 which has a good descriptio
n
> of this and also a section on partitioned views.
> John
> "Saransh" wrote:
>|||Hi
Try:
Create Procedure Procedure3
@.par1 int
As
Declare @.p nvarchar(4000)
SET @.p = @.par1
Select * from View1 where ColN= @.p
Go
"Saransh" wrote:
> John,
> First of all I would like to thank you for prompt reply.
> I tried parameter sniffing but it did not works for me. What I observe is
> that clustered indexes of all the underlying tables in partitioned views a
re
> scanned and then STARTUP filter is applied. Because of this the cost is
> higher althought statistics io does not shown any unwanted table scan. Can
u
> give some example code which will execute a query plan not adding the cost
of
> unwanted clustered index scans?
> Vikas M.
> "John Bell" wrote:
>|||Hi John,
I tried running parameter snuffing u sent me but still no luck.
Try running this in SQL Query analyzer
Exec procedure2 1
Exec procedure3 1
Go
And see the relative cost of batch. I am looking for cost of both statements
to be similar. As rarely we write stored procedures with constant value.
Vikas M.
"Saransh" wrote:

> Hi,
> Working with partitioned views in SQL Server 2000, I have come across a
> performance problem.
> I have two different price tables with similar set of 1.5M records for two
> different clients.
>
> Create table Table1
> (
> Col1 int not null,
> Col2 nvarchar(200),
> ColN int not null check(ColN=1)
> )
> GO
> Alter table Table1 WITH CHECK
> Add constraint pk_Table1_Constraint PRIMARY KEY CLUSTERED
> (
> Col1,
> ColN
> )
> GO
> Create table Table2
> (
> Col1 int not null,
> Col2 nvarchar(200),
> ColN int not null check(ColN=2)
> )
> GO
> Alter table Table2 WITH CHECK
> Add constraint pk_Table2_Constraint PRIMARY KEY CLUSTERED
> (
> Col1,
> ColN
> )
> GO
> Create view View1
> AS
> Select Col1, Col2, ColN From Table1
> UNION all
> Select Col1, Col2, ColN From Table2
> --1
> Create Procedure Procedure1
> @.par1 int
> As
> Select * from View1 where ColN=@.par1
> Go
> Create Procedure Procedure2
> @.par1 int
> As
> Declare @.p nvarchar(4000)
> set @.p=N'Select * from View1 where ColN=' + CAST(@.par1 as varchar(10))
> exec sp_executesql @.p
> Go
> Exec Procedure1 1
> Go
> Exec Procedure2 1
> Go
> If I do statistics io check on both procedures, none of them are scanning
> unwanted tables,
> so partitioned view is working fine. Startup filter is also being shown in
> query plan.
> But, the cost and time comparison to two gives totally results.
> Procedure1 is much more costlier than Procedure2. Why?
> Saransh