Discussion: linq vs stored procedures

  • Thread starter Thread starter Arjen
  • Start date Start date
A

Arjen

Hi,

My experience with linq is that I can develop my web application very fast.
On the other hand, I have read that using stored procedures are executing
faster.

Is it smart to use linq for a heavily used web application, let's say for a
social network application?
Or is the performance difference between linq and stored procedures to
small? And what about the future differences, will linq technology be faster
in the future?

Thanks you.
 
Arjen said:
Hi,

My experience with linq is that I can develop my web application very
fast. On the other hand, I have read that using stored procedures are
executing faster.

Is it smart to use linq for a heavily used web application, let's say
for a social network application?
Or is the performance difference between linq and stored procedures to
small? And what about the future differences, will linq technology be
faster in the future?

Thanks you.
Click to expand...

The advantage of stored procedures is that you are sure that the query
always looks the same, so the database will cache the execution plan and
the result.

Also, it's a bit easier to optimise the query, as you can easily run the
stored procecdure and see the execution plan. It's harder to predict
what the query generated by LINQ will look like, and how to change the
code to get it to generate the desired query.
 
Not an easy one; in addition to Göran's points, you also have a little
more control (re security) with SPs, but I like LINQ / ORM ;-p (I used
to be an SP junkie...). You also get the benefits of composabiltiy
with LINQ, which can be very welcome - but means you can't guarantee
what your final queries look like. Double edged.

Marc
 
Göran Andersson said:
The advantage of stored procedures is that you are sure that the query
always looks the same, so the database will cache the execution plan and
the result.

Also, it's a bit easier to optimise the query, as you can easily run the
stored procecdure and see the execution plan. It's harder to predict what
the query generated by LINQ will look like, and how to change the code to
get it to generate the desired query.
Click to expand...


Hi Göran, hi Marc,

What about a combination?
For select statements stored procedures and for create, update, and delete
statements linq?

What is your opinion?

Thanks again!

Arjen
 
Arjen said:
Hi,

My experience with linq is that I can develop my web application very
fast. On the other hand, I have read that using stored procedures are
executing faster.
Click to expand...

sp's aren't executed faster. Both (dynamic queries with parameters and
procs) are compiled to an execution plan, and that execution plan is ran
and also cached. So the second time you call the same proc, or run the
same dyn. query, the db will check it's cache and will re-use the
execution plan. If the execution plan is still valid, it will run it
again, if not (statistics changed etc.) it will recompile the query, be
it a proc or a dyn. query.

So there's no difference in execution speed. Anyone telling you that is
either believing the myth or lying.

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#)
------------------------------------------------------------------------
 
Anyone telling you that is either believing the myth or lying.

Or just believing the old truth; "back in the day" there were more
significant performance benefits in stored procedures, but things are
a lot more even these days. I say this just for the OPs benefit (I'm
fairly certain Frans could easily trump me on database knowledge...).

Marc
 
Marc Gravell said:
Or just believing the old truth; "back in the day" there were more
significant performance benefits in stored procedures, but things are
a lot more even these days. I say this just for the OPs benefit (I'm
fairly certain Frans could easily trump me on database knowledge...).

Marc
Click to expand...


Okay, thank you both.

Arjen
 
Back
Top