LINQ Queries vs Stored Procs

F

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?
 
C

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?
Click to expand...

Is that not enough?

Cor
 
J

Jon Skeet [C# MVP]

Frank Calahan said:
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?
Click to expand...

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.)
 
C

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
 
J

Jon Skeet [C# MVP]

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

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
 
M

Michael Nemtsev [MVP]

Hello Frank,

adding to other posts, I'd recommend to consider between LINQ vs SP in the
aspect of changability.
SP logic changed easily without any code chages (if params are not changed)
and your can tune your queries easily.
for example today u have 100 rows, tomorrow 100 mils rows, in case of LINQ
there are few steps for optimizing, in terms of SP you can apply partitioning,
sectioned indexing and etc to improve your data selection

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


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

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


J> On Jan 2, 11:49 am, "Cor Ligthert [MVP]" <[email protected]>
J> wrote:
J>J> What gets cached with stored procs that isn't cached with
J> parameterised queries?
J> I'm pretty sure the execution plan is cached for both...
J> I'd certainly be interested in hearing more information, of course.
J>
J> Jon
J>
 
J

Jon Skeet [C# MVP]

Michael Nemtsev said:
it depends on DB, but generally only queries/SP with the same params are
cached
Click to expand...

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.)
 
I

Ignacio Machin \( .NET/ C# MVP \)

Hi,


I think the same, especially for SP in the form : SELECT * FROM T WHERE
.....
but if your SP has more than one statement then SP is the way to go.

Most of my SP (especially those used for reports) are complex multiqueries
using temp tables, etc. In this escenario there is no substitution.

You have two tools and each one has its own use. You just have to know when
to use one or the other
 
C

Cor Ligthert[MVP]

Ignocio,
Most of my SP (especially those used for reports) are complex multiqueries
using temp tables, etc. In this escenario there is no substitution.
Click to expand...
I agree completely with you an SP is easier to test than in C# set inline
code.

(I did not write easy, it is easier before I will be misunderstood)

Cor
 
M

Marc Gravell

LINQ for SQL does support the inclusion of SP's through method extensions to
the DataContext I believe.
Click to expand...

True; but one disadvantage here is that you lose the composable nature
of the query - i.e. the ability to apply additional filters, sorts to
the method. Depending on your scenario, this may be a pain.

For info - historically I've been largely SP-based, but I'm drifting
quite a long way with LINQ. To combine the benefits of pre-defined
queries (to avoid duplication) with composability, I'm actually
looking at constructs like (ignore names):

IQueryable<Order> ListOrdersByCustomer({keytype} customerKey) {
return whatever from wherever where foo order by something; //
insert real LINQ query here...
}

then callers can use:
var query = from order in ListOrdersByCustomer(key)
where value > minValue
order by some-other-sort

the final query will include the shared (ListOrdersByCustomer)
filters, plus my usage-specific filters,and my desired sort (only)
[with the original sort from ListOrdersByCustomer as a default if it
isn't sorted by the caller].

Marc
 
F

Frank Calahan

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 simpleSQLquery 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.)
Click to expand...

Thanks to everyone who replied to this thread. I'm simply a developer
who is trying to make smart design decisions, given new technology
that is supposed to help me do my job better. For a long time, most
of the guidance I've seen advocates the use of SPs. After reading the
responses in this thread, I took some time to research (lightly) and
see if I could find any guidance that recommended against using stored
procs. Again, everything I've found recommended using stored procs.
Here's an example of what I'm talking about. It is guidance from the
Microsoft Patterns and Practices Group titled "Improving .NET
Application Performance and Scalability":

http://msdn2.microsoft.com/en-us/library/ms998569.aspx#scalenetchapt12_topic11

I've found the syntax for calling SPs through the DataContext in LINQ
to SQL very inviting (i.e. 1 line of code vs creating a DAL method).
I can also see use in the the composable nature of LINQ to SQL, but
only for those few cases where it is necessary, but an alternative
(maybe not as easy) is to concatenate strings in code. Other than
that, why should anyone would stop using SPs and start using LINQ to
SQL queries? Can anyone provide a reference that refutes the guidance
above from the Microsoft Patterns and Practices Group?
 
J

Jon Skeet [C# MVP]

Other than that, why should anyone would stop using SPs and start
using LINQ to SQL queries? Can anyone provide a reference that
refutes the guidance above from the Microsoft Patterns and Practices
Group?
Click to expand...

The discussion I've seen isn't nearly as unbalanced as you've made out.
I won't go into all the details, but searching for "Frans Bouma"
"stored procedures" will go a long way. Alternatively, look at the
documentation for ORM projects (including the one Frans develops).
They're obviously bias in the other direction.

I'm very surprised to see the patterns and practices group put out what
looks like FUD. Let's see:

o Execution plan optimization and caching: this is done for prepared
statements too

o Passing less information across the network: yeah, like the size of
your SQL statement (excluding parameters which would have to be passed
in either way) is likely to be in any way significant traffic

o Putting responsibility into the hands of SQL experts: true, although
there's no reason not to get your DBAs to review your LINQ queries too.
In my experience, putting in this extra layer *mostly* results in a
lack of flexibility: if I want to do anything even slightly different
to what's already been done, you have to go to a lot more trouble with
the "always use SPs" mantra.

o Maintenance and security benefits: in certain situations I agree
about security. Personally I'd rather maintain code than stored procs,
although it's often easier to deploy a fixed stored proc once than
update all clients, of course.

o Countermeasure for SQL injection: not an issue for LINQ to SQL (or
any decent ORM or use of parameterized SQL) in the first place


Now, which issue are you actually concerned about? If it's performance,
I suggest you try it against *your* data, as that's what's going to be
really important.

In some cases stored procs certainly are the best way to go - but all
of the processing can be expressed in a single query statement, I
suspect you'll usually find that a tuned stored proc and a tuned LINQ
to SQL query will be very similar in performance. The tuning of both is
important, however. Also make sure when you do performance tests that
you take account of the slight hit incurred the first time a particular
query is executed in LINQ to SQL - don't just do one query and time
that; time lots of them.
 
P

Paul Shapiro

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. Stored
procedures eliminate most sql injection attacks, which are more possible
with dynamic sql.

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.
 
M

Marc Gravell

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.
Click to expand...
Just a counter-point; historically I've used SPs almost exclusively,
and a common need is a flexible "search", which even using the SP
approach often means building dynamic SQL and executing via
sp_ExecuteSQL - which requires read permissions *OR* an involved
signing process. But yes; it can better mitigate against INS/UPD/DEL.
Stored procedures eliminate most sql injection attacks, which are more possible with dynamic sql.
Click to expand...
Only if used correctly! "EXEC" is the obvious one, but I've also seen
somebody call an SP by building a CommandText along the lines of
"MyProc '" + argString +"'". Welcome back injection. So yes, an SP
called via parameters will be injection safe - but identically so will
a non-SP command that uses parameters. And any decent ORM (including
LINQ) will do that automatically.
It separates the database processing details from the application processing.
Click to expand...
I'd agree that it formalises an API to the database - yet even if the
SP name and args are formalised, the return grid(s) [perhaps the most
important part?] aren't. But it doesn't really separate it - it just
means you have to do it all twice; once in SQL, and once again in C#
to prepare the commands and (more verbosely) consume the data. You can
arguably use ORM to bind to the SPs, but you lose all flexibility and
composability.

Additionally, just look at the ease (and performance) with which LINQ
can sort and page a query. Now try that in SQL... OK, the paging you
can do *reasonably* well in SQL Server 2005+ with CTE etc (and some
other vendors have direct support); the flexible sort? Either a
horrible SELECT CASE, or dynamic SQL (in the SP); but you're very much
back into the world of vendor-specific tricks. Hopefully an ORM tool
(and I'm thinking "Entity Framework"/"LINQ-to-Entity", not LINQ-to-
SQL, for cross-targeting here) will do this (using the appropriate
optimised approach) for free.

Personally I'm quite a big fan of LINQ - and historically I've batted
quite heavily on the "use SPs" team, so I know the arguments for both
sides (and there are *definitey* some good cases where SPs are the
only route). But IMO, there hasn't been a bigger opportunity to at
least call a truce, put aside historical prejudices, and ask: "does
this work for me? how do I want to manage my DAL?". I was surprised...
it really seems to help me...

Marc
 
J

Jon Skeet [C# MVP]

Paul Shapiro said:
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
F

Frans Bouma [C# MVP]

Paul said:
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. Stored procedures eliminate
most sql injection attacks, which are more possible with dynamic sql.
Click to expand...

Injection attacks with dyn. sql are only possible if you don't use
parameters. If you DO use parameters (and you should), your claim is
void.

Also about the security aspects: it's not as you claim it to be: I can
also specify security on tables via roles for dyn. sql. Also, if I need
to return only a few columns based on user, I can also create a view
for that.

The thing with 'security' and procs is that it isn't going to help: if
I as a user in Marketing have to use your app which uses procs and I
have to be able to delete a customer from the db, your proc will be:
pr_DeleteCustomer @customerID

I can call that proc with whatever ID I want from query
analyzer/whatever querying tool available.

Also, if you place business logic outside the DB, your application
(which runs under the user I can use to call all your procs) is going
to call more procs as it has to obtain more data (as processing takes
place outside the db). I.o.w.: procs won't help here.

But this topic has been beaten to death a gazillion times :)

FB
>Frank Calahan
Click to expand...



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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Critique my LINQ to SQL strategy using stored procs, please 5
Dynamic SQL instead of stored procs? 9
LINQ- Compiled queries and data layers 2
linq to sql for stored proc with user defined table as parameter 6
linq troubles 5
Discussion: linq vs stored procedures 6
Advantages of going with LINQ 2
Linq to Entity, query sql 5

Top