Performance Difference between LINQ and Stored Procedures

Question

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?

Answer 1

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.

Answer 2

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.

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.