Hi all, I need your opinions, which is better and why

ysfkay · June 5th, 2010, 11:06 PM

I want to understand which is better between using stored procedures to query my database then using Linq to Sql to connect to the stored procedures or just use LINQ itself to query the database without any stored procedures..which is a better approach and why? thanks.

melvik · June 6th, 2010, 05:04 AM

hello there:
what do u mean by BETTER?
SP always is much faster that plaint SQL bc of stored Execution plan
& I guess SP much organized! & im sure it depends on ur senario & bussiness
Im going to add some usefull notes from
Wrox Professional ADO NET 3.5 with LINQ and the Entity Framework by Roger Jennings
Chapter 1: Taking a New Approach to Data Access in ADO . NET 3.5
Page 15

Quote:

The advantage of moving from the relational model of tables, rows, and keys to the entity model of entity classes, entity instances, and associations is that entities can â and usually do â represent business objects, such as customers, partners, vendors, employees, products, and services. If your databases are highly normalized, like the AdventureWorks sample database, you â ll probably need one or more joins and WHERE clause criteria to represent a customer or employee. Adopting an entity model enables developers to design real - world objects with custom properties, associations, and behaviors suited to the organization â s business processes.

HTH

Imar · June 6th, 2010, 06:19 AM

Quote:

SP always is much faster that plaint SQL bc of stored Execution plan

This is a common misconception about sprocs, and generally not true.

Quote:

The cached execution plan used to give stored procedures a performance advantage over queries. However, for the last couple of versions of SQL Server, execution plans are cached for all T-SQL batches, regardless of whether or not they are in a stored procedure. Therefore, performance based on this feature is no longer a selling point for stored procedures. Any T-SQL batch with static syntax that is submitted frequently enough to prevent its execution plan from aging out of memory will receive identical performance benefits. The "static" part is key; any change, even something as insignificant as the addition of a comment, will prevent matching with a cached plan and thereby prevent plan re-use.

For more details: http://msdn.microsoft.com/en-us/library/ms973918.aspx

Cheers,

Imar

ysfkay · June 6th, 2010, 08:42 PM

Thanks for the information, you have compared between stored procedures and T-SQL in application layer, is this comparison thesame with comparing Stored procedures and Linq queries ? thanks...

Imar · June 7th, 2010, 03:08 AM

From a querying perspective, the answer is probably yes.

However, LINQ comes with overhead of its own as it needs to create object instances out of the database data. This means it will run slower than just retrieving data. However, when not using LINQ you need to turn the database data into something your application can work with and that takes time as well.

My advise if you want to know the nitty gritty of LINQ performance? Measure, measure, measure, and Google: http://www.google.com/#hl=en&q=linq+...f11b3472c963b9

Cheers,

Imar