May 30, 2009 at 11:52 am
Comments posted to this topic are about the item Writing Dynamic Stored Procedure
June 1, 2009 at 12:56 am
Old style to write the WHERE condition.
I prefer to build a string ONLY with the true condition; it's more performing.
In a complex WHERE with useless condition may lead to a BIG consume of resources.
June 1, 2009 at 1:26 am
Quick question is it only me or has the formatting of that article gone a bit haywire. I'm sure there shouldn't be HTML formatting tags appearing alongside the SQL example.
I used to use lots of CASE statements and IF branches to handle queries that needed to be dynamic but build on the server side using multiple proc params. For example something like this:
CREATE PROC test
@Stamp datetime,
@FirstName varchar(25) = NULL,
@Surname varchar(25) = NULL,
@Age int = NULL,
@Address1 varchar(40) = NULL
AS
BEGIN
DECLARE @Today BIT
SELECT @Today = CASE Datediff(day,@Stamp,getdate()) WHEN 0 THEN 1 ELSE 0 END
IF @Today = 1
BEGIN
SELECTFirstName, SureName, Age, Address1
FROMDAILY
WHEREFirstName = CASE WHEN @FirstName IS NULL THEN FirstName ELSE @FirstName END
AND Surname = CASE WHEN @Surname IS NULL THEN Surname ELSE @Surname END
AND Age = CASE WHEN @Age IS NULL THEN Age ELSE @Age END
AND Address1 = CASE WHEN @Address1 IS NULL THEN Address1 ELSE @Address1 END
END
ELSE
BEGIN
SELECTFirstName, SureName, Age, Address1
FROMHISTORICAL
WHEREFirstName = CASE WHEN @FirstName IS NULL THEN FirstName ELSE @FirstName END
AND Surname = CASE WHEN @Surname IS NULL THEN Surname ELSE @Surname END
AND Age = CASE WHEN @Age IS NULL THEN Age ELSE @Age END
AND Address1 = CASE WHEN @Address1 IS NULL THEN Address1 ELSE @Address1 END
END
But then I learnt a bit more about query plan caching and indexing and so if I have to do dynamic queries in the DB I would build them up using a string with only the columns and filters required and then use sp_executeSQL to run it.
June 1, 2009 at 2:16 am
Apart from the fact that this article is horribly mangled (which may of course not be the author's fault, but it doesn't help in making this article easily readable), it is also a rather bad advice. And I know because I had done the same a while ago but got corrected.
For details see http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
June 1, 2009 at 2:22 am
I prefer to use Sunil's way of writing query when I have optional (can be null) parameters in the procedure. I don't see a logical reason to use CASE, or ISNULL statement.
Sunil example is not anything new. Like anything in live, this too have some good and bad sides. The good ones are that is very logical, easy to debug, don't have a problem of sql injection etc. But also have bad side like the one that it can't use the cached plan for the query. In the other side creating query dinamically as string and executing with sp_executesql have all the bad sides but one good and that is that can use cashed plan. In most of situation we should use the first one, becuase if has more good than bad sides. But it becomes very usefull to use second one when we search very big table with milions of records.
June 1, 2009 at 2:52 am
http://www.sommarskog.se/dyn-search-2005.html discusses this and many other similar methods
June 1, 2009 at 3:02 am
Yes, it is not good way to write dynamic procedures like this because of performance, but on other side this approach has one plus - it is good readable, so easy to understand when looking on code of procedure. Times ago i was doing it same way, but once database was growing much the penalties for uising this procedures were big.
June 1, 2009 at 3:05 am
Excellent link Dave.
I had been going to say I could never get the @x = y or @x is null thing to work effectively - it always ends up scanning big tables rather than using decent indexes, but Erland seems to cover the whole subject in very good depth.
June 1, 2009 at 3:54 am
http://www.sommarskog.se/dynamic_sql.html This one is also very good discussing dynamic sql in general. Coincidently I posted to my blog a method for optimizing dynamic sql for Unbalanced data loads http://sqlandthelike.blogspot.com
June 1, 2009 at 5:42 am
Gail's article a few month's back on catch-all queries covered this territory really well:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
Sunil's method is acceptable on small tables, but on larger tables this will choke a server. I think developers implement it in their test environment where tables have a thousand rows, and it does what it's supposed to do and returns quickly. So it goes to production where the million row tables are first encountered. That's when the stored procedure that runs in under a second during testing takes hours in actual use. Clustered index scan fun at its best. 😛
June 1, 2009 at 6:14 am
This is a great, albeit rather standard, technique for conditional WHERE clauses. Performance has never been an issue for me as long as the evaluated condition is efficient (i.e. evaluate true conditions wherever possible as stated by another poster).
My only issue with the code as presented is the use of NULL values as the parameter defaults. This is extremely risky since different tools methods of calling a stored procedure can cause different and unexpected values to be passed as parameter values.
Instead of NULL (dates are an exception - see below), use a default value appropriate for the data type. I usually use 0 for number data types and empty string ('') for character data types. Then evaluate the parameter in the WHERE clause as ISNULL(@NumberParm, 0) or ISNULL(@StringParm, '').
For dates, use NULL as the default and then use the ISDATE() function to make sure you get a real date. Since both '' and NULL will cause ISDATE(@DateParm) to return 0, it is safe and robust.
J Pratt
June 1, 2009 at 6:25 am
Personally I hope lots of people keep on doing this because due to the staggeringly-horrible performance this type of query can have there will be more opportunities for me to get called in as a performance tuning guru to fix the mess!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2009 at 6:34 am
jpratt (6/1/2009)
Instead of NULL (dates are an exception - see below), use a default value appropriate for the data type. I usually use 0 for number data types and empty string ('') for character data types. Then evaluate the parameter in the WHERE clause as ISNULL(@NumberParm, 0) or ISNULL(@StringParm, '').For dates, use NULL as the default and then use the ISDATE() function to make sure you get a real date. Since both '' and NULL will cause ISDATE(@DateParm) to return 0, it is safe and robust.
I disagree with your comment. You have a problem when using ISNULL and default values and that is you can't have null values in columns and search by those columns too. If you do decide to not allow null values in tables then you have to use default values for columns too, which values must be in the referenced tables too. I don't see this is logical and usefull.
Having nullable parameters is very normal to use and i can't see any problem.
June 1, 2009 at 6:46 am
Yes that link is a very good article that covers all the various methods and a good discussion of recompilation. As per my first example I used to write my "dynamic" queries as static SQL using CASE statements purely because they were easy to read and nice to manage. I remember a proc like the following that because of parameter difference and plan caching was taking 30 seconds+ to run. Adding the option(recompile) at the end of it fixed that problem.
SELECTRow, CandidatePK, [Name], Email, RegisterDate,[Filename],Apps,[status]
FROM (SELECT TOP (@Stop) ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'ASC' THEN registerDate END ASC,
CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'DESC' THEN registerDate END DESC,
CASE WHEN @OrderBy = 'name' AND @Direction = 'ASC' THEN c.Name END ASC,
CASE WHEN @OrderBy = 'name' AND @Direction = 'DESC' THEN c.Name END DESC)
AS Row, c.CandidatePK, c.Name, c.Email,RegisterDate,[Filename],
count(a.ApplicationPK) as apps, isnull(cast(cv.status as int),-1) as [status]
FROM CANDIDATES as c
LEFT JOIN CANDIDATES_CV as cv
ON c.CandidatePk = cv.CandidatePK
LEFT JOIN APPLICATIONS as a
ON c.CandidatePk = a.CandidateFK
WHERE c.SiteFK = @SitePK AND
CASE
WHEN @SearchFor is null THEN 1
ELSE
CASE
WHEN @LookIn = 'name' AND c.Name LIKE @SearchFor THEN 1
WHEN @LookIn = 'email' AND c.Email LIKE @SearchFor THEN 1
ELSE 0
END
END = 1
GROUP BY c.CandidatePK, c.Name, c.Email, RegisterDate, [Filename], [status]
)
AS CANDS
WHERERow between @Start and @Stop
OPTION(RECOMPILE)
I would also like to know whether the point he makes in the article which I have reproduced below is still true for SQL 2005. Providing as many options as possible for the optimiser to pick the best plan from. If there is a query that can be covered by multiple indexes should you supply as many options as possible or only provide those that you think it needs. I had a similar issue the other day in a huge mult million row logger table where the rows of data I wanted to return could be accessed by multiple clauses. Should I have added all possible paths to filter the data or not:
Double Feature
You may wonder why there are two conditions on the order id, customer id and product id (lines 32-33, 48-49 and 64-65). This is because I once learnt that you should always specify all conditions you know of to help the optimizer out. That was very long ago, and the rule may no longer apply. But I included it here nevertheless.
June 1, 2009 at 6:52 am
I'd agree with most of the postings - beware of dynamic SQL and make sure to check the execution plans of several different queries that get run depending upon the parameters passed in. Have to make sure that table scans are not being performed instead of indexes being utilized...
Viewing 15 posts - 1 through 15 (of 83 total)
You must be logged in to reply to this topic. Login to reply