How often do you really need dynamic t-SQL?

Question

How often do you really need dynamic t-SQL?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Jeff Moden (3/23/2011)
opc.three (3/22/2011)
In general dynamic sql is a bad idea and you should avoid it...
In general, I disagree. 😀 There are some very easy steps that can be taken to prevent SQL Injection and to make execution plans reusable.
Heh... now... if you had said "In general, EMBEDDED SQL is a bad idea", then I'd come closer to agreeing. 😉
I do agree that certain types of "catch all" queries are a bad thing, but not always. "It Depends". :hehe:

"In general" ...shame on me for even using that phrase in this forum. The intent behind the comment was...too much...anyway, the problems with the misuse and overuse of dynamic SQL are well documented.

At the risk of continuing to brutalize the proverbial horse any longer I think we can all agree on one thing...it depends so research your approach based on your environment variables, test your code and then test your code again.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

grahamc SSCertifiable Points: 6762 More actions · Answer 2

I agree with the statements about the catch-all query...

From past experience, we had a couple of search queries that were created with a generator of some sorts that basically created about 20-25 stored procedures that would build SELECT statements based on a multitude of options in the frontend.

Some of the queries would be fast, so would timeout (app).

Solution (for us) was to take the top 5 searches used (which turned out to equate to about 90% of all search queries) we then built "quick search" screen for app (which was a wrapper proc which called 3 or 4 basic procedures with the appropriate SQL statements) with a button to the old search screen.

Took a few weeks for them to start using it properly, but the improvements were brilliant :w00t:

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

GilaMonster (3/23/2011)
Discussed here - http://sqlinthewild.co.za/index.php/2011/03/18/q-a-from-24-hours-of-pass-session/

I’m actually in favour of nulls in a database. If there’s no data for a column I don’t want some made up value to indicate that the data is missing.

Ok, Gail... I'm officially "in love". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Here too is an article from Kim Tripp on the topic: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/entryid/76182/dynamic-sql-vs-option-recompile

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 5

I was recently surprised to find that, for dynamic Where clauses, the dynamic SQL performed better and made better use of indexes. This was pointed out to me by a co-worker. I was suggesting the use of coalesce or something like @Cus_Name IS NULL OR Cus_Name = @Cus_Name in the Where clause.

Neither one of these solutions (COALESCE or (@A is NULL or A=@A)) were good solutions for this type of search on tables of this size. We did tests with all 3 methods. The second method is significantly faster than the first (5-10 seconds instead of minutes), but a dynamic SQL with only the where clauses you need is much faster (subsecond).

It appears that the best solution for our test was to build Dynamic SQL strings with the parameters you need. Its more work but it’s the fastest solution. Oh, and make sure they are parameterized SQL, not SQL with hardcoded values (using sp_executesql with @params), that way there can be some plan reuse.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

... or not. I've found that sometimes it's more important to prevent parameter sniffing by letting it go ahead and recompile. "It Depends". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)