Part

Hi folks,
I posted this somewhere else here, excuse me,,,
I have the following challenge
When I create a stored procedure the selects data from the portioned view,
the execution plan shows that SQL server scans all base tables, while the
select statement after replacing the variable with an actual value scans onl
y
the required table.
Here is a full script:
Execute the commented out statements at the end of the script with show
graphical execution plan option and see the difference.
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[EMPLOYEES]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_3]
GO
CREATE TABLE [Employees_1] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
) ON [PRIMARY]
CREATE TABLE [Employees_2] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
) ON [PRIMARY]
CREATE TABLE [Employees_3] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
) ON [PRIMARY]
GO
--
CREATE VIEW EMPLOYEES
AS
SELECT *
FROM EMPLOYEES_1
UNION ALL
SELECT *
FROM EMPLOYEES_2
UNION ALL
SELECT *
FROM EMPLOYEES_3
GO
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (1,'aaaaa',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (2,'aaaab',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (3,'baaaa',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (4,'baaab',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (5,'caaaa',3)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (6,'caaab',3)
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc GetEmp
@.firstchar int
as
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = @.firstchar
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc GetEmp2
@.firstchar int
as
declare @.sql varchar(8000)
set @.sql ='
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = ' + convert(varchar,@.firstchar)
exec (@.sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = 2
exec GetEmp 2
exec GetEmp2 2
*/Use STATISTICS IO and you will see that SQL Server will only access one of t
he table for the GetEmp
procedure. But the plan doesn't show that because the same plan should work
for whatever value you
send in the parameter. So elimination of the other views are deferred until
run-time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
> Hi folks,
> I posted this somewhere else here, excuse me,,,
> I have the following challenge
> When I create a stored procedure the selects data from the portioned view,
> the execution plan shows that SQL server scans all base tables, while the
> select statement after replacing the variable with an actual value scans o
nly
> the required table.
> Here is a full script:
> Execute the commented out statements at the end of the script with show
> graphical execution plan option and see the difference.
>
> SET NOCOUNT ON
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[EMPLOYEES]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_3]
> GO
> CREATE TABLE [Employees_1] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_2] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_3] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
> ) ON [PRIMARY]
> GO
> --
> CREATE VIEW EMPLOYEES
> AS
> SELECT *
> FROM EMPLOYEES_1
> UNION ALL
> SELECT *
> FROM EMPLOYEES_2
> UNION ALL
> SELECT *
> FROM EMPLOYEES_3
> GO
>
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (1,'aaaaa',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (2,'aaaab',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (3,'baaaa',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (4,'baaab',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (5,'caaaa',3)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (6,'caaab',3)
> go
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp2]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> create proc GetEmp
> @.firstchar int
> as
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = @.firstchar
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE proc GetEmp2
> @.firstchar int
> as
> declare @.sql varchar(8000)
> set @.sql ='
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = ' + convert(varchar,@.firstchar)
> exec (@.sql)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> /*
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = 2
> exec GetEmp 2
> exec GetEmp2 2
> */
>|||Karaszi,
the SP takes time much more than the select stmt, this is why i analyzed the
execution plan.
"Tibor Karaszi" wrote:

> Use STATISTICS IO and you will see that SQL Server will only access one of
the table for the GetEmp
> procedure. But the plan doesn't show that because the same plan should wor
k for whatever value you
> send in the parameter. So elimination of the other views are deferred unti
l run-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
>
>|||What does statistics IO say when you execute it? I assume that you have more
data in your tables
than the test script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
> Karaszi,
> the SP takes time much more than the select stmt, this is why i analyzed t
he
> execution plan.
> "Tibor Karaszi" wrote:
>|||I am sorry; there was a bug in my SP that was enforcing the engine to scan
all queries. My issue is closed now.
This posting was useful; you don’t have to rely on the graphical execution
plan always, some time you have to examine other thinks like the SET
STATISTICS IO.
Thanks all
"Tibor Karaszi" wrote:

> What does statistics IO say when you execute it? I assume that you have mo
re data in your tables
> than the test script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
>