Re: Static vs. Dynamic SQL for dynamic filtering

Question

Re: Static vs. Dynamic SQL for dynamic filtering

jlp3630

Hall of Fame

Points: 3243
More actions
October 1, 2008 at 8:08 am

#191567

One of our applications allows dynamic filtering based on user inputs. For example, a user can select 6 filter combinations for 3 possible filter criteria. However, most of these inputs will be integers (with strings represented as integer keys) and datetime. What are the advantages/disadvantages of using dynamic SQL (i.e., EXEC or sp_executesql()) versus static SQL solutions (i.e., (1) col_a = @a OR @a IS NULL; (2) col_a = COALESCE(@a, col_a))?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Glen SSCertifiable Points: 6737 More actions · Answer 1

It is more of question of what advantages/disadvantages AREAS you are looking for? (i.e. Speed, Maintainability, Architecture and Design etc.)

The discussion "Is dynamic SQL better than Stored Procedures" is as old as SQL itself (wrong... at least after SQL 97). However, very few conversations are defining what means "better"?

There is a set of major differences between stored procedures and dynamic SQL. You can read about them here:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1214541,00.html

Personally, in your particular case scenario I would use a stored procedure, since in the case you are describing (and considering a multiuser environment) it will be faster (overhead of dynamic SQL against cashed execution plan).

jlp3630 Hall of Fame Points: 3243 More actions · Answer 2

Glen (10/1/2008)
It is more of question of what advantages/disadvantages AREAS you are looking for? (i.e. Speed, Maintainability, Architecture and Design etc.)
The discussion "Is dynamic SQL better than Stored Procedures" is as old as SQL itself (wrong... at least after SQL 97). However, very few conversations are defining what means "better"?
There is a set of major differences between stored procedures and dynamic SQL. You can read about them here:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1214541,00.html
Personally, in your particular case scenario I would use a stored procedure, since in the case you are describing (and considering a multiuser environment) it will be faster (overhead of dynamic SQL against cashed execution plan).

I'm not sure if I understand you. Both of my solutions (static and dynamic SQL) will be implemented via stored procedures. However, I'm not sure which is better.

Here's what I plan to do (untested code):

Static SQL:

SELECT *

FROM test

WHERE (col_a = @a OR @a IS NULL) AND (col_b = @b-2 OR @b-2 IS NULL)

Dynamic SQL:

DECLARE @sql AS NVARCHAR(2000);

SET @sql = N'SELECT *'

+ N'FROMtest'

+ N'WHERE 1=1'

+ CASE WHEN @a IS NOT NULL THEN

+ N' AND col_a = @a' ELSE N'' END

+ CASE WHEN @b-2 IS NOT NULL THEN

+ N' AND col_b = @b-2' ELSE N'' END;

EXEC sp_executesql @sql, N'@a_value AS INT, @b_value AS INT', @a_value = @a, @b_value = @b-2;

Glen SSCertifiable Points: 6737 More actions · Answer 3

Sorry,

I didn't understand your original question then.

To make a long story short, I am using a simple rule of thumb:

If you can avoid using dynamic SQL in stored procedure - do it.

In your particular case I would say that the code you are trying to use is the same. I do not see any disadvantages, neither I can see any advantages of using dynamic SQL.

It is simply that the first code snippet is cleaner. (BTW, your CASE syntax is incorrect).

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

The primary concern is performance, versus maintainability & prevention of SQL injection attacks, right ?

Any SQL like this:

WHERE (col_a = @a OR @a IS NULL) AND (col_b = @b-2 OR @b-2 IS NULL)

.. will typically execute slower than the dynamically built equivalent, because the OR's will cause the optimizer not to index seek.

So it's a grey area, and every situation is different. If data volumes are small & the performance difference not huge, then I'd avoid dynamic SQL, but if you're finding orders of magnitude type difference, then go dynamic.

I maintain a web app that uses dynamic SQL in this way. The approach was to run profiler & only implement dynamic SQL on the queries that ran extremely slowly, or that were executed most frequently and were having the highest impact to user experience.

Glen SSCertifiable Points: 6737 More actions · Answer 5

PW, you are actually comparing running dynamic SQL against running a stored procedure. Using dynamic SQL within a stored procedure will have the same execution plan...

jlp3630 Hall of Fame Points: 3243 More actions · Answer 6

PW (10/1/2008)
The primary concern is performance, versus maintainability & prevention of SQL injection attacks, right ?
Any SQL like this:
WHERE (col_a = @a OR @a IS NULL) AND (col_b = @b-2 OR @b-2 IS NULL)
.. will typically execute slower than the dynamically built equivalent, because the OR's will cause the optimizer not to index seek.
So it's a grey area, and every situation is different. If data volumes are small & the performance difference not huge, then I'd avoid dynamic SQL, but if you're finding orders of magnitude type difference, then go dynamic.
I maintain a web app that uses dynamic SQL in this way. The approach was to run profiler & only implement dynamic SQL on the queries that ran extremely slowly, or that were executed most frequently and were having the highest impact to user experience.

Yeah, that's what I'm trying to figure out. Thanks for your input.