LINQ to SQL vs. Stored Procedures (Performance) -- dotnet framework

Mike
3/19/2008 7:35:00 AM

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

[quoted text, click to view]

Mike
3/19/2008 7:51:01 AM

Thanks for all the info!

[quoted text, click to view]

Peter Ritchie [C# MVP]
3/19/2008 9:35:02 AM

LINQ to SQL and sprocs are not mutually exclusive. You can use sproces with
LINQ to SQL. See
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx for an example.

--
Browse http://connect.microsoft.com/VisualStudio/feedback/ and vote.
http://www.peterRitchie.com/blog/
Microsoft MVP, Visual Developer - Visual C#

[quoted text, click to view]

Cowboy (Gregory A. Beamer)
3/19/2008 10:39:37 AM

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!
|
*************************************************
[quoted text, click to view]

Jon Skeet [C# MVP]
3/19/2008 2:25:22 PM

[quoted text, click to view]

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.

[quoted text, click to view]

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

Jon Skeet [C# MVP]
3/19/2008 2:40:53 PM

[quoted text, click to view]

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet framework

dotnet framework : LINQ to SQL vs. Stored Procedures (Performance)