18

Related

LINQ-to-SQL vs stored procedures?

I have heard a lot of talk back and forth about the advantages of stored procedures being pre compiled. But what are the actual performance difference between LINQ and Stored procedures on Selects, Inserts, Updates, Deletes? Has anyone run any tests at all to see if there is any major difference. I'm also curious if a greater number of transactions makes a difference.

My guess is that LINQ statements get cached after the first transaction and performance is probably going to be nearly identical. Thoughts?

Improve this question
1
  • I think this post is not duplicate because I am looking for hard data on the performance difference. Not the pros and cons of using both technologies.
    – Al Katawazi
    Commented Apr 15, 2009 at 15:09

12 Answers 12

Reset to default
23

LINQ should be close in performance but I disagree with the statement above that says LINQ is faster, it can't be faster, it could possibly be just as as fast though, all other things being equal.

I think the difference is that a good SQL developer, who knows how to optimize, and uses stored procedures is always going to have a slight edge in performance. If you are not strong on SQL, let Linq figure it out for you, and your performance is most likely going to be acceptable. If you are a strong SQL developer, use stored procedures to squeeze out a bit of extra performance if you app requires it.

It certainly is possible if you write terrible SQL to code up some stored procedures that execute slower than Linq would, but if you know what you are doing, stored procedures and a Datareader can't be beat.

Improve this answer
5
  • 1
    I honestly do not know how this is possible but I ran my own adhoc tests and they are showing LINQ is faster than sprocs. 2 different data contexts run each, and I am doing inserts. Will post results when I have them.
    – Al Katawazi
    Commented Apr 15, 2009 at 15:29
  • 2
    All of the "auto-magical" sql tools that I've ever seen generate workable queries that are better than the worst human written queries, but worse than the best written ones.
    – KM.
    Commented Apr 15, 2009 at 15:33
  • 2
    Are you comparing a linq query versus an SP executed by Linq? that may be different, but an SP executed by a datareader should be faster than a linq query, or a linq executed SP. It may be undetectably faster, but I can't understand how it could possibly be slower.
    – E.J. Brennan
    Commented Apr 15, 2009 at 15:35
  • Yes I am using LINQ to run both. Now maybe I am wrong for doing so but my understanding was all the pooling and caching of requests was done automaticlaly for you via linq. I wanted to eliminate that as a confounding factor.
    – Al Katawazi
    Commented Apr 15, 2009 at 15:47
  • 5
    Don't optimize prematurely: it's easier to optimize correct code than to correct optimized code.
    – Petrus Theron
    Commented May 14, 2010 at 9:45
6

Stored procedures are faster as compared to LINQ query they can take the full advantage of SQL features. when a stored procedure is being executed next time, the database used the cached execution plan to execute that stored procedure. while LINQ query is compiled each and every time. Hence, LINQ query takes more time in execution as compared to stored procedures.

Stored procedure is a best way for writing complex queries as compared to LINQ.

Improve this answer
3

LINQ queries can (and should be) precompiled as well. I don't have any benchmarks to share with you, but I think everyone should read this article for reference on how to do it. I'd be especially interested to see some comparison of precompiled LINQ queries to SPROCS.

Improve this answer
2
  • I think you are confusing two issues. Precompiling the LINQ query simply means that the SQL statement is pregenerated instead of generated on the fly. There isn't any DB server optimization done until the command is actually executed. With SPROCS the server is able to pre-optimize based on usage.
    – tvanfosson
    Commented Apr 15, 2009 at 15:02
  • Nope, just a hint. :) Note the words "as well"
    – JoshJordan
    Commented Apr 16, 2009 at 14:30
3

There is not much difference except that LINQ can degrade when you have lot of data and you need some database tuning.

Improve this answer
2

LINQ2SQL queries will not perform any differently from any other ad-hoc parameterized SQL query, other than the possibility that the generator may not optimize the query in the best fashion.

Improve this answer
1

The common perception is that ad-hoc sql queries perform better than Stored Procedures. However, this is false:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

-- SqlServer's Books Online

Given the above and the fact that LINQ generates ad-hoc queries, my conclusion is that there is no performance difference between Stored Procedures & LINQ. And I am also apt to believe that SQL Server wouldn't move backwards in terms of query performance.

Improve this answer
1
  • Why do you say it is a common perception?
    – Petrus Theron
    Commented May 14, 2010 at 9:45
1

Linq should be used at the business logic layer on top of views created in sql or oracle. Linq helps you insert another layer for business logic, maintenance of which is in the hands of coders or non sql guy. It will definitely not perform as well as sql coz its not precompiled and you can perform lots of different things in sps. But you can definitely add a programming detail and segregate the business logic from core sql tables and database objects using Linq.

Improve this answer
0

See LINQ-to-SQL vs stored procedures for help - I think that post has most of the info. you need.

Improve this answer
0

Unless you are trying to get every millisecond out of your application, whether to use a stored procedure or LINQ may need to be determined by what you expect developers to know and maintainability.

Stored procedures will be fast, but when you are actively developing an application you may find that the easy of using LINQ may be a positive, as you can change your query and your anonymous type that is created from LINQ very quickly.

Once you are done writing the application and you know what you need, and start to look at optimizing it, then you can look at other technologies and if you have good unit testing then you should be able to compare different techniques and determine which solution is best.

You may find this comparison of various ways for .NET 3.5 to interact with the database useful. http://toomanylayers.blogspot.com/2009/01/entity-framework-and-linq-to-sql.html

Improve this answer
0

I don't think I would like having my database layer in compiled code. It should be a separate layer not combined. As I develop and make use of Agile I am constantly changing the database design, and the process goes very fast. Adding columns, removing columns or creating a new tables in SQL Server is as easy as typing into Excel. Normalizing a table or de-normalizing is also pretty fast at the database level. Now with Linq I would also have to change the object representation every time I make a change to the database or live with it not truly reflecting how the data is stored. That is a lot of extra work.

I have heard that Linq entities can shelter your application from database change but that doesn't make sense. The database design and application design need to go hand in hand. If I normalize several tables or do some other redesign of the database I wouldn't want a Linq object model to no longer reflect the actual database design.

And what about advantage of tweaking a View or Stored Procedure. You can do that directly at the database level without having to re-compile code and release it to production. If I have a View which shows data from several tables and I decide to change the database design all I have to do is change that View. All my code remains the same.

Improve this answer
2
  • 3
    What is the different between redeploying an assembly with the new database updates as deploying stored procs? You can also replace tables with updatable views if you want to replace database tables without updating LINQ or other data access technologies.
    – Matthew Whited
    Commented Oct 5, 2010 at 0:13
  • I used to think exactly like this. But now I'm hooked on LINQ and wouldn't go back. All those old systems I built which just executed Stored Procs and I had access to edit the Stored Procs at any time without compiling code, well, that's kind of a dodgy release process. Yes you might as well just be tweaking a well designed app. If you have to change a Stored Proc, then something isn't right and chances are that it may end up flowing on to require a code change anyway... Just have it all intact and then deployment and version control is beautiful and noone can mess with your Stored Procs!
    – Aaron
    Commented Jan 10, 2013 at 11:17
-3

Consider a database table with a million entries, joined to another table with a million entries... do you honestly think that doing this on the webserver (be it in LINQ or ad-hoc SQL) is going to be faster or more efficient than letting SQL Server do it on the database?

For simple queries, then LINQ is obviously better as it will be pre-compiled, giving you the advantage of having type checking , etc. However, for any intensive database operations, report building, bulk data analysis that need doing, stored procedures will win hands down.

Improve this answer
1
  • 3
    LINQ to SQL and adhoc SQL are still both done on the SQL server.
    – Matthew Whited
    Commented Oct 5, 2010 at 0:11
-3

<script>alert("hello") </script> I think that doing this on the webserver (be it in LINQ or ad-hoc SQL) is going to be faster or more efficient than letting SQL Server do it on the database?

For simple queries, then LINQ is obviously better as it will be pre-compiled, giving you the advantage of having type checking , etc. However, for any intensive database operations, report building, bulk data analysis that need doing, stored procedures will win hands dow

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.