Discussion: linq vs stored procedures

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

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Arjen wrote:

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.

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.

--
Göran Andersson
_____
http://www.guffa.com

[Marc Gravell]

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

[Arjen]

"Göran Andersson" <gu***@guffa.co mschreef in bericht
news:uQ******** ******@TK2MSFTN GP05.phx.gbl...

Arjen wrote:

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

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.

--
Göran Andersson
_____
http://www.guffa.com

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

[Frans Bouma [C# MVP]]

Arjen wrote:

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.

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

[Marc Gravell]

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

[Arjen]

"Marc Gravell" <ma**********@g mail.comschreef in bericht
news:32******** *************** ***********@c65 g2000hsa.google groups.com...

>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

Okay, thank you both.

Arjen