By Scott Marlowe


2008-08-18 12:37:57 8 Comments

I took a look at the "Beginner's Guide to LINQ" post here on StackOverflow (Beginners Guide to LINQ), but had a follow-up question:

We're about to ramp up a new project where nearly all of our database op's will be fairly simple data retrievals (there's another segment of the project which already writes the data). Most of our other projects up to this point make use of stored procedures for such things. However, I'd like to leverage LINQ-to-SQL if it makes more sense.

So, the question is this: For simple data retrievals, which approach is better, LINQ-to-SQL or stored procs? Any specific pro's or con's?

Thanks.

23 comments

@Gregory Kornblum 2008-10-20 03:14:09

Also, there is the issue of possible 2.0 rollback. Trust me it has happened to me a couple of times so I am sure it has happened to others.

I also agree that abstraction is the best. Along with the fact, the original purpose of an ORM is to make RDBMS match up nicely to the OO concepts. However, if everything worked fine before LINQ by having to deviate a bit from OO concepts then screw 'em. Concepts and reality don't always fit well together. There is no room for militant zealots in IT.

@dansasu11 2012-03-06 16:41:50

Stored procedure makes testing easier and you can change the query without touching the application code. Also with linq, getting a data does not mean its the right data. And testing the correctness of the data means running the application but with stored procedure it's easy to test without touching the application.

@SQLMenace 2008-08-28 16:11:55

LINQ will bloat the procedure cache

If an application is using LINQ to SQL and the queries involve the use of strings that can be highly variable in length, the SQL Server procedure cache will become bloated with one version of the query for every possible string length. For example, consider the following very simple queries created against the Person.AddressTypes table in the AdventureWorks2008 database:

var p = 
    from n in x.AddressTypes 
    where n.Name == "Billing" 
    select n;

var p = 
    from n in x.AddressTypes 
    where n.Name == "Main Office" 
    select n;

If both of these queries are run, we will see two entries in the SQL Server procedure cache: One bound with an NVARCHAR(7), and the other with an NVARCHAR(11). Now imagine if there were hundreds or thousands of different input strings, all with different lengths. The procedure cache would become unnecessarily filled with all sorts of different plans for the exact same query.

More here: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=363290

@JohnOpincar 2009-06-24 01:27:12

What a silly argument -- just use a variable and your problem is solved.

@kay.one 2009-07-26 23:42:17

@John, it wouldn't help if you used a validate instead of a literal string since at the end you are sending a literal string to the database. it might look like a variable in your code, but it will be a string in the generated T-SQL that gets sent to the DB.

@Mark Byers 2010-04-07 11:47:18

SQLMenace: According to the page you linked to, the issue is solved in VS2010.

@Brain2000 2011-12-06 18:45:53

This issue was valid when the answer was posted, but it has since then been solved in VS2010, so it is no longer an issue.

@Manish 2012-09-25 21:36:30

All these answers leaning towards LINQ are mainly talking about EASE of DEVELOPMENT which is more or less connected to poor quality of coding or laziness in coding. I am like that only.

Some advantages or Linq, I read here as , easy to test, easy to debug etc, but these are no where connected to Final output or end user. This is always going cause the trouble the end user on performance. Whats the point loading many things in memory and then applying filters on in using LINQ?

Again TypeSafety, is caution that "we are careful to avoid wrong typecasting" which again poor quality we are trying to improve by using linq. Even in that case, if anything in database changes, e.g. size of String column, then linq needs to be re-compiled and would not be typesafe without that .. I tried.

Although, we found is good, sweet, interesting etc while working with LINQ, it has shear disadvantage of making developer lazy :) and it is proved 1000 times that it is bad (may be worst) on performance compared to Stored Procs.

Stop being lazy. I am trying hard. :)

@liang 2013-04-10 01:41:46

Loading everything in memory and filter it? Linq can send filter as part of the query to database, and returns the filtered result set.

@It's a trap 2017-03-08 12:57:35

there are quite a few people who believe that LINQ loads all the data and processes it using a foreach loop. That's wrong. It just gets compiled into an sql query and provides almost same performance for most of the CRUD operations. But yes it's not a silver bullet. There are cases where using T-SQL or stored procs may turn out to be better.

@Manish 2017-11-02 20:25:21

I agree to both of counter-arguments. However, it is not completely true that linq sends all filters to DBMS to work on it. There are a few things which works in memory one of them is distinct.

@Neo 2012-03-10 10:11:39

For simple CRUD operations with a single data access point, I would say go for LINQ if you feel comfortable with the syntax. For more complicated logic I think sprocs are more efficiant performance-wise if you are good at T-SQL and its more advanced operations. You also have the help from Tuning Advisor, SQL Server Profiler, debugging your queries from SSMS etc.

@Todd Bandrowsky 2012-02-08 16:35:56

I think you need to go with procs for anything real.

A) Writing all your logic in linq means your database is less useful because only your application can consume it.

B) I'm not convinced that object modelling is better than relational modelling anyway.

C) Testing and developing a stored procedure in SQL is a hell of a lot faster than a compile edit cycle in any Visual Studio environment. You just edit, F5 and hit select and you are off to the races.

D) It's easier to manage and deploy stored procedures than assemblies.. you just put the file on the server, and press F5...

E) Linq to sql still writes crappy code at times when you don't expect it.

Honestly, I think the ultimate thing would be for MS to augment t-sql so that it can do a join projection impliclitly the way linq does. t-sql should know if you wanted to do order.lineitems.part, for example.

@totaldonet 2011-10-20 17:32:46

Create PROCEDURE userInfoProcedure
    -- Add the parameters for the stored procedure here
    @FirstName varchar,
    @LastName varchar
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT FirstName  , LastName,Age from UserInfo where [email protected]
    and [email protected]
END
GO

http://www.totaldotnet.com/Article/ShowArticle121_StoreProcBasic.aspx

@Teoman shipahi 2015-07-31 14:13:37

What is the point of this?

@pokrate 2011-08-24 04:30:03

The outcome can be summarized as

LinqToSql for small sites, and prototypes. It really saves time for Prototyping.

Sps : Universal. I can fine tune my queries and always check ActualExecutionPlan / EstimatedExecutionPlan.

@live-love 2011-08-12 15:34:50

Both LINQ and SQL have their places. Both have their disadvantages and advantages.

Sometimes for complex data retrieval you might need stored procs. And sometimes you may want other people to use your stored proc in Sql Server Management Studio.

Linq to Entities is great for fast CRUD development.

Sure you can build an app using only one or the other. Or you can mix it up. It all comes down to your requirements. But SQL stored procs will no go away any time soon.

@Kyaw Thura 2011-06-07 07:53:56

According to gurus, I define LINQ as motorcycle and SP as car. If you want to go for a short trip and only have small passengers(in this case 2), go gracefully with LINQ. But if you want to go for a journey and have large band, i think you should choose SP.

As a conclusion, choosing between motorcycle or car is depend on your route (business), length (time), and passengers (data).

Hope it helps, I may be wrong. :D

@Alex 2012-07-24 22:28:38

friends have died riding motorcycles :P

@liang 2015-05-14 05:29:20

people died driving car as well.

@Asad Ali 2017-03-22 15:40:06

yes you are wrong.

@Craig 2009-06-30 19:04:17

IMHO, RAD = LINQ, RUP = Stored Procs. I worked for a large Fortune 500 company for many years, at many levels including management, and frankly, I would never hire RUP developers to do RAD development. They are so siloed that they very limited knowledge of what to do at other levels of the process. With a siloed environment, it makes sense to give DBAs control over the data through very specific entry points, because others frankly don't know the best ways to accomplish data management.

But large enterprises move painfully slow in the development arena, and this is extremely costly. There are times when you need to move faster to save both time and money, and LINQ provides that and more in spades.

Sometimes I think that DBAs are biased against LINQ because they feel it threatens their job security. But that's the nature of the beast, ladies and gentlemen.

@Sam 2011-02-24 21:00:28

Yeah, us DBAs sit around waiting all day for you to request a Stored Proc push to production. Not having to do that would break our hearts!

@simon 2008-12-03 05:40:53

LINQ is new and has its place. LINQ is not invented to replace stored procedure.

Here I will focus on some performance myths & CONS, just for "LINQ to SQL", of course I might be totally wrong ;-)

(1)People say LINQ statment can "cache" in SQL server, so it doesn't lose performance. Partially true. "LINQ to SQL" actually is the runtime translating LINQ syntax to TSQL statment. So from the performance perspective,a hard coded ADO.NET SQL statement has no difference than LINQ.

(2)Given an example, a customer service UI has a "account transfer" function. this function itself might update 10 DB tables and return some messages in one shot. With LINQ, you have to build a set of statements and send them as one batch to SQL server. the performance of this translated LINQ->TSQL batch can hardly match stored procedure. Reason? because you can tweak the smallest unit of the statement in Stored procedue by using the built-in SQL profiler and execution plan tool, you can not do this in LINQ.

The point is, when talking single DB table and small set of data CRUD, LINQ is as fast as SP. But for much more complicated logic, stored procedure is more performance tweakable.

(3)"LINQ to SQL" easily makes newbies to introduce performance hogs. Any senior TSQL guy can tell you when not to use CURSOR (Basically you should not use CURSOR in TSQL in most cases). With LINQ and the charming "foreach" loop with query, It's so easy for a newbie to write such code:

foreach(Customer c in query)
{
  c.Country = "Wonder Land";
}
ctx.SubmitChanges();

You can see this easy decent code is so attractive. But under the hood, .NET runtime just translate this to an update batch. If there are only 500 lines, this is 500 line TSQL batch; If there are million lines, this is a hit. Of course, experienced user won't use this way to do this job, but the point is, it's so easy to fall in this way.

@bbqchickenrobot 2009-07-08 18:27:41

it's easy to fail with pointers in C/C++/C# does it mean it should never be used?

@Jacques 2011-03-18 09:09:11

How many mid-level programmers deal with pointers? Linq exposes this capability to any level coder meaning the risk of error increases dramatically.

@liang 2015-05-14 06:34:06

You're comparing newbies in LINQ with senior TSQL guy. Not really a fair comparison. I would agree with your code, LINQ is not performant for batch update/delete in general. However, I would argue most of the performance arguments between LINQ and SP, are claims, but not based on measure

@Eric Z Beard 2008-08-18 12:59:51

I am generally a proponent of putting everything in stored procedures, for all of the reasons DBAs have been harping on for years. In the case of Linq, it is true that there will be no performance difference with simple CRUD queries.

But keep a few things in mind when making this decision: using any ORM couples you tightly to your data model. A DBA has no freedom to make changes to the data model without forcing you to change your compiled code. With stored procedures, you can hide these sorts of changes to an extent, since the parameter list and results set(s) returned from a procedure represent its contract, and the innards can be changed around, just so long as that contract is still met.

And also, if Linq is used for more complex queries, tuning the database becomes a much more difficult task. When a stored procedure is running slow, the DBA can totally focus on the code in isolation, and has lots of options, just so that contract is still satisfied when he/she is done.

I have seen many, many cases where serious problems in an application were addressed by changes to the schema and code in stored procedures without any change to deployed, compiled code.

Perhaps a hybird approach would be nice with Linq? Linq can, of course, be used to call stored procedures.

@Mark Cidade 2008-08-18 13:16:23

@Eric LINQ to ADO.NET Entities doesn't tie you to a data model. With the entity framework, you define a conceptual data model that is mapped to the storage model, which you can change at any time, even without compiling (provided the mapping is in a text file as opposed to being part of the binary).

@NotMe 2008-11-26 15:46:19

One feature you neglected is security. With Linq, you have to expose the tables directly to the application. With stored procedures you can limit access to those procs and enforce your business requirements.

@devlord 2009-01-27 21:30:41

I like your hybrid approach idea: if it's a simple CRUD that does not require joins and locking hints, especially if you're just querying one field, then you know the schema is not going to change and you won't be getting in fights with the DBA.

@Neil Barnwell 2009-03-03 17:22:09

"A DBA has no freedom to make changes to the data model without forcing you to change your compiled code." Why would you advocate this? No-one should have "freedom" to make changes, that's the point of configuration management. Changes should go through dev, test, etc before deployment regardless.

@Adam Robinson 2009-04-15 14:56:16

@Neil: Yes, but he can't make changes in the development environment without forcing the developer to change the code.

@Simon P Stevens 2009-06-19 17:40:35

I've got to say that I've seen the argument about tightly coupling to data bases a whole lot, and I'm not actually sure it's that valid. I've never actually come across a situation (beyond trivial examples) where I want to change the database that doesn't require a change in code higher up anyway. And really, how is Linq any different from stored procs or parameterized queries anyway. Your data access layer should still be nicely separated and abstracted regardless of whether you use an ORM or something simpler. That is what gives you the loose coupling, not what technology you use. Just my 2c

@GernBlandston 2009-06-27 14:00:02

@Chris: the security issue you speak of is easily solved using views and roles.

@SnapJag 2010-07-28 15:54:00

@Eric - Great comments. I'm a developer and DBA. I wear both hats for consulting purposes and advocate the same. LINQ has its place, it doesn't replace SPs. What is does enhance, is the ability for smaller projects/teams to bring product to market. But low will be the days when the company/project goes big and has to adapt for high-availability, VLDBs, tweaking, hints, complex queries, etc. I could never thing of using Linq in my high-volume, high-availability solutions. Especially when there are teams of programmers AND DBAs cooperating to provide big robust solutions.

@MikeKulls 2012-03-28 22:08:22

In the end linq is really just a fancy way of putting inline sql into your app and taking the sql out of your database. Whether you use linq or sql or something else they dream up in the future the reasons for putting sql into your db are still valid and don't magically go away.

@user74754 2012-07-24 22:50:41

I've seen 199 stored procedures written needlessly for every 1 schema change that was shielded from applications by way of the SP's signature, but similarly to what Simon P Stevens said, semantic changes in the usage of the SPs required application changes 100% of the time anyway. Not to mention the fact that the very existence of the SPs just begs some future dev or dba to go leaking some business logic into the SP. Then a little more, and a little more.

@Microsoft Developer 2019-04-26 12:47:49

Never put business logic in the database. A database is for data shaping operations, not business logic. So many reasons why we dont partake in such outdated ideas anymore. Layer your system properly and use the database for what it was designed to do.

@Paul Parry 2008-10-09 14:43:53

LINQ definitely has its place in application-specific databases and in small businesses.

But in a large enterprise, where central databases serve as a hub of common data for many applications, we need abstraction. We need to centrally manage security and show access histories. We need to be able to do impact analysis: if I make a small change to the data model to serve a new business need, what queries need to be changed and what applications need to be re-tested? Views and Stored Procedures give me that. If LINQ can do all that, and make our programmers more productive, I'll welcome it -- does anyone have experience using it in this kind of environment?

@Meta-Knight 2009-06-08 19:34:41

You bring up a good point; LINQ is not an alternative in this case.

@bbqchickenrobot 2009-07-08 18:25:57

Your various applications should all be using a common data access layer (of course, that isn't always the case). If so, then, you can make a single change to the common library and redeploy. You can also use something like WCF to handle the data acess for you. There's a nice layer of abstraction that could technically use linq to access data behind the scenes. I guess it all just depends on what your guys come up with for your requirements in regards to using SPs vs Linq.

@Dr8k 2008-09-12 04:42:23

I think the pro LINQ argument seems to be coming from people who don't have a history with database development (in general).

Especially if using a product like VS DB Pro or Team Suite, many of the arguments made here do not apply, for instance:

Harder to maintain and Test: VS provides full syntax checking, style checking, referential and constraint checking and more. It also provide full unit testing capabilities and refactoring tools.

LINQ makes true unit testing impossible as (in my mind) it fails the ACID test.

Debugging is easier in LINQ: Why? VS allows full step-in from managed code and regular debugging of SPs.

Compiled into a single DLL rather than deployment scripts: Once again, VS comes to the rescue where it can build and deploy full databases or make data-safe incremental changes.

Don't have to learn TSQL with LINQ: No you don't, but you have to learn LINQ - where's the benefit?

I really don't see this as being a benefit. Being able to change something in isolation might sound good in theory, but just because the changes fulfil a contract doesn't mean it's returning the correct results. To be able to determine what the correct results are you need context and you get that context from the calling code.

Um, loosely coupled apps are the ultimate goal of all good programmers as they really do increase flexibility. Being able to change things in isolation is fantastic, and it is your unit tests that will ensure it is still returning appropriate results.

Before you all get upset, I think LINQ has its place and has a grand future. But for complex, data-intensive applications I do not think it is ready to take the place of stored procedures. This was a view I had echoed by an MVP at TechEd this year (they will remain nameless).

EDIT: The LINQ to SQL Stored Procedure side of things is something I still need to read more on - depending on what I find I may alter my above diatribe ;)

@Adam Lassek 2008-10-09 15:04:12

Learning LINQ is no big deal -- it's just syntactic sugar. TSQL is an entirely different language. Apples and Oranges.

@Dave R. 2008-12-18 16:33:04

The benefit of learning LINQ is that it isn't tied to a single technology - the querying semantics can be used for XML, objects and so on, and this will expand over time.

@SnapJag 2010-07-28 16:02:24

Agreed! So many people (developers) are looking for a "speed-to-market" solution in small teams and projects. But if the development is advanced to the next level of corporate style with multiple teams, large databases, high-volume, high-availability, distributed systems, it will be a different story to use LINQ! I don't believe you will need to edit your opinion in too long a time. LINQ is not for projects/teams that are larger and have multiple people, multiple teams (Dev & DBA), AND have a strict deployment schedule.

@liammclennan 2008-08-28 21:54:40

Stored Procs vs Code (Previous discussion)

@kenny 2008-08-28 12:39:55

LINQ doesn't prohibit the use of stored procedures. I've used mixed mode with LINQ-SQL and LINQ-storedproc. Personally, I'm glad I don't have to write the stored procs....pwet-tu.

@Chris Gillum 2008-08-26 20:04:51

Some advantages of LINQ over sprocs:

  1. Type safety: I think we all understand this.
  2. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls sprocs.
  3. Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).
  4. Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).
  5. Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.
  6. Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. This is related to #3 and #4.

Some disadvantages of LINQ vs sprocs:

  1. Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.
  2. Less flexible: Sprocs can take full advantage of a database's featureset. LINQ tends to be more generic in it's support. This is common in any kind of language abstraction (e.g. C# vs assembler).
  3. Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

Security and manageability are something that people argue about too.

  1. Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).
  2. Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.

I used to be a big sproc guy, but I'm starting to lean towards LINQ as a better alternative in general. If there are some areas where sprocs are clearly better, then I'll probably still write a sproc but access it using LINQ. :)

@RussellH 2008-12-24 01:27:33

"LINQ works with lots of databases " But LINQTOSQL only supports SQL Server.

@bbqchickenrobot 2009-07-08 17:36:02

LINQ to Entities works with Postgres and MySql in addition to MSSQL. Not sure, but I thought I read there was something for Oracle around.

@Ed James 2010-03-02 15:46:53

devart dotConnect has an Oracle thing, but it's buggy as hell. Also, I cannot get over the performance deficit that is introduced by having to select data out of the database in order to update it (Attach() is also possible, but it's rather poopey)

@MikeKulls 2012-03-09 00:42:31

I haven't seen anyone here mention code reuse. You can't reuse you linq in a VB6 or asp or file maker pro app. If you put something in the database then it can be reused EVERYWHERE. You could make a dll with linq in it I guess but that is getting overly complicated and crappy imo. Adding a function or stored proc that can be reused is much simpler.

@sparebytes 2013-12-03 17:07:52

Speaking of Code Re-use in TSQL (1) good luck trying to save the results of a stored procedure into a temp table without resorting to dynamic sql. (2) There's not much you can do to organize all your procs/functions; the namespace gets cluttered quick. (3) You've written a powerful select statement but now you want the user to be able to pick the column that gets sorted - in TSQL you might have to use a CTE that does a row_number over each column that could be sorted; in LINQ it can be solved with a few if statements in an afterthought.

@lomaxx 2008-08-19 14:28:23

A DBA has no freedom to make changes to the data model without forcing you to change your compiled code. With stored procedures, you can hide these sorts of changes to an extent, since the parameter list and results set(s) returned from a procedure represent its contract, and the innards can be changed around, just so long as that contract is still met.

I really don't see this as being a benefit. Being able to change something in isolation might sound good in theory, but just because the changes fulfil a contract doesn't mean it's returning the correct results. To be able to determine what the correct results are you need context and you get that context from the calling code.

@lomaxx 2008-08-18 13:08:31

For basic data retrieval I would be going for Linq without hesitation.

Since moving to Linq I've found the following advantages:

  1. Debugging my DAL has never been easier.
  2. Compile time safety when your schema changes is priceless.
  3. Deployment is easier because everything is compiled into DLL's. No more managing deployment scripts.
  4. Because Linq can support querying anything that implements the IQueryable interface, you will be able to use the same syntax to query XML, Objects and any other datasource without having to learn a new syntax

@bbqchickenrobot 2009-07-08 17:36:55

for me compile time safety is key!

@SnapJag 2010-07-28 15:56:14

For deployment however, if you're on a corporate team that has a deployment cycle and a bug exists that needs to be out quickly, your deployment of DLLs through QA, etc, is probably more strict than the path to deployment of a single database script. Your advantages seem to better represent a small team/project scenario.

@liang 2015-05-14 06:53:30

Deployment of SQL scripts which doesn't need to go through QA, is not necessarily a good thing here.

@Keith 2008-08-18 12:40:52

Linq to Sql.

Sql server will cache the query plans, so there's no performance gain for sprocs.

Your linq statements, on the other hand, will be logically part of and tested with your application. Sprocs are always a bit separated and are harder to maintain and test.

If I was working on a new application from scratch right now I would just use Linq, no sprocs.

@torial 2008-10-20 03:24:22

I disagree regarding your comments on no gain for sprocs. I profiled a series of approaches to SQL Server access on a prod system, and using Prepare + stored procs had among the best results. Even across multiple calls of the same logic. Perhaps you are right on a DB w/ low usage, though.

@dkretz 2008-12-03 05:55:37

If you are, and will be, the most skilled database query developer, then use what you're most intimately familiar with. Unlike procedural code, you can't say "if it gives the right answer, ship it."

@RussellH 2009-01-12 21:12:02

I think LINQ to SQL can cause recompilations when the size of a string parameter changes.

@BlueRaja - Danny Pflughoeft 2010-05-04 13:32:52

@RussellH: This was true of .Net 3.5, they fixed that in .Net 4 (for both L2S and L2E)

@SnapJag 2010-07-28 15:49:05

@Kieth Not the case! Many more execution plans are created from Linq than SPs. Benefits exist with code to debug; but problems with recompiling for company deploy cycle; inflexibility to test different ACTION queries, WHERE, ORDER BY. Changing the order of WHERE statement can cause a different query to be processed; pre-fetching; this can't be easily done in Linq. Need to have the data layer available for tweaking. SPs are harder to maintain and test? If you're not used to it; but SPs are beneficial for corps and VLDBs, High-Availability, etc! Linq is for small projects, solo work; go for it.

@Keith 2010-07-29 07:46:49

@SnapJag - You're right that sprocs give you more fine grain control, but the fact is that 99% of the time (even in the large enterprise systems I work in) you don't need any additional optimisation. Sprocs are harder to maintain and test whether you're used to them or not - I'm very used to them (I was a DBA before I was a developer) and ensuring that the sproc for each CRUD operation for loads of different tables is up to date is a pain. The best practice (IMHO) is to code in the way that's easiest, then run performance checks and optimise only where its needed.

@Mark Cidade 2008-08-18 12:45:13

The best code is no code, and with stored procedures you have to write at least some code in the database and code in the application to call it , whereas with LINQ to SQL or LINQ to Entities, you don't have to write any additional code beyond any other LINQ query aside from instantiating a context object.

@Jon Limjap 2008-08-18 12:44:12

I'm assuming you mean Linq To Sql

For any CRUD command it's easy to profile the performance of a stored procedure vs. any technology. In this case any difference between the two will be negligible. Try profiling for a 5 (simple types) field object over 100,000 select queries to find out if there's a real difference.

On the other hand the real deal-breaker will be the question on whether you feel comfortable putting your business logic on your database or not, which is an argument against stored procedures.

@HLGEM 2008-11-11 14:10:42

Since more places than the application can affect data - data imports, other applications, direct queries, etc. it is foolish not to put the business logic in the database. But if data integrity is not a concern to you, go ahead.

@4thSpace 2009-02-02 06:46:20

Business logic shouldn't go into the database. It should be in a programming language where it is easily debugged and managed. It can then be shared across applications as objects. Some rules might be in the database but not business logic.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

8 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1119783 View
  • 933 Score
  • 8 Answer
  • Tags:   c# linq group-by

26 Answered Questions

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

21 Answered Questions

[SOLVED] LINQ query on a DataTable

5 Answered Questions

17 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

34 Answered Questions

[SOLVED] What is the Java equivalent for LINQ?

  • 2009-08-01 18:53:26
  • Ahmed
  • 253658 View
  • 783 Score
  • 34 Answer
  • Tags:   java linq

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 549622 View
  • 1478 Score
  • 7 Answer
  • Tags:   linq sql-order-by

3 Answered Questions

[SOLVED] Concurrency with Linq To Sql Stored Procedures

Sponsored Content