Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Making Dynamic Queries Static

By Leon Platt,

In this article, I discuss some possibilities for circumventing the performance hit on dynamic queries.  This problem crops up endlessly when trying to build stored procedures that take multiple parameters in the where clause.  There are actually two parts to this problem: first, how do we know which parameters to include in the where clause and second, how do we get sql to cache a query plan for the stored procedure.  Lets explore the first problem with a simple example:  Lets say I want to build an application that will allow the user to search for an account based on some criteria.  Lets say our criteria will be Account Name, Contact first name, Contact last name, city, state, and phone number.  To do this we need to build a stored procedure that takes six input parameters: @Aname, @Cfirst, @Clast, @city, @state, @phone.  The select statement we want will look something like this:  SELECT * from account A inner Join  Contact C on a.primarykey=c.accountkey inner join Address D on A.primarykey=D.foreignkey WHERE ......  The problem is how do we build the WHERE clause since it is dynamic.  In other words we do not know which of the input parameters were actually passed values that need to be in the where clause.  Fortunately the rules are pretty simple, if a parameter has a value then it must be contained in the where clause and if a parameter does not have a value then it can not appear in the where clause.  So, how do we solve this problem? This article will present three different solutions: 

Solution 1:  Use the CASE or IF statement to test each parameter and run an appropriate select statement

Example:       

IF @Aname<>'' and @Cfirst='' and @Clast='' and @city='' and @state='' and @phone=''

SELECT * from account A inner Join  Contact C on a.primarykey=c.accountkey inner join Address D on A.primarykey=D.foreignkey WHERE A.name=@Aname

ELSE

IF @Aname<>'' and @Cfirst<>'' and @Clast='' and @city='' and @state='' and @phone=''

 SELECT * from account A inner Join  Contact C on a.primarykey=c.accountkey inner join Address D on A.primarykey=D.foreignkey WHERE A.name=@Aname and C.lastName=@Clast

 ELSE........................

Advantages:    Probably the most straight forward solution

                       Best SQL performance  (low reads, fast sql execution)

Disadvantages:   Complex code as the number of cases rises exponentially with the number of parameters

                            CPU time increases based on the number of CASE or IF comparisons

Solution 2:  Write your sql so that you can always include every parameter in the where clause. How is this possible???

Example:

SELECT * from account A inner Join  Contact C on a.primarykey=c.accountkey inner join Address D on A.primarykey=D.foreignkey WHERE (A.name like @Aname or A.name is NULL) and (C.firstName like @Cfirst or C.firstName is NULL) and (C.lastName like @Clast or C.lastName is NULL) and (D.city like @city or D.city is NULL) and (D.state like @state or D.state is NULL) and (D.phone like @phone or D.phone is NULL)

Advantages:      Easy code to write

                         Fast CPU time (code executes quickly)

Disadvantages:    Need to decide what you are going to do with Nulls

                           Slower SQL performance

Lets take a look at how this works.  First we have to make sure that our input parameters to the stored procedure are correct.  For this scheme to work every parameter will get represented in a LIKE clause, thus the parameter must contain a % at the end.  If parameters are blank then they get converted to %.  So what happens when we do something like WHERE d.city LIKE '%' ?  Well if you turn on show execution plan in query analyzer you can see that it is represented as a SEEK(d.city Is Not Null).  In other words, all records will be returned unless they are NULL.  This is why we need to decide what to do with the Nulls.  When I created this solution, I decided that if the value was NULL then it is conceivable that it could match, thus I added the criteria (or d.City is NULL) to my where.  I end up with the criteria of:  WHERE (dcity LIKE '%' or d.city Is NULL).  This will return "all records where city is Not Null"  or  "all records where city is NULL".  In other words, it will return all records (it is as if the criteria did not exist).  Its magic, with this technique we are able to always add the parameter to the select statement without it actually limiting our return results (it is as if the criteria did not exist). 

Recap of the city example:

WHERE (d.city LIKE '%' or d.city Is NULL)       Returns all records (as if the criteria does not exits). 

WHERE (d.city LIKE 'Orlando%' or d.city Is NULL)    Returns all records where city starts with Orlando or city is Null

As I stated earlier, my implementation of this solution returns records where the city is NULL.  My reasoning for this was that a NULL value by definition means that I do not have the information available to me.  If I do not know for sure that the city is Not Orlando then it is very well possible that it could be Orlando.  What would I do if I wanted to get rid of the records where city is NULL?  Well, your first guess may be to remove the (or d.city Is NULL) clause, which would work fine in cases where the city parameter has a value, but then what would happen if the city parameter was blank?  It would get converted to '%' and the clause would be (d.city LIKE '%') which would limit my results to all records where the city is not null.  Take for instance the case where I want to return all records where state is CA.  My where clause would end up looking like this:

WHERE (A.name like '%') and (C.firstName like '%') and (C.lastName like '%') and (D.city like '%') and (D.state like 'CA%') and (D.phone like '%')

My results would not contain any records where a.name, c.firstName, c.lastname, d.city or d.phone were NULL even if the state was CA.

The challenge is how could you modify my solution so that all records that had a value of 'CA' for state will be returned regardless of whether or not the values in the other fields are Null?  The solution must be generic so that it can work with any parameter and any value passed in.  Remember that the solution must also work for cases where the parameter is blank '%'.  When blank we do not want to restrict any records returned, it should be as if the criteria was not there!

The only other problem I can find with this solution is that you can not enforce operators (=, <>, > , <, IN) other than Like as it stands.  This may or may not be an issue depending on the application.  Another challenge would be to come up with a solution to this!

Solution 3:  Build a Cache table for every combination of parameters that occur

Select @storedProc=storedProc from ProcCache where pAname=@Aname and pCfirst=@Cfirst and pClast=@Clast and pCity=@city and pState=@state and pPhone=@phone

IF @@rowcount=1

    exec (@storedProc)

ELSE

    exec sp_CreateNewProc

Advantages:    Don't waste code trying to handle situations that never occur

                      Good performance  (fast sql execution)

DisAdvantages:    Need to do a lookup in the cache table every time

                            Need to build a stored procedure on the fly the first time a combination occurs

This is an interesting solution to the problem which I have not explored yet.  Basically the way it would work would be like this;  The stored procedure is called with a group of parameters passed in.  The first thing I would do is a lookup in my ProcCache Table.  The ProcCache table would have a column for each parameter.  A value of 1 in the column would indicate that the parameter is included in the where clause and a value of 0 in the column would indicate that the parameter is not included in the where clause.   I would build a SELECT statement based on the parameters passed into the stored procedure.  For example assume I have a stored procedure that accepted two input parameters @account, @name:  My SELECT would be created like this:

SELECT storedProcToRun fromProcCache where Account=CASE @account WHEN '' THEN 0 ELSE 1 END AND Name= case @name WHEN '' THEN 0 ELSE 1 END

The select statement would return the name of the stored procedure that was created to handle this particular query.  If there was no entry for the query then I would insert a row into ProcCache and create a new stored procedure on the fly.  The next time the same query is run it will be in the ProcCache.  This solution means that I only have to create stored procedures for the queries that are run (I do not have to figure out all possible combinations).  The stored procedure will be created automatically since all of the information necessary to create it is known.  As a new query is attempted my collection of stored procedures grows automatically without user intervention.

My thoughts are that this solution could offer a good balance of performance and flexibility.  I will always take a hit doing the cache lookup but it should be very quick, and I will only take a hit creating the stored procedure the first time.  One thing I am considering with this solution is to pass in both the parameter and the comparison operator.  That way I could do any kind of comparison (=, <>, >, <, Like, IN) I wanted and use the one that makes the most sense in some situations.    I expect to play around with this idea shortly and write a follow up article on my experiences. 

SUMMARY

In this article, I have presented three solutions to over come the problem of dynamic queries in stored procedures.  To be honest with you, I would not get too hung up on trying to avoid using dynamic queries.  Yes, sql server can not cache the query plan and you will suffer some performance loss, but this is not any more performance loss than passing the sql directly to sql server from an application.  My opinion is that stored procedures should not just be thought of as a method of increasing sql performance, but also as a way to package your solutions and maybe even most importantly a way to provide maximum security.  By packaging your solutions, I mean that I think of stored procedures as analogous to sub routines in programming.  My current strategy when programming is to do everything through a stored procedure.  Calling the stored procedures are just like calling other sub routines in my program except that the stored procedures deal directly with the database servers.  You may have many situations where you need to do something like return a list of valid users.  Why not make a stored procedure to do this and call it over and over.  Making the stored procedure generic enough to be used in any situation may require dynamic sql, but I would not let that stop you.  One of the greatest benefits of always using a stored procedure for database access is that you can remove the users permissions to the tables completely and place all of the permissions on the stored procedures, thus limiting the users ability to do only those functions that have been coded into stored procedures.  Keep in mind when you are creating dynamic queries in a stored procedure that there may be a higher performance way to achieve the results especially if you are dealing with a small number of input parameters.  Those of you who are techno geeks, try to solve the problem exposed in solution 2 and maybe some of you even have some better solutions than I have presented here.  Lets hear'em!

Total article views: 12868 | Views in the last 30 days: 8
 
Related Articles
FORUM

stored procedure parameters question

stored procedure parameters question

FORUM

stored procedure with optional parameters

creating stored procedure with optional parameters

FORUM

Stored procedures with output parameters

Stored procedures with output parameters

FORUM

Retrieving stored procedure parameters for modification

ADO stored procedure parameter modification

FORUM

Stored procedure- help with date parameter validation

Stored procedure- help with date parameter validation

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones