Re: LINQ Queries vs Stored Procs

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Frank Calahan <ichbineinhund@xxxxxxxxx> wrote:

<snip>

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?

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.

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

Relevant Pages

  • Re: Are Linq-SQL methods commutative
    ... because to translate what is being done in LINQ to ... of the previous query. ... the intention of the linq query with SQL. ... get 2 different resultsets by specifying them in a different order, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Building expression trees to filter derived entity classes
    ... up the inheritance correctly in your LINQ to SQL mapping? ... you project the your LINQ to SQL entity objects onto some other POCO ... LINQ to SQL is still trying to process the entire query, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Are Linq-SQL methods commutative
    ... because to translate what is being done in LINQ to database ... you would actually perform a query on the result set of the previous ... SQL Server, then the ordering of the result set will be on that index, ... But I agree with you, in database land, it makes no sense, because you ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: LINQ to SQL vs. Stored Procedures (Performance)
    ... If it is an oft used LINQ ... query, you will find that SQL Server creates stats and the query will run ... Stored Procedure and a LINQ to SQL command, ...
    (microsoft.public.dotnet.framework)
  • Re: Are Linq-SQL methods commutative
    ... No - in LINQ each step effectively works off the result of the ... linq query which has to meet a SQL query they have in mind. ... freedom a SQL statement gives them: the SQL statement is the one ...
    (microsoft.public.dotnet.languages.csharp)