September 20, 2016 at 2:55 am
Hi all,
I'm just reading some a document on SQL coding best practice and came across this which I usually tell developers to do.
Including complex conditionals in
the WHERE clause
It is tempting to produce queries in routines that have
complex conditionals in the WHERE clause where
variables are used for filtering rows. Usually this is done
so that a range of filtering conditions can be passed
as parameters to a stored procedure or tale-valued
function.[/u]
What is a good alternative to this.
September 20, 2016 at 5:59 am
I'm not completely sure I understand the question.
You're saying that you use local variables instead of parameters?
What happens with this is that you are taking away the optimizers ability to sniff parameters, sample the values. The optimizer uses the values in a parameter to look at the statistics and get more precise row counts based on the value. Most of the time, in most situations, this is a very good thing. Occasionally, when the statistics are out of date or there is skew (abnormal distribution of the data), you can get bad parameter sniffing. However, as painful as bad parameter sniffing can be, most of the parameter sniffing is good. I recommend using parameters over variables for this reason.
What happens with variables is that an average of the row count is taken instead of specific values. In some cases, this is helpful, but in most cases, it's less accurate. For the optimizer to make the best possible choices, it needs to be as accurate as it can be and parameters do this better than variables.
Oh, and there is such a thing as variable sniffing. When a recompile event occurs, variable values can be sniffed in the same way as parameter values, potentially changing the query plan produced by the optimizer.
In general, the safer approach is to use parameters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2016 at 10:19 am
Could it be referring to something like a catch-all query?
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
September 20, 2016 at 10:43 am
I know what you're talking about. If you have a stored procedure that accepts multiple parameters for performing searches (it seems every CRM type application has one), then dynamic SQL execution using SP_EXECUTESQL can be a better alternative to a complex WHERE clause with a complex bag of conditional expressions. Even if you pay upfront at runtime for compiling a non-cached plan, then execution plan can be more simple and optimized.
How to Design, Build and Test a Dynamic Search Stored Procedure
http://www.sqlservercentral.com/articles/T-SQL/103529/
Also, this article weighs the pros and cons of dynamic sql.
http://www.sommarskog.se/dynamic_sql.html#FirstEncounter
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply