sql server programming: Stored procedure and query optimization -- sql server programming

RE: Stored procedure and query optimization

Alejandro Mesa
9/28/2004 10:49:05 AM

sql server programming:

Read this outstanding article, written by Erland Sommarskog. Here you will
find pros and cons when creating a multi-column search solution.

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

AMB

[quoted text, click to view]

RE: Stored procedure and query optimization

Cowboy (Gregory A. Beamer) - MVP
9/28/2004 11:19:03 AM

Any time you get too generic, you suffer. From a perf standpoint, you are
better to have a really long sproc with quite a few conditions than to
dynamically ad hoc the query. From a maintenance standpoint, however, you
might be willing to suffer a bit of perf loss to more easily maintain.

If it were me, I would break out the different conditions and fire only the
code necessary to get the answer I wanted. Whether this is multiple sprocs or
one is your call.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

[quoted text, click to view]

Stored procedure and query optimization

Vagif Abilov
9/28/2004 7:18:50 PM

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
vagif@online.no

Re: Stored procedure and query optimization

Vagif Abilov
9/28/2004 9:34:41 PM

Thank you very much! This is really a great article, and exactly what I was
looking for.

Vagif

[quoted text, click to view]

Re: Stored procedure and query optimization

Vagif Abilov
9/29/2004 9:27:06 AM

Thank you, Gregory. This makes sense. I am updating my code :-)

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