Strategies for changing WHERE clause in Procs...
-
I'm curious how others are dealing with stored procedures that have fluctuating "WHERE" clauses based on input parameters.
I've used three different methods:
1) String manipulation and sp_executeSQL
Ex: @sSQL=@SelectSQL+@WHERESQL
EXEC sp_executesql (@sSQL)
2) Hard coded varieties sorted through with IF statements:
EX: IF @inputParam1 IS NOT NULL BEGIN (SOME LONG QUERY WITH @inputParam1 in WHERE Clause) END
IF @inputParam2 IS NOT NULL BEGIN (SOME LONG QUERY WITH @inputParam2 in WHERE Clause) END
IF @InputParam1 IS NOT NULL AND @inputParam2 IS NOT NULL BEGIN ...you get the idea.
3) Using the "LIKE" operator instead of the "=" operator and using '%' as a default parameter. This is a new method for me and it has worked very well on the SMALL tables I have used this method against. I emphasize small.
Below is a pseudo proc for illustration...
************************************************************************************************
CREATE PROCEDURE dbo.spSomeProc
(@ParDiv varchar(10)=null, @ParAgent varchar(50)=null, @ParDateLower varchar(20)=null, @ParDateUpper varchar(20)=null)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @StatParDiv varchar(10)
DECLARE @StatParAgent varchar(50)
DECLARE @StatParDateLower varchar(50)
DECLARE @StatParDateUpper varchar(50)
SET @StatParDiv = '%'
SET @StatParAgent = '%'
SET @StatParDateLower = CAST('1/1/1900' as datetime)
SET @StatParDateUpper = getdate()
IF @ParDiv IS NOT NULL SET @StatParDiv=@ParDiv
IF @ParAgent IS NOT NULL SET @StatParAgent=@ParAgent
IF @ParDateLower IS NOT NULL SET @StatParDateLower=@ParDateLower
IF @ParDateUpper IS NOT NULL SET @StatParDateUpper=@ParDateUpper
SELECT (SOME LONG SELECT Statement)
FROM
(
DERIVED TABLE
) s
INNER JOIN
(
DERIVED TABLE
) f
ON s.Key=f.Key
WHERE
CAST(f.Division as varchar(50))LIKE @StatParDiv AND
CAST(f.UserID as varchar(50))LIKE @StatParAgent AND
f.VerifiedDate BETWEEN @StatParDateLower AND @StatParDateUpper
*****************************************************************************************************
I have found this method to work well on small tables because:
1) It's legible
2) As I understand it the optimizer turns "LIKE ('String%')" into a >= <= expression and can use indexes (this doesn't work for "LIKE ('%String%')")
3) I always get the same cached query plan and it appears to be lightning quick.
My concern with this method is that they query engine is using nested loops and passing index information into the derived tables. Even when no input params are specified and the default where clause is "WHERE column LIKE '%'" (essentially column equal to anything) the index seek is being used and the value is being passed via nested loop to the derived tables.
So, with all that being said, does this method introduce an uneccessary performance loss in the future when the the tables become progressively larger?
-
I'd say that the easy answer is 'it depends'.
imo there is never going to happen that you can write a piece of generic code that is going to perform optimally under all thinkable circumstances. Much like index stretegies are a living, breathing thing that needs periodic scrutiny and reevaluation, even more so on SQL statements that are 'unknown'. What is lightning today may crawl tomorrow.
'Multi-purpose' procedures are a very complex area, and also involves a lot of factors in order to get the optimal plan every time it executes under any circumstances. I feel you can drag the concept of 'generic' only so far.. How far that is? It depends...
In a sense you've already answered your question, since you've already have experience with different variants, and their pros and cons respectively. In short, there's no one shoe that fits all.
I'd say to the last question: maybe.
/Kenneth
-
Maybe this can help :
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply