LINQ Queries vs Stored Procs

[Frank Calahan]

I've been looking at LINQ and it seems very nice to be able to make
queries in code, but I use stored procs for efficiency. If LINQ to SQL
only works with SQL Server and stored procs are more efficient, what
use is LINQ to SQL, other than to have a simpler way to call my stored
proc?

[Cor Ligthert[MVP]]

I've been looking at LINQ and it seems very nice to be able to make

queries in code, but I use stored procs for efficiency. If LINQ to SQL
only works with SQL Server and stored procs are more efficient, what
use is LINQ to SQL, other than to have a simpler way to call my stored
proc?

Is that not enough?

Cor

[Jon Skeet [C# MVP]]

Frank Calahan <ic***********@gmail.comwrote:

I've been looking at LINQ and it seems very nice to be able to make
queries in code, but I use stored procs for efficiency. If LINQ to SQL
only works with SQL Server and stored procs are more efficient, what
use is LINQ to SQL, other than to have a simpler way to call my stored
proc?

How convinced are you that your stored procs are actually improving
efficiency? If they're complex procs which are actually processing data
in a way that a simple SQL query wouldn't be able to then they will
indeed be helping - but if you've got stored procs for simple CRUD,
you're unlikely to be getting much benefit. (I believe stored procs and
parameterised queries go through much the same optimisation paths, are
cached etc.)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

[Cor Ligthert [MVP]]

Jon,

AFAIK does the internal caching of the SQL server makes stored procedures a
very little bit more efficient.
(However that applies often used procedures)

Cor

[Jon Skeet [C# MVP]]

On Jan 2, 11:49 am, "Cor Ligthert [MVP]" <[e-mail]>
wrote:

AFAIK does the internal caching of the SQL server makes stored procedures a
very little bit more efficient.
(However that applies often used procedures)

What gets cached with stored procs that isn't cached with
parameterised queries?
I'm pretty sure the execution plan is cached for both...

I'd certainly be interested in hearing more information, of course.

Jon

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

Parameterized queries in SQL Server are cached and re-used, and there is
normally little difference in efficiency vs. stored procedures.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com
"Frank Calahan" wrote:

I've been looking at LINQ and it seems very nice to be able to make
queries in code, but I use stored procs for efficiency. If LINQ to SQL
only works with SQL Server and stored procs are more efficient, what
use is LINQ to SQL, other than to have a simpler way to call my stored
proc?

[Michael Nemtsev [MVP]]

Hello Jon Skeet [C# MVP],

it depends on DB, but generally only queries/SP with the same params are
cached

---
WBR,
Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo
JOn Jan 2, 11:49 am, "Cor Ligthert [MVP]" <[e-mail]>
Jwrote:
J>

>AFAIK does the internal caching of the SQL server makes stored
procedures a
very little bit more efficient.
(However that applies often used procedures)

JWhat gets cached with stored procs that isn't cached with
Jparameterised queries?
JI'm pretty sure the execution plan is cached for both...
JI'd certainly be interested in hearing more information, of course.
J>
JJon
J>

[Jon Skeet [C# MVP]]

Michael Nemtsev [MVP] <ne*****@msn.comwrote:

it depends on DB, but generally only queries/SP with the same params are
cached

I understood it to be queries with the same parameter placeholders, but
not the same *values* of parameters.

(This can be a distinct disadvantage occasionally, if a query with one
parameter value should actually use a very different execution plan to
the same query with a different parameter value.)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

[Jon Skeet [C# MVP]]

Paul Shapiro <pa**@hideme.broadwayData.comwrote:

I think the security issue is the big one. If ALL data access is via Stored
Procedures, you can limit the user to only executing stored procedures,
without allowing read permissions on any tables. If you have well-defined
application roles, you can group users into role-based security groups and
assign the minimally-necessary permissions on the stored procedures.

True. Where that level of security is actually necessary, stored procs
are great. Where they're not, I think they're an extra overhead.

Stored
procedures eliminate most sql injection attacks, which are more possible
with dynamic sql.

Can you show me a SQL injection attack with LINQ to SQL, which is the
dynamic sql option here?

I personally prefer putting sql into stored procs instead of the application
because I find it a more effective way to organize the code. It separates
the database processing details from the application processing. But I think
that's just personal preference.

That sounds great in theory, but I think it adds an extra overhead if
you need flexibility of search. It can also be a real pain in cases
where you need really dynamic queries, e.g. allowing users to specify
multiple criteria in a flexible way.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk