When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName NVARCHAR(40) = NULL, @SearchLocation NVARCHAR(100) = NULL, @SearchReputation INT = NULL AS BEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 '; IF @SearchDisplayName IS NOT NULL SET @StringToExecute = @StringToExecute + N' AND DisplayName LIKE @SearchDisplayName '; IF @SearchLocation IS NOT NULL SET @StringToExecute = @StringToExecute + N' AND Location LIKE @SearchLocation '; IF @SearchReputation IS NOT NULL SET @StringToExecute = @StringToExecute + N' AND Reputation = @SearchReputation '; EXEC sp_executesql @StringToExecute, N'@SearchDisplayName NVARCHAR(100), @SearchLocation NVARCHAR(100), @SearchReputation INT', @SearchDisplayName, @SearchLocation, @SearchReputation; END GO |
The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all the parameter combinations.
But what if a lot of the queries look similar?
For example, what if your search page has a set of defaults, and the vast, vast majority of searches just use the defaults?
In our example above, what if the vast majority of the time, people always searched for users in London whose Reputation = 1? There’s no reason we can’t put a short-circuit at the top of our stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName NVARCHAR(40) = NULL, @SearchLocation NVARCHAR(100) = NULL, @SearchReputation INT = NULL AS BEGIN IF @SearchLocation = 'London' AND @SearchReputation = 1 AND @SearchDisplayName IS NULL EXEC usp_SearchUsers_WelcomeToLondon; DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE 1 = 1 '; (...the rest continues normally) |
In complex stored procedures, this short circuit buys me a few cool advantages:
You can build a hand-crafted query. You can restructure the T-SQL to be more performant, add index hints, query hints, trace flags, you name it, things you wouldn’t normally want to add to dynamic SQL on the fly.
You can get the data from somewhere different. For example, at one client, 99% of product searches were looking for the “Item of the Day” on sale – and that product list only changed once per day. We simply built a new table with the necessary product info, populated it once per day on a schedule, and changed the search procedure to something like this (obviously with a different table name):
|
1 2 |
IF @SearchFeaturedProduct = 1 SELECT * FROM dbo.FeaturedProductsTableUpdatedOnceDaily; |
For that client, it made a phenomenal difference in eliminating joins, memory grants, permissions checks, and sorts. (The table only had one row in it.)
You skip the overhead of building a dynamic string. I know it sounds trivial, but when you’re dealing with tens of thousands of queries per second, this can be a big deal.
How to know if this tactic makes sense
Warning: run-on sentence incoming.
- If you run sp_BlitzCache and your top most resource-intensive queries use dynamic SQL,
- And a little further down in the results, you see a dynamic string that was built by one of these procs,
- And when you compare the outer proc’s resource utilization numbers (CPU, reads, duration, etc) number to the dynamic SQL’s resource utilization, and it looks like that one dynamic SQL line is a big chunk,
- And you think you could do better tuning that particular branch of dynamic SQL by hand,
- And the other branches wouldn’t benefit from you just tuning the main string itself,
- Then it’s a good fit.
7 Comments. Leave new
> You skip the overhead of building a dynamic string. I know
> it sounds trivial, but when you’re dealing with tens of
> thousands of queries per second, this can be a big deal.
It’s not the string building that can be sped up. It’s advisable not to use dynamic t-sql in
usp_SearchUsers_WelcomeToLondonat all so that it skips recompilation.Skipping recompilation is especially useful when short-circuit query is expected to return 1 row. For instance if @SearchDisplayName contains no wildcard characters it’s an exact match single-row filtering. On large tables recompilation of dynamic t-sql might take hundreds of ms and then the execution of this single-row query takes less than 1ms.
So skipping recompilation for single-row queries by not using dynamic t-sql is what we do here all the time.
Absolutely agree! That’s what I meant by “skip the overhead of building a dynamic string” – if you’re not doing dynamic SQL in that proc, there’s no string-building overhead in it.
Some time ago I built an inceptional (c) procedure to track the duration of my dynamic SQL Statements, nothing I would use on heavy load OLTP Systems, but it can help with debugging/tracking dynamic code
https://tsql.tech/tracking-dynamic-sql-performance-automatically-with-a-wrapper-on-sp_executesql-dynamic-sql-inception/
There’s always a trade-off. You may need to update both of those stored procedures when fixing bugs, changing logic, etc… In other words, it may be easy to fix a bug in the main stored procedure and forget to check/fix the specific stored procedure.
Great post!
What about spezifying the where clause to
WHERE
(DisplayName = @SearchDisplayName OR @SearchDisplayName IS NULL)
AND (Location = @SearchLocation or @SearchLocation IS null)
AND (Reputation = @SearchReputation or @SearchReputation IS NULL)
Wouldn’t this be any easier solution?
Thanks! I’m afraid I have terrible news though – we cover why that’s the most horrific performance problem in my Mastering Query Tuning class, but if you don’t mind running a few demos yourself in Stack, you can get a sneak peek of the problem here: https://www.brentozar.com/sql/dynamic/
That does some nasty things to the plan:
https://www.brentozar.com/archive/2017/09/optional-parameters-missing-index-requests/
https://www.brentozar.com/archive/2018/09/a-simple-stored-procedure-pattern-to-avoid/