3

According to answers I've seen on stackoverflow, stored procedures (and to a lesser extent db functions) tend to perform better vs. LINQ + ORM frameworks (e.g. Entity Framework).

I want to determine if the performance difference is large enough to justify an increase in development time, assuming it takes more time to write equivalent, reasonably complex query operations in T-SQL vs. LINQ.

In which scenarios is there an order-of-magnitude (or at least a very large constant factor) difference in running time, between a performance-optimized stored proc and its equivalent, performance-optimized LINQ counterpart? Or for scalar- or table- valued functions vs. the LINQ equivalent?

Hopefully, it is not too difficult to see that this question follows the guidelines per the FAQ. It's not asking a subjective question like, "which is better, T-SQL or LINQ?". It's asking for a few specific scenarios (can give abstract or concrete examples) where there is a significant difference in performance. If that still doesn't make sense to you, and you still think this question is not constructive and should be closed, please leave an explanation why if you can.

Community
  • 1
T. Webster
  • 1,246
  • 2
  • 12
  • 21

3 Answers3

9

My suggestion:
1. Do development with LINQ to get a product to market faster.
2. Optimize performance of the whole system by using cache and alike.
3. Optimize performance of those few queries where it really matters.

In the order listed.

z-boss
  • 281
1

I think you are making an incorrect assumption that it will take longer to create a stored procedure than an equivalent LINQ query. The extra speed can be a huge benefit though here is an article talking about studies by Google and Amazon where load time increases of less than .5 secs had huge impacts on revenue. Given the long life of most applications it seems foolish to worry about an extra few hours of developer time for what could work out to days of time not wasted by users and/or significant increases to revenue. Another thing to consider is MS is dropping L2S in favor of EF.

Ryathal
  • 13,486
  • 1
  • 36
  • 48
1

In which scenarios is there an order-of-magnitude (or at least a very large constant factor) difference in running time, between a performance-optimized stored proc and its equivalent, performance-optimized LINQ counterpart? Or for scalar- or table- valued functions vs. the LINQ equivalent?

1 - When your business logic requires examining different data rows or large amount of data in general before it makes a decision or before it calculates a result, stored procedures have the advantage of doing this processing on the database server itself and could take a binary decision or perform the calculation on the server without passing any business data to the client. This results in good performance indeed. Let' say you have a complex business rule that would need examining 3 tables before you perform an insert. If you want your middle tear to do that, you have to bring the data via a query or more to the middle tear, execute the C# code (for example) and then continue with the insert or raise an error. When you implement the same logic on the server, you don't have to pass any data to the client (except the result).

2 - Stored procedures are also used in triggers and that is something you can't use LINQ for.

NoChance
  • 12,532