Re: Confused about proc vs. dynamic SQL vs LINQ

Nicholas Paldino [.NET/C# MVP] wrote:

Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql.

no. Don't feed a myth.

In the case of SQL Server, you might get just as fast
performance from dynamic sql (sql that you generate on the client and
issue to the server, as opposed to executing the stored procedure
with parameters) depending on the cache (dynamic queries are cached
to a degree, and whether or not they are reused depends on how often
the cache is hit, as well as if the queries are the same and a number
of other factors). With stored procedures, the execution plan is
already compiled, and the server won't have to recompile it.

no that's not true. Execution plans are created at runtime for procs
as well as for dyn. parameterized queries. This is done to be able to
optimize queries based on the actual statistics. If one would store
compiled execution plans, a query could slow down over time because
another execution plan would be faster because statistics changed.

See BOL on execution plans and caching, there is no difference between
procs and dyn. sql in this.

Depending on the statistics that were in place when the stored
procedure was compiled, and the statistics that are in place when you
execute your dynamic sql, you might get a different execution plan
(assuming you are executing the same sql dynamically that you are
executing in the stored procedure.

I believe this is only true on DB2 these days, and they too have now
more runtime optimization than in the earlier days where procs were
converted to C, compiled and were only optimizable via hints.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: Best practices for dynamic user searches
    ... dynamic SQL statements (creating the field lists, ... > queries take parameters, and those parameters aren't executed as SQL ... >> First off make sure that the database user the app runs under can only ... >>> queries (as opposed to stored procedures) since you never know how many ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Re: Newbie: Stored Procedures and Or Datasets ?
    ... >the proc is executed for the first time, the execution plan is then cached ... whole lot of advantages over a standard ad-hoc T-SQL ... An Evaluation of Stored Procedures for the .NET Developer ... SQL Server, execution plans are cached for all T-SQL batches, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Dynamic SQL
    ... Dynamic SQL versus stored procedures is really a minimal discussion. ... Obviously if you CAN use stored procedures, you are going to have a MUCH ... With only minimal modification you could make your middle tier use stored ... > While I am not trying to start another discussion about> business rules and where they ...
    (microsoft.public.sqlserver.programming)