|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Dynamic sql statements
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Dynamic sql statements
|
|
| Anna Lin 2002-11-21, 11:24 am |
| Hi all,
we have some dynamic sql statements (using exec(sql)) in
some stored procedures. What kind of negative impact do
the dynamic sql statements have?
Thanks, Anna
| |
| Quentin Ran 2002-11-21, 12:24 pm |
| Anna,
if possible avoid dynamic sql.
It has an impact on performance since sql server has to compile the
statement every time the statements get executed, and hence can not use
pre-determined execution plan as a stored proc does.
It also requires different security settings than stored proc, when crossing
over to a different database. Explicit access needs to be assigned to the
"remote" database for the query to work.
If you have to do it dynamically, try use sp_executesql as BOL recommends.
Quentin
| |
| Hal Berenson 2002-11-21, 12:24 pm |
| The main problem with dynamic SQL is that you have to compile the statement
every time it is executed. The cost of compiling SQL statements is high, so
if you do excessive amounts of compilation your overall performance will
suffer. Having to execute an occasional dynamic SQL statement in your
application is fine, and in some cases will actually yield better
performance, so there is no general reason to avoid doing so. The key is to
not abuse dynamic SQL. If you have a lot of cases where your dynamic SQL
generates the same query with different values, try to parameterize and use
sp_executesql instead of EXEC(sql). And try to keep dynamic SQL out of your
most frequently executed transactions.
SQL Server does have a couple of features that help mitigate the compilation
cost associated with dynamic SQL, but they help in only limited cases. It
does cache plans for "ad-hoc" queries and try to match incoming queries
against them. And it does auto-parameterize many simple ad-hoc queries.
The main intent with these is to make poorly written client applications
(e.g., VB apps that were written to concatenate parameters into the query
string rather than actually submit a parameterized query) perform
reasonably. But under some conditions they will help the performance of
appropriately used dynamic SQL that happens to be able to benefit from them.
--
Hal Berenson
True Mountain Consulting
"Anna Lin" <lliming12@yahoo.com> wrote in message
news:e2c601c29180$c4fb2530$8df
82ecf@TK2MSFTNGXA02...
> Hi all,
>
> we have some dynamic sql statements (using exec(sql)) in
> some stored procedures. What kind of negative impact do
> the dynamic sql statements have?
>
> Thanks, Anna
|
|
|
|
|