sql server programming: Part -- sql server programming

Part	RATA 4/11/2006 11:15:01 PM

sql server programming: 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 only
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
*/

Re: Part

Tibor Karaszi
4/12/2006 12:00:00 AM

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 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/

[quoted text, click to view]

Re: Part

RATA
4/13/2006 7:40:03 AM

Karaszi,
the SP takes time much more than the select stmt, this is why i analyzed the
execution plan.

[quoted text, click to view]

Re: Part

Tibor Karaszi
4/13/2006 9:30:33 PM

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/

[quoted text, click to view]

Re: Part

RATA
4/15/2006 11:15:01 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.