August 2, 2001 at 9:00 am
One of the common things I need my stored procedures to do is apply parameters in certain cases but not others.
Let us suppose that we have a table mapping clients to account managers.
CREATE TABLE Tbl_ClientAccountManager (
ClientID INT NOT NULL ,
AccountManagerID INT NOT NULL ,
CONSTRAINT PK_ClientContact PRIMARY KEY (ClientID , AccountManagerID) )
In some cases I want to retrieve all account managers for a specific client, in others I want to retrieve all clients for a specific account manager.
Assuming that genuine ID fields are greater than zero the following will work:-
CREATE PROC usp_SelectClientAccountManager @ClientID Int = -1 , @AccountManager Int = -1 AS
SET NOCOUNT ON
SELECT *
FROM Tbl_ClientAccountManager
WHERE ( ClientID = @ClientID OR @ClientID = -1) AND ( AccountManagerID = @AccountManagerID OR @AccountManagerID = -1)
GO
August 2, 2001 at 9:07 am
Would'nt this work
CREATE PROC usp_SelectClientAccountManager @ClientID Int , @AccountManager Int AS
SET NOCOUNT ON
SELECT *
FROM Tbl_ClientAccountManager
WHERE ( ClientID = ISNULL(@ClientID,ClientID))
AND ( AccountManagerID = ISNULL(@AccountManagerID,ClientID))
GO
August 2, 2001 at 9:18 am
Oops
CREATE PROC usp_SelectClientAccountManager @ClientID Int , @AccountManager Int AS
SET NOCOUNT ON
SELECT *
FROM Tbl_ClientAccountManager
WHERE ( ClientID = ISNULL(@ClientID,ClientID))
AND ( AccountManagerID = ISNULL(@AccountManagerID,AccountManagerID))
August 2, 2001 at 9:27 am
Yes, it works.
I read somewhere that evaluating NULLs has a performance hit, although I suspect it is very minor in this case
August 2, 2001 at 6:31 pm
You've got to be executing that code a LOT to worry about the time it takes to evaluate nulls! Code it so that it works, cleanly.
Andy
August 9, 2001 at 2:12 am
This mechanism should really be avoided due to the fact that indexes cannot be used optimally. Dynamic SQL is not a bad thing if used correctly. Inserting 100000 rows into the table and then using the sp for any value will involve reading 633 pages of data (only to return 3 rows).
Change the SP to check for NULL and the index can then be used and so when passing a clientId only 3 pages are read.
Try the code below, which increases the amount of data to be read by adding a char column. This can be taken off to show the original results
The following discussion details the pros and cons of dynamic SQL. The main point is that using sp_executesql to issue a parameterised query is just as performant as the use of an SP.
drop table Tbl_ClientAccountManager
go
CREATE TABLE Tbl_ClientAccountManager (
ClientID INT NOT NULL ,
AccountManagerID INT NOT NULL ,
duffData char(100) )
go
set nocount on
declare @i int
set @i = 0
begin transaction
while @i < 10000
begin
insert into Tbl_ClientAccountManager values (@i, 10000-@i,'')
insert into Tbl_ClientAccountManager values (@i, 10000-@i+1,'')
insert into Tbl_ClientAccountManager values (@i, 10000-@i+2,'')
set @i = @i +1
if @i % 2000 = 0
begin
commit transaction
begin transaction
end
end
commit transaction
set nocount off
go
alter table Tbl_ClientAccountManager add CONSTRAINT PK_ClientContact PRIMARY KEY (ClientID , AccountManagerID)
CREATE INDEX i_Tbl_ClientAccountManager_AccountManagerId ON Tbl_ClientAccountManager(AccountManagerID)
go
drop procedure usp_SelectClientAccountManager
drop procedure usp_SelectClientAccountManager2
go
CREATE PROC usp_SelectClientAccountManager @ClientID Int , @AccountManagerID Int AS
SET NOCOUNT ON
SELECT *
FROM Tbl_ClientAccountManager
WHERE ( ClientID = ISNULL(@ClientID,ClientID))
AND ( AccountManagerID = ISNULL(@AccountManagerID,AccountManagerID))
go
CREATE PROC usp_SelectClientAccountManager2 @ClientID Int , @AccountManagerID Int AS
SET NOCOUNT ON
IF @ClientId is NOT NULL
SELECT *
FROM Tbl_ClientAccountManager
WHERE ClientID = @ClientID
ELSE
SELECT *
FROM Tbl_ClientAccountManager
WHERE AccountManagerID = @AccountManagerID
go
set statistics io on
exec usp_SelectClientAccountManager 2324,null
exec usp_SelectClientAccountManager null,99
exec usp_SelectClientAccountManager2 2324,null
exec usp_SelectClientAccountManager2 null,99
set statistics io off
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
August 9, 2001 at 4:34 am
i agree with you simon ...the if contruct would be more optimized
but the solution i choose depends on how complex the query is ...i often find myself writing complex queries that link a lot of tables with the requirement of such conditional comparison .. and that would mean that i need to write a if contruct for each variation ..which at times becomes impractical if not impossible ..
it basically a trade off between performance and ease of maintainance/usability...
i've used the ISNULL solution quite a few times.. and the performance loss is not that significant because i usually tend to put all my queries into procedures ..however if the query required total optimization i would consider the if contruct...
August 9, 2001 at 4:59 am
The problem is that the number of queries you need to write grows geometrically.
1 variable = 2 queries.
2 variables = 4 queries.
3 variables = 8 queries etc.
In other words the number of combinations is 2 to the power of the number of variables.
In the past I have avoided building up query strings because they can be difficult to maintain and because the query is compiled at runtime.
I understand that on subsequent runs of sp_ExecuteSQL the query plan for the 1st run is re-used, but what is the life cycle for the query plan?
Is the use of sp_ExecuteSQL really preferable to the the earlier examples in this dicussion thread?
August 9, 2001 at 5:44 am
This is what books online says about sp_executesql
----------
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.
---------
This would mean that if you generated a string for using with sp_executesql the Query plan would not be used...passing parameters to the sp_executesql would mean the same as creating a procedure without the benefit of pre-compilation AND notice the statement "query optimizer is likely to reuse the execution plan"..
December 8, 2002 at 4:09 pm
When we have this situation, (same result set, multiple selection criteria)
we do something like this
create proc myProc (@CustId int=NULL, @MgrID int = NULL
as
declare @MyCust int, @myMrgint
set @MyCust = coalease(@CustID, 0)
set @MyMgr = coalease(@MgrID, 0)
select *
from org
where Custid=@MyCustID
or @myMgr = @MyMgr
This assumes that 0 is not a value custID or MgrID. You can make 0 anything you want.
This coalesces() just once per var and optimizes the same regardless of whether something is passed in for the parm or not. You just can't search for "AND" conditions with this logic.
The other thing you could try is a self join with the criteria but it would be ugly and error-prone.
December 9, 2002 at 8:05 am
With that you must always get a tablescan.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
December 9, 2002 at 8:47 am
Well this looks a lot like the discussion we had with optional parameters.
Then i Posted this solution.....
I have some experience with this too and i found out that when you are using several paremeters it is better to build te string Dynamically and use SP_Executesql.....
Some Advantages :
1. Very Easy to maintain
2. ALWAYS a good query plan
3. Add as much optional parameters as you want,
the resulting query will always be an exact mach of what YOU want.....
4. Beats a COALESCE by FAR..... ( 10 to 30 times faster )
Here is the example......
CREATE PROCEDURE dbo.OptionalParemeters
(
@Field1 int = null, /* optional variable */
@Field2 varchar(64) = null, /* optional variable */
@Field3 varchar(16) = null, /* optional variable */
)
AS
BEGIN
Declare @ColumnStr varchar(600)
Declare @FromStr varchar(100)
Declare @JoinStr varchar(500)
Declare @WhereStr varchar(2000)
Declare @SQLStatement nvarchar(3000)
-- Define the columns to display
Set @ColumnStr = '
m.Field1,
m.Field2,
m.Field3,
J.FieldA,
J.FieldB
'
-- Define from table
Set @FromStr = '
Table1 m
'
-- Define the joined tables
Set @JoinStr = '
Left join Table1 J on M.ID=J.ID
'
-- Empty the where clause
Set @WhereStr = ''
if @Field1 is not NULL -- Is this parameter filled ?
begin
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field1 =' + str(@pv_field1)
end
if @Field2 is not NULL -- Is this parameter filled ?
begin
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field2=' + char(39)+ @Field2 +char(39)
end
if @Field3 is not NULL -- Is this parameter filled ?
begin
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field2=' + char(39)+ @Field3 +char(39)
end
if rtrim(@WhereStr) <> '' set @Wherestr = +' Where '+ @WhereStr
set @SQLStatement = 'Select '+ @ColumnStr + ' From ' + @FromStr + ' ' + @Joinstr + @WhereStr
EXEC sp_executesql @SQLStatement
END
I think this will ALWAYS result in a fast query..
Ok so we loose the benefits op precompiled stuff but...........
If you have 10 parameters and only two filled....... This will beat any ISNULL or coalesce query.......
Tested it a lot of times, the only time you loose is the compilation of the query....
Try it once and look at the results.....
Do a DBCC FREEPROCCACHE first.....
December 9, 2002 at 8:54 am
The biggest argument against dynamic SQL: security.
If you're comfortable letting users have access to the base tables, then it doesn't matter. However, with the case here where you're trying to return a query based on a specific account manager... a savvy user could open up Access (assuming he or she doesn't have the SQL Server client tools installed), link to the table, and return the whole table.
sp_executesql keeps the stored procedure from recompiling mainly because it shifts the responsibility of determining if an execution plan is necessary to another batch. However, its execution plan will be cached as an ad-hoc query. Meaning it will be killed before the stored procedure all things being equal.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 9, 2002 at 9:06 am
In a reply on Simon Sabin.
I tested your solution but extended it a little.....
set nocount on
set statistics io on
set statistics time on
exec usp_SelectClientAccountManager 2324,null
exec usp_SelectClientAccountManager null,99
exec usp_SelectClientAccountManager2 2324,null
exec usp_SelectClientAccountManager2 null,99
exec sp_executesql N'SELECT * FROM Tbl_ClientAccountManager WHERE ClientID = 2324'
exec sp_executesql N'SELECT * FROM Tbl_ClientAccountManager WHERE AccountManagerID = 99'
When i did these queries on your testdatabase i got The same results as your second query. Although your solution is a good one if there are a few paremeters, your solution is hard to maintain for 10 parameters. That is why i try to tell people never to use a coalesce or isnull when a SP has optional parameters.
But i did some additional testing..
Declare @Now1 Datetime
Declare @Now2 Datetime
Declare @Now3 Datetime
Declare @Now4 Datetime
Declare @Now5 Datetime
Declare @Now6 Datetime
DBCC FREEPROCCACHE
SET @NOW1=GETDATE()
exec usp_SelectClientAccountManager 2324,null
exec usp_SelectClientAccountManager null,99
SET @NOW2=GETDATE()
DBCC FREEPROCCACHE
SET @NOW3=GETDATE()
exec usp_SelectClientAccountManager2 2324,null
exec usp_SelectClientAccountManager2 null,99
SET @NOW4=GETDATE()
DBCC FREEPROCCACHE
SET @NOW5=GETDATE()
exec sp_executesql N'SELECT * FROM Tbl_ClientAccountManager WHERE ClientID = 2324'
exec sp_executesql N'SELECT * FROM Tbl_ClientAccountManager WHERE AccountManagerID = 99'
SET @NOW6=GETDATE()
print 'First version took : ' +ltrim(datediff(ms,@NOW1,@Now2)) + ' MS'
print 'Second version took : ' +ltrim(datediff(ms,@NOW3,@Now4)) + ' MS'
print 'Third version took : ' +ltrim(datediff(ms,@NOW5,@Now6)) + ' MS'
And this resulted in :
First version took : 70 MS
Second version took : 10 MS
Third version took : 10 MS
Hoping to make a statement......
Better use NO query plan at all than a BAD query plan.
December 10, 2002 at 9:15 am
Difficulty is that 10ms is the smallest unit went it comes to datediff and dates, so you need to do some looping. In addition should dropcleanbuffers otherwise the first suffers from having to read all the data from the disk.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply