March 21, 2012 at 10:44 am
I decided to add this as it’s own discussion.
If I have a proc who takes optional params. Say 10 and any one of these params or any combo of them can be null in any given call, then u shouldn't use isnull or coalesce because the functions kill sargability nor should u break these out into " or param is null" because that makes for bad exec plan?
Someone mentioned using dynamic sql would allow for sargability in these instances, which I suppose makes sense because your removing the possible isnull() functions. But I thought dynamic sql doesn’t allow for the proper caching of the optimized execution plan. Well it does but being it's dynamically generated sql then the actual sql probably won’t get used again before the plans out of cache. Am I making any sense here cause I’ve always been interested in the sargability and cached plans details.
The reason i ask is i had a search proc at a place that i worked at some years ago and it was written in dynamic sql because there were all sorts of input params who were being dynamically populated based on which search conditions the end user selected. Price range, make, model , year...stuff like that.
I was always under the impression that it’s best to break each possible combo or as close to every combo of input params into their own procs so u hit the cached plan more often than not and just have like a master proc who determines which proc to call based on which params were supplied? What's your opinion?
March 21, 2012 at 10:54 am
I'll start by saying it depends.
Having a master procedure that calls the correct child procedure based on the parameters passed is definately one way to do it. The complexity can grow as the number of parameters and combinations of parameters grow.
IIRC, when it comes to dynamic sql, I thought I read that it depended on how the dynamic sql was called. I read, I believe in BOL, that it is better to use sp_executesql than to use exec when executing dynamic sql.
March 21, 2012 at 10:58 am
I like the idea of the master proc.
sp_executesql is the better way to go with dynamic sql as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 21, 2012 at 1:22 pm
just read this "sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many 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 SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution."
Assumeing that is true then it appears because sp_executesql uses params the plan is going to get cached and apply to all different variations of the params values. meaning the cached plan will apply to many more different executions of the same proc. To be hones knowing that i would have kept the dynamic sql search proc i rewote alone as opposed to breaking it out into a bunch off modularized procs. To be honest based on the above info i suppose my only issue w/ dynamic sql now is its readbility. It's just plain ugly.
March 21, 2012 at 1:42 pm
Snargables (3/21/2012)
To be honest based on the above info i suppose my only issue w/ dynamic sql now is its readbility. It's just plain ugly.
A lot of that may depend on the method of construction. Most of my dynamic SQL is perfectly legible. In fact, it's a lot more legible than some of the static SQL that I have seen.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2012 at 2:51 pm
All dynamic and ad-hoc SQL statements have their plans cached. The rules for caching plans is the same for procedures and for ad-hoc or dynamic SQL.
The difference is in how the incoming statements are matched to cached plans. For procedures that's by object_id. For ad-hoc or dynamic SQL it's done on a hash of the statement text. Hence if a specific piece of ad-hoc SQL (parameterised or not) is run 20 times, it is very, very likely that the plan will be reused 20 times.
Parameterising the ad-hoc or dynamic SQL means that the matching of statement does not include the literal values, hence the statement text is more likely to match an existing plan and for the plan to be reused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2012 at 2:54 pm
Snargables (3/21/2012)
I was always under the impression that it’s best to break each possible combo or as close to every combo of input params into their own procs so u hit the cached plan more often than not and just have like a master proc who determines which proc to call based on which params were supplied?
The problem with that is that the number of required sub-procedures increases incredibly fast. For 1 or 2 optional parameters sure, that's 2 or 4 sub-procedures. Try 6 optional parameters, that's around 720 procedures needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2012 at 6:23 am
If parametized dynamic sql will result in more cache hits then why would anyone write non parameterized dynamic sql?
March 22, 2012 at 6:33 am
Because they don't know better?
There are some things that can't be parameterised - IN lists, table and column names, however ignoring those you should always try to write parameterised queries both for the caching and the protection against SQL Injection attacks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 7:00 am
Snargables (3/22/2012)
If parametized dynamic sql will result in more cache hits then why would anyone write non parameterized dynamic sql?
There are MANY times where you don't WANT the engine to use a cached plan. Take a reporting style query with a startdate and enddate. If the first call to the query is 1 day between those dates you will get a very efficient index seek, bookmark lookup type plan for the few rows that match. Then the next call to the same query has a TEN YEAR date range. Do you REALLY want to do the same index seek, bookmark lookup plan?? ABSOLUTELY NOT - it will cause a kajillion random IOs on the system. The reverse is bad as well - ten year range initially getting you scan/hash (or merge) plan which will be extremely inefficient for the 1-day date range call.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 23, 2012 at 7:56 am
TheSQLGuru (3/23/2012)
Snargables (3/22/2012)
If parametized dynamic sql will result in more cache hits then why would anyone write non parameterized dynamic sql?There are MANY times where you don't WANT the engine to use a cached plan. Take a reporting style query with a startdate and enddate. If the first call to the query is 1 day between those dates you will get a very efficient index seek, bookmark lookup type plan for the few rows that match. Then the next call to the same query has a TEN YEAR date range. Do you REALLY want to do the same index seek, bookmark lookup plan?? ABSOLUTELY NOT - it will cause a kajillion random IOs on the system. The reverse is bad as well - ten year range initially getting you scan/hash (or merge) plan which will be extremely inefficient for the 1-day date range call.
However in that case you'd probably use Option(Recompile). If you don't want the plan to be reused then there is no point in caching it (and wasting memory and time), rather just tell SQL that you want the plan to be regenerated on each execution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 9:21 am
GilaMonster (3/23/2012)
TheSQLGuru (3/23/2012)
Snargables (3/22/2012)
If parametized dynamic sql will result in more cache hits then why would anyone write non parameterized dynamic sql?There are MANY times where you don't WANT the engine to use a cached plan. Take a reporting style query with a startdate and enddate. If the first call to the query is 1 day between those dates you will get a very efficient index seek, bookmark lookup type plan for the few rows that match. Then the next call to the same query has a TEN YEAR date range. Do you REALLY want to do the same index seek, bookmark lookup plan?? ABSOLUTELY NOT - it will cause a kajillion random IOs on the system. The reverse is bad as well - ten year range initially getting you scan/hash (or merge) plan which will be extremely inefficient for the 1-day date range call.
However in that case you'd probably use Option(Recompile). If you don't want the plan to be reused then there is no point in caching it (and wasting memory and time), rather just tell SQL that you want the plan to be regenerated on each execution.
Agreed. Often dynamic SQL and OPTION (RECOMPILE) go hand-in-hand. dynamic SQL can get you exact values for WHERE clause components, TOP, etc. but more importantly it can allow you to completely eliminate JOINs to tables that are not needed to be touched due to input parameters being NULL. I have done this type of stuff for open-ended search systems numerous times with great payback.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply