LINQ to SQL vs. Stored Procedures (Performance)

[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

 

[Jon Skeet [C# MVP]]

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?

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?

No - because prepared statements will also have their query execution
plans cached.

 

[Mike]

Interesting... What exactly is a 'Prepared Statement', if I may ask.

 

[Jon Skeet [C# MVP]]

Interesting... What exactly is a 'Prepared Statement', if I may ask.

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

 

[Mike]

Thanks for all the info!

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

 

[Cowboy \(Gregory A. Beamer\)]

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

 

[Peter Ritchie [C# MVP]]

LINQ to SQL and sprocs are not mutually exclusive. You can use sproces with
LINQ to SQL. See
http://weblogs.asp.net/scottgu/arch...-retrieving-data-using-stored-procedures.aspx for an example.