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

Parameters best practice

Folks, What is the recommended best practice for the following?
Let’s say we are in the pubs database and we want to return some employee
information via a stored proc with an optional parameter of last name. The
issue at hand is how do you structure the stored proc to allow multiple last
names to be provided? It seems that some form of
SELECT * FROM [employee] WHERE [lname] in (@.lastNames)
would do the trick, but no.
Building a sql statement will work but that kind of defeats the value of
using parameters to prevent injection.
One and none provided are easy. Executing the stored proc multiple times
does not seem like the best answer.
Thanks…ChucIt seems as if I quote Dejan on a daily basis:
http://solidqualitylearning.com/blo.../10/22/200.aspx
Since the function mentioned in Dejan's blog returns a table of values,
you'd use it in a join to your table.
ML|||http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Why do you think that your imaginary syntax "lname IN (@.lastNames"
makes sense? A parameter is scalar, SQL is a compiled language!! Why
did you use square brackets instead of non-proprietary syntax?
names to be provided? <<
This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
w of any language class.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter;
it is not code. This is not just an SQL problem; this is a basic
misunderstanding of programming of principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
MS comes up to standards that a over a decade old.
3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.|||Great attitude. Check it at the door next time...
"--CELKO--" wrote:

> Why do you think that your imaginary syntax "lname IN (@.lastNames"
> makes sense? A parameter is scalar, SQL is a compiled language!! Why
> did you use square brackets instead of non-proprietary syntax?
>
> names to be provided? <<
> This is a common Newbie question. This shows that you don't know what
> a scalar parameter is. This is MUCH worse than not knowing SQL. This
> is a fundamental programming concept that you should learn in the first
> w of any language class.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter;
> it is not code. This is not just an SQL problem; this is a basic
> misunderstanding of programming of principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma.
> You can then write:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array", like this in SQL/PSM
> (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM ( VALUES (p1), (p2), .., (pN))
> AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> You will need to use SELECT ..UNION ALL in T-SQL dialect for this until
> MS comes up to standards that a over a decade old.
> 3) The right way! You load the Parmlist table with values so that each
> value is validated by the SQL engine, subject to more constraints and
> you have no SQL injection problems. A good optimizer will not need the
> SELECT DISTINCT, just a SELECT.
>