Confused about proc vs. dynamic SQL vs LINQ

[Ronald S. Cook]

I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
- is the more proper tier to put it since is a data function

But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Thanks for any insight,
Ron

 

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

Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql. 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. 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.

Now LINQ (when coupled with SQL, in LINQ to SQL, or with ADO.NET
Entities with LINQ to Entities) will rely completely on dynamic sql. I'd
expect the sql that is generated to be pretty performant, if issued against
SQL Server, and probably not too much of a difference performance wise
(depending on your load) if you are doing single-statement inserts, updates,
and deletes.

LINQ to objects, LINQ to XML, and anything that works on objects in
memory is a different technology, one which doesn't attach to a database at
all.

Hope this helps.

 

[Ronald S. Cook]

Thanks very much for the reply. Good info.

Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql. 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. 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.

Now LINQ (when coupled with SQL, in LINQ to SQL, or with ADO.NET
Entities with LINQ to Entities) will rely completely on dynamic sql. I'd
expect the sql that is generated to be pretty performant, if issued
against SQL Server, and probably not too much of a difference performance
wise (depending on your load) if you are doing single-statement inserts,
updates, and deletes.

LINQ to objects, LINQ to XML, and anything that works on objects in
memory is a different technology, one which doesn't attach to a database
at all.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've always been taught that stored procedures are better than writing
SQL in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
- is the more proper tier to put it since is a data function

But then I've heard that writing SQL in my client .NET code might run
just as fast? Dynamic SQL or something?

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Thanks for any insight,
Ron

 

[Jon Skeet [C# MVP]]

I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:

- runs faster as is compiled and lives on the database server

It depends what you're doing, of course, but on many databases they'll
go through the same optimisation steps, and if you make several calls
to queries which are the same other than parameters, that optimisation
is cached.

- is the more proper tier to put it since is a data function

It depends what you're trying to do. I've seen plenty of situations
where people create stored procs which *just* call normal CRUD
operations, for no reasons other than dogma. Yes, there's the
theoretical "change the database and the procs implementation, don't
change the calling code" argument - but in my experience the change is
often widespread enough to require changes in the client code anyway.

Note that there's nothing stopping you from having a layer in your
application which is solely about data, but happens to be running in
the web server (or whatever) rather than in the DB.

I can see two primary reasons to use stored procs:
1) More finely grained security - give users access to specific stored
procs, rather than a whole table, etc.
2) When the stored proc needs to do a lot of work with the data but
doesn't actually need much of the data to come out of the other end,
thus vastly reducing network traffic.

But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?

It depends on the database server, but I believe that using a cached
parameterised query tends to be as fast as a simple "just CRUD" stored
proc on most of the major database platforms these days.

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Well, it's a way of dynamically creating SQL and maintaining a degree
of compile-time checking. It's another step in the ORM journey - but
ORM's been around for a while now.

See http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx for
more details "against" stored procs.

 

[Frans Bouma [C# MVP]]

I've always been taught that stored procedures are better than
writing SQL in client code for a number of reasons:

- runs faster as is compiled and lives on the database server

Modern RDBMSs don't compile stored procs anymore, they evaluate any
query at runtime to execution plans which are optimized at runtime, not
at compile time. So, no, stored procedures aren't faster and no they're
not stored in compiled form.

- is the more proper tier to put it since is a data function

I think opinions differ on that

But then I've heard that writing SQL in my client .NET code might run
just as fast? Dynamic SQL or something?

parameterized queries are as fast as procs and can be faster as you
can tailor dyn. SQL (parameterized!) to the job you have to do, so if
you have a table with 10 fields, you don't have to write a single
UPDATE proc which accepts values for all fields to update, you can
write an update query which updates just 1 field.

And then there's LINQ on the horizon. Is it a successor to
everything previously mentioned?

Linq is a language construct and the result of linq expressions,
expression trees, can be used to generate dyn. SQL at runtime, like any
other O/R mapper does these days.

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#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

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#)
------------------------------------------------------------------------

 

[Frans Bouma [C# MVP]]

See http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx for
more details "against" stored procs.

For a more clearer overview:
http://weblogs.asp.net/fbouma/archive/2006/05/26/Yay_2100_-A-new-Stored-
Proc-vs.-Dyn.-Sql-battle_2100_.aspx

The 'procs are bad, m'kay?' article was a bit tongue in cheek as well,
so please read
http://www.theserverside.net/news/thread.tss?thread_id=31953#158113
which is more serious.

Procs aren't all bad of course, Jon gave a great example when they're
useful:
" 2) When the stored proc needs to do a lot of work with the data but
doesn't actually need much of the data to come out of the other end,
thus vastly reducing network traffic."

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#)
------------------------------------------------------------------------

 

[Andy]

Generally speaking, stored procedures are going to be faster than
dynamic sql. 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. 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.

As of Sql Server 7, that's not true. Sql Server doesn't 'compile'
stored procedures at all. It just checks syntax. Either method you
use will cause sql to create an execution plan and cache it. After
that, it will used the cached plan either way. Check the BOL.