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.

13
  • 1
    Every LINQ provider is different and will produce different SQL, so you really cannot generalize. Furthermore, "reasonably complex query operations" will often be impractical, if not impossible, to translate into LINQ - and more often then not, if it is even possible, it will be considerably more time consuming than just writing the SQL in the first place. Jan 6, 2012 at 6:36
  • @qes: A good comment, but I doubt this. Cannot generalize? Is the generated T-SQL or the algorithms used to generate it from e.g. LINQ-to-SQL and Entity Framework, really so fundamentally different? By time-consuming here I assume you mean development time, not running time (i.e. the question)? Even still, not necessarily. For example, suppose your query involved concatenation, other string manipulation, iteration,with persistence of some state from one row to the next, and calls to trigonometry functions...
    – T. Webster
    Jan 6, 2012 at 6:48
  • You doubt it? How many LINQ providers have you worked with? L2S and EF are only 2 of dozens, and both from Microsoft at that. The algorithms used are quite different (even from L2S to EF, heck even from one version of EF to the next) for anything but the simplest single table select statements - and those are of no interest to your question it would seem. It will be considerably more time consuming, yes, in development time - as you fight to make a complex SQL statement simply translate from LINQ at all. Even reasonably simple joins in SQL can be tricky or even impossible Jan 6, 2012 at 6:52
  • It seems you don't really understand how short most LINQ providers fall. String manipulation? Maybe 50/50 chance it's implemented. Iteration with persistence of state? Calling trig functions? Not happening. Use L2S, EF, SubSonic, LLBLGen, Lightspeed, etc. - at least a handful - and see what you find for yourself. And that's not even getting into using different LINQ providers against DB engines other than Microsoft SQL Server, another whole can of worms there. Jan 6, 2012 at 6:53
  • 1
    Trig & financial - Used in stored procs. Not in LINQ. So if your sproc uses them, you won't be easily translating them into a LINQ query. String functions, like I said - maybe 50/50. Some are supported, some providers are better than others (try your string functions in SubSonic and see how many work). My point is that the type of SQL you are asking about - "reasonably complex query operations" - are more often than not, not going to be possible to simply write in LINQ - and even when you can accomplish it - runtime performance is irrelevant since development time will be lengthened. Jan 6, 2012 at 8:07

3 Answers 3

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.

2
  • This answer hits the nail on the head!
    – major-mann
    Dec 20, 2013 at 8:13
  • 1
    But do stored procedures for much complex queries!
    – John
    Jul 3, 2014 at 14:19
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.

1
  • amen. forget the single developer, think of the experience for the thousands of users. The dev gets paid to write the code, the poor users end up paying as that dev makes his life easier.
    – gbjbaanb
    Mar 20, 2012 at 12:16
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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