LINQ to SQL vs. Stored Procedures (Performance)

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi. I have a performance-related question about LINQ to SQL. If I have a
Stored Procedure and a LINQ to SQL command, both which perform the same
function (ie - SELECT, INSERT, etc), is the Stored Procedure innately more
efficient than the LINQ to SQL command?

From what I understand Stored Procedures cache their query execution plans,
and this gives them a performance improvement when compared to direct T-SQL
executed against the database. Does this advantage also make Stored
Procedures more processor-efficient than LINQ to SQL commands?

Thanks
 
Mike said:
Hi. I have a performance-related question about LINQ to SQL. If I have a
Stored Procedure and a LINQ to SQL command, both which perform the same
function (ie - SELECT, INSERT, etc), is the Stored Procedure innately more
efficient than the LINQ to SQL command?
Click to expand...

The stored proc is able to do a lot of work on the database so that
less data needs to come back to the client, that can be more
performant.

If it's just a case of declaring a stored proc to execute a single SQL
statement, then it's unlikely to make any difference at all.
From what I understand Stored Procedures cache their query execution plans,
and this gives them a performance improvement when compared to direct T-SQL
executed against the database. Does this advantage also make Stored
Procedures more processor-efficient than LINQ to SQL commands?
Click to expand...

No - because prepared statements will also have their query execution
plans cached.
 
Mike said:
Interesting... What exactly is a 'Prepared Statement', if I may ask.
Click to expand...

A SQL statement which is designed to be reused. I suspect all ADO.NET
drivers use them by default these days - in Java you can specifically
prepare them, but I don't think you need to in .NET.

They're not terribly effective when you embed values directly into the
SQL, as then it needs to be parsed each time - but if you use
placeholders (which you should do anyway in many cases, to guard
against SQL injection attacks) the value isn't part of the cached
statement, so the value can change and it still reuse the original
plan. (That can very occasionally be the wrong thing to do, admittedly,
but that's a different matter.)
 
Thanks for all the info!

Jon Skeet said:
A SQL statement which is designed to be reused. I suspect all ADO.NET
drivers use them by default these days - in Java you can specifically
prepare them, but I don't think you need to in .NET.

They're not terribly effective when you embed values directly into the
SQL, as then it needs to be parsed each time - but if you use
placeholders (which you should do anyway in many cases, to guard
against SQL injection attacks) the value isn't part of the cached
statement, so the value can change and it still reuse the original
plan. (That can very occasionally be the wrong thing to do, admittedly,
but that's a different matter.)
Click to expand...
 
Both will cache their plans after an initial hit. If it is an oft used LINQ
query, you will find that SQL Server creates stats and the query will run
fast. If rarely used, you MIGHT find SQL faster, but stats will go out of
date on it to, so the precompilation is the main benefit. That and the
ability to set security on an object (sproc, not LINQ).

Note, however, that you CAN write a sproc that requires frequent
recompilation, blowing the theory that sprocs are always better out of the
water.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

*************************************************
| Think outside the box!
|
*************************************************
 
Back
Top