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

Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>