Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««7891011

Use Dynamic SQL to Improve Query Performance Expand / Collapse
Posted Wednesday, May 26, 2010 11:17 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, November 11, 2016 6:39 AM
Points: 9,932, Visits: 11,346
Hey James,


"It is well know for instance that if we put NEWID() in a SELECT statement, it will be executed once per row. Other functions and expressions such as GetUtcDate() won't. This shows how the functions and expressions are evaluated, and how the mess will be big if they break this behavior."

Really? Read the comments from the SQL Server development team on this Won't Fix bug report:

You're going to get a full response to your long post in due course, but I thought you should see that first.


Paul White
Post #928478
Posted Wednesday, May 26, 2010 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 22, 2016 9:27 AM
Points: 34, Visits: 153
Good article.

This is one of the most common culprits I have found of long running procedures and lock issues at a number of various clients for whom I have done work. (Search query takes for ever and prevents write operations from other processes). Typically, I'll recommend checking the parameters and executing the appropriate query with only the arguments that are not null (literally duplicating the query for every combination). In cases where you have 4+ optional parameters, then I generally recommend the Dynamic SQL with Variables approach, as the proc woudl become otherwise unwieldy. Though someone mentioned issues with execution scope where data could not be access directly, the duplicated query with only the specified fields, while wordy and ugly, would provide the performance benefits without this limitation.

as a consultant, sometimes I like seeing the OR IS NULl approach, as I can quicly re-write it with thousandfold performance improvements and the client loves me for it . Other times, it annoys me because obviously people didn't bother investigating what thier queries were DOING...

Thanks Paul W for posting info about the RECOMPILE option changes in SP1 CU5. I'll look into that for some cases, though often when I hit this pattern the SQL itself if generally complex search patterns that would suffer from forced recompilation.
Post #928600
Posted Wednesday, May 26, 2010 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 1, 2010 4:19 AM
Points: 39, Visits: 150
I thinks it will be well preformance using sp_executesql for dynamic sql query. it can reuse the execure plan in cache, so reduce the compile time.
Post #928736
Posted Monday, June 21, 2010 6:41 PM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 2, 2016 5:17 AM
Points: 955, Visits: 1,514
Very nice article. Keep up the good work.
Very well written and informative.

My only complain would be the use of hacks like "always true" or "always false".
The "WHERE 1 = 1" was used nicely though. :)

Best regards,

Andre Guerreiro Neto

Database Analyst
Post #940708
Posted Friday, February 17, 2012 3:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 9:32 AM
Points: 8, Visits: 45
Very nice article.
Some month ago, I tried to do something like that, and I tried to improve the query doing my best but…I found that when you have dynamic queries, so you need to do the classic string concat in order to do it, the best I found was CLR Stored Procedure. I found a big difference into the performance when you query tables with more than 20 millions of rows.
SQL Server also saves in cache a dynamic query from a CLR Stored procedure, and for a normal dynamic query made in a Stored Procedure it don’t.
This is my opinion, of course, I am not a SQL Server guru I am just a .Net Developer and I had big performance problems to solve, so I found this way to solve it.
In my case I use very big tables from a Telecom system.., so the performance is very important.
Post #1253722
Posted Friday, February 17, 2012 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 28, 2016 11:20 AM
Points: 1,323, Visits: 2,082

At the risk of sounding snotty, you handicapped Case 2 intentionally to remove it from consideration. Neither of the other cases will return the same RESULTS as case 2 will. Try it. Case 3 will return nothing because LastName and Email are Null. Case 4 will return nothing as @Email is NULL. Only Case 2 properly represents a FirstName search. Therefore it is the only correct case.

Look at the number of coded cases you will need for the approach in case 3...

1 input parameter = 2 cases (with/without null)
2 input parameters = 4 cases
3 input parameters = 8 cases
4 input parameters = 16 cases
5 input parameters = 32 cases
6 input parameters = 64 cases
7 input parameters = 128 cases
8 input parameters = 256 cases

Now, if you need a true dynamic query the ONLY option that has performance across all input parameters on a million row test table is Dynamic SQL. Otherwise at 8 input parameters you are trying to maintain 256 cases which is not only impractical, it's virtually impossible.

Edit: I didn't realize this article/thread was over 2 years old... I don't expect a response given how old it is.

James Dingle-651585 (5/26/2010)

If OBJECT_ID('Case3GetUser', 'P') Is Not Null Drop Procedure Case3GetUser;
Create Procedure Case3GetUser (
@LastName VarChar(50) = Null,
@FirstName VarChar(50) = Null,
@Email VarChar(255) = Null
RaisError('Case3GetUser', 10, 1) With NoWait;

Select *
From [User]
-- Last name only (omitted but implicit: "And @LastName Is Not Null")
UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName Is Null)
-- Last name and first name together (omitted but implicit "And LastName Is Not Null And @FirstName Is Not Null")
Or UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName = FirstName)
-- by email (omitted but implicit "And @Email Is Not Null")
Or UserId In (Select UserId From [User] Where @Email = Email)

Looking at the execution plans, we observe that:
1. Method 2 have a unique execution plan per combination of parameters, while methods 3 and 4 always have the same one.
2. Because methods 3 & 4 have each only ONE execution plan, it's easy to review. For method 2, you have to test all the combinations of parameters and look at each execution plan generated.
3. The only terrible SQL statement and execution plan come from method 2. It is an illustration of an unexpected code path leading to the execution of something that has not been reviewed nor predicted.

Looking at the I/O statistics you will observe that:
1. All methods give the same performance when running in the use case they were designed for.
3. In the bogus situation, method 2 is the only one to turn into a full scan, while others do not put the server in danger.

Post #1253846
Posted Friday, February 17, 2012 11:52 AM



Group: General Forum Members
Last Login: Monday, October 17, 2016 2:18 PM
Points: 121, Visits: 683
Brett Robson (5/20/2010)
I don't see why you guys are promotiing the use of dynamic SQL. I'm often asked in job interviews when it's appropriate to use dynamic SQL - the correct answer is never.

I have never (and will never) see dynamic SQL out perform a properly coded & compiled stored procedure. It's impossible - NOTHING gets cached.

Sorry, Brett, the correct answer is "it depends", like Gianluca and others rightly pointed out.

If it weren't for company confidentiality, I would love to post the SP that generates dynamic SQL comprising nested CTEs to solve formulae stored as SQL strings in the database that consume further formulae and/or aggregated raw data and so on, involving differing aggregate operations, AVG()/SUM()/COUNT etc. at each level, accommodating differing periodicity of inputs, requiring pre-aggregation or not, converting signal counts to digital readings or not, drawing from cached results or raw data and more - 'nuff said as to complexity? And then issue a T-SQL Challenge to come up with a solution that completely obviates dynamic SQL... Under SQL 2005 one of our formulas expands to the point where over 256 tables are involved in the query and it gets rejected upon exec... NEVER is a word that is blown out the door by this core process... And the typical dynamic SQL runs in around 0.5 seconds on multiple tens of thousands of rows drawn from a base table comprising 2,104,604,629 rows - GO DYNAMIC SQL!

If you run Kimberley Tripp's revealing cache query at Plan cache and optimizing for adhoc workloads you'll see that IF you run dynamic SQL, you CAN see plan reuse. Even the monster described above achieved plan-reuse.

Be careful that you look at every problem with an open mind...
Post #1254098
« Prev Topic | Next Topic »

Add to briefcase «««7891011

Permissions Expand / Collapse