MS SQL Server - Parameterized adhoc SQL v.s. non-parameterized

Author

Parameterized adhoc SQL v.s. non-parameterized

nickdu

2006-11-15, 7:14 pm

Does parameterized adhoc SQL stand a greater chance of being optimized
(server being able to match against existing compiled code) than
non-parameterized adhoc SQL. By the way, I'm not a database guy so forgive
me if none of this makes sense. As an example, if I have the following two
queries:

1. Non parameterized:

select * from tblClientOrder where clientOrderId='<some guid here>'

2. Parameterized:

select * from tblClientOrder where clientOrderId=@clien
tOrderId

And there is an index on clientOrderId. Will each of these queries execute
the same? Any reason to choose one over the other?
--
Thanks,
Nick

nicknospamdu@communi
ty.nospam
remove "nospam" change community. to msn.com

Uri Dimant

2006-11-15, 7:14 pm

In very very general , the first one will more efficient , because SQL
Server knows the real values

Google "parameter sniffinga" and you'll find more info

"nickdu" < nicknospamdu@communi
ty.nospam> wrote in message
news:9429ECEA-0C5A-4990-882A- 45847905FF6F@microso
ft.com...
> Does parameterized adhoc SQL stand a greater chance of being optimized
> (server being able to match against existing compiled code) than
> non-parameterized adhoc SQL. By the way, I'm not a database guy so
> forgive
> me if none of this makes sense. As an example, if I have the following
> two
> queries:
>
> 1. Non parameterized:
>
> select * from tblClientOrder where clientOrderId='<some guid here>'
>
> 2. Parameterized:
>
> select * from tblClientOrder where clientOrderId=@clien
tOrderId
>
> And there is an index on clientOrderId. Will each of these queries
> execute
> the same? Any reason to choose one over the other?
> --
> Thanks,
> Nick
>
> nicknospamdu@communi
ty.nospam
> remove "nospam" change community. to msn.com

Dan Guzman

2006-11-15, 7:14 pm

> Does parameterized adhoc SQL stand a greater chance of being optimized
> (server being able to match against existing compiled code) than
> non-parameterized adhoc SQL.

Parameterization greatly increases the likelihood that an existing plan can
be reused, avoiding the overhead of compilation. It is especially important
to avoid (re)compilation in high-volume OLTP environments. Parameterized
SQL is more secure and you don't have to be concerned with formatting string
literals (doubling up single quotes and formatting dates). It is also
common to use stored procedures to encourage parameterization and execution
plan reuse.

Optimization is the process of generating the execution plan during query
compilation. As Uri mentioned, SQL Server can do the best job when the
actual values are known. When the actual query values are unknown (e.g.
parameterized query), SQL Server estimates typical values based on existing
data statistics and generates the appropriate plan. This plan will be good
for most queries, However, there might no single plan that will optimal for
all possible values so that plan may be suboptimal for the exceptional
values. That said, the query plan for the example you posted is most likely
considered trivial and won't be cached at all or may be auto-parameterized.

Check out the very good white paper at
http://www.microsoft.com/technet/pr...005/recomp.mspx for more
information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"nickdu" < nicknospamdu@communi
ty.nospam> wrote in message
news:9429ECEA-0C5A-4990-882A- 45847905FF6F@microso
ft.com...
> Does parameterized adhoc SQL stand a greater chance of being optimized
> (server being able to match against existing compiled code) than
> non-parameterized adhoc SQL. By the way, I'm not a database guy so
> forgive
> me if none of this makes sense. As an example, if I have the following
> two
> queries:
>
> 1. Non parameterized:
>
> select * from tblClientOrder where clientOrderId='<some guid here>'
>
> 2. Parameterized:
>
> select * from tblClientOrder where clientOrderId=@clien
tOrderId
>
> And there is an index on clientOrderId. Will each of these queries
> execute
> the same? Any reason to choose one over the other?
> --
> Thanks,
> Nick
>
> nicknospamdu@communi
ty.nospam
> remove "nospam" change community. to msn.com

Sponsored Links