Stored procedure performance optimization

[Vagif Abilov]

Hello,

I have a question regarding stored procedure desing that provides the
optimal performance. Let's say we have a table Products that consists of
three columns: Name, Status, RegistrationTime. All columns are indexed and
users should be able to lookup data by any of the columns. We have two main
options to design stored procedures for data retrieval:

1. Design separate stored procedures for each search criteria:
LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.

2. Write a generic stored procedure that will fit any search criteria:

CREATE PROCEDURE GetProducts (
@Name varchar(20),
@Status int = NULL,
@FromTime datetime = NULL,
@ToTime datetime = NULL)
AS BEGIN
SELECT
[Name],
[Status],
[RegistrationTime]
FROM [Products]
WHERE [Name]=CASE
WHEN @Name<>NULL THEN @Name
ELSE [Name]
END
AND [Status]=CASE
WHEN @Status<>NULL THEN @Status
ELSE [Status]
END
AND [RegistrationTime]>=CASE
WHEN @FromTimestamp<>NULL THEN @FromTimestamp
ELSE [RegistrationTime]
END
AND [RegistrationTime]<=CASE
WHEN @ToTimestamp<>NULL THEN @ToTimestamp
ELSE [RegistrationTime]
END
ORDER BY [RegistrationTime]
END;

The second option is very attractive, because it is obviously easier to
maintain such code. However, I am a little concerned about performance of
such stored procedure. It is not possible to foresee what index should be
used, index can only be selected each during procedure execution, because
search criteria can include either Name, Status or RegistrationTime. Will it
make this SP inefficient? Or perormance difference in such case is not big
(if any) and we should choose the second option because of its significant
code reduction?

Thanks in advance

Vagif Abilov
va***@online.no

[Simon Hayes]

"Vagif Abilov" <va***@online.no> wrote in message
news:H3*******************@news4.e.nsc.no...

Hello,

I have a question regarding stored procedure desing that provides the
optimal performance. Let's say we have a table Products that consists of
three columns: Name, Status, RegistrationTime. All columns are indexed and
users should be able to lookup data by any of the columns. We have two
main options to design stored procedures for data retrieval:

1. Design separate stored procedures for each search criteria:
LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.

2. Write a generic stored procedure that will fit any search criteria:

CREATE PROCEDURE GetProducts (
@Name varchar(20),
@Status int = NULL,
@FromTime datetime = NULL,
@ToTime datetime = NULL)
AS BEGIN
SELECT
[Name],
[Status],
[RegistrationTime]
FROM [Products]
WHERE [Name]=CASE
WHEN @Name<>NULL THEN @Name
ELSE [Name]
END
AND [Status]=CASE
WHEN @Status<>NULL THEN @Status
ELSE [Status]
END
AND [RegistrationTime]>=CASE
WHEN @FromTimestamp<>NULL THEN @FromTimestamp
ELSE [RegistrationTime]
END
AND [RegistrationTime]<=CASE
WHEN @ToTimestamp<>NULL THEN @ToTimestamp
ELSE [RegistrationTime]
END
ORDER BY [RegistrationTime]
END;

The second option is very attractive, because it is obviously easier to
maintain such code. However, I am a little concerned about performance of
such stored procedure. It is not possible to foresee what index should be
used, index can only be selected each during procedure execution, because
search criteria can include either Name, Status or RegistrationTime. Will
it make this SP inefficient? Or perormance difference in such case is not
big (if any) and we should choose the second option because of its
significant code reduction?

Thanks in advance

Vagif Abilov
va***@online.no

This article might help:

http://www.sommarskog.se/dyn-search.html

Simon

[strider5]

if you choose second option, dont' forget that expressions such as
@Status<>NULL never evaluate to true even if they are syntactically
valid, but you problably want to evaluate @status IS NOT NULL

[Vez do Han]

Hi,
Step by step.

1.) Create Table as
CREATE TABLE [Products] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (20) NOT NULL ,
[Status] [int] NOT NULL ,
[RegistrationTime] [datetime] NOT NULL ,
CONSTRAINT [pk_Products] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

2.) Fill random rows. Exactly 262144 rows
3.) Create Indexes as
CREATE INDEX [Products2] ON [dbo].[Products]([Name]) ON [PRIMARY]
CREATE INDEX [Products3] ON [dbo].[Products]([Status]) ON [PRIMARY]
CREATE INDEX [Products4] ON [dbo].[Products]([RegistrationTime]) ON
[PRIMARY]
4.) Create Proc as
create PROCEDURE GetProducts (
@Name varchar(20),
@Status int = NULL,
@FromTime datetime = NULL,
@ToTime datetime = NULL)
AS BEGIN
SELECT
[Name],
[Status],
[RegistrationTime]
FROM [Products]
WHERE [Name]=CASE
WHEN @Name IS NOT NULL THEN @Name
ELSE [Name]
END
AND [Status]=CASE
WHEN @Status IS NOT NULL THEN @Status
ELSE [Status]
END
AND [RegistrationTime]>=CASE
WHEN @FromTime IS NOT NULL THEN @FromTime
ELSE [RegistrationTime]
END
AND [RegistrationTime]<=CASE
WHEN @ToTime IS NOT NULL THEN @ToTime
ELSE [RegistrationTime]
END
ORDER BY [RegistrationTime]
END
5) And analyse execution plan (Query: GetProducts @Name='ab1')

|--Sort(ORDER BY:([Products].[RegistrationTime] ASC))
|--Clustered Index
Scan(OBJECT:([Prod430D].[dbo].[Products].[pk_Products]),
WHERE:((([Products].[Name]=If ([@Name]<>NULL) then [@Name] else
[Products].[Name] AND [Products].[Status]=If ([@Status]<>NULL) then
[@Status] else [Products].[Status]) AND [Products].[RegistrationTime]>=If
([@FromTime]<>NULL) then [@FromTime] else [Products].[RegistrationTime]) AND
[Products].[RegistrationTime]<=If ([@ToTime]<>NULL) then [@ToTime] else
[Products].[RegistrationTime]))

Wrong - Clustered Index Scan

6)Create index as
CREATE NONCLUSTERED INDEX [Products22] ON [dbo].[Products]
([RegistrationTime] ASC, [Name] ASC, [Status] ASC )
And goto 5)

|--Index Scan(OBJECT:([Prod430D].[dbo].[Products].[Products22]),
WHERE:((([Products].[Name]=If ([@Name]<>NULL) then [@Name] else
[Products].[Name] AND [Products].[Status]=If ([@Status]<>NULL) then
[@Status] else [Products].[Status]) AND [Products].[RegistrationTime]>=If
([@FromTime]<>NULL) then [@FromTime] else [Products].[RegistrationTime]) AND
[Products].[RegistrationTime]<=If ([@ToTime]<>NULL) then [@ToTime] else
[Products].[RegistrationTime]) ORDERED FORWARD)
Yeah OK Index Scan

Warning:

Creating Procedure as
create PROCEDURE GetProducts1 (
@Name varchar(20),
@Status int = NULL,
@FromTime datetime = NULL,
@ToTime datetime = NULL)
AS BEGIN
SELECT
[Name],
[Status],
[RegistrationTime]
FROM [Products]
WHERE ([Name]=@Name or @Name is null)
AND ([Status]=@Status or @Status is NULL)
AND ([RegistrationTime] = @FromTime or @FromTime is null)
AND ([RegistrationTime]= @ToTime or @ToTime is null)
ORDER BY [RegistrationTime]
END;

Is very bad idea because exec time grow dramatically.

Conclusion
Solution <Write a generic stored procedure > is acceptable with good indexes
in tables.