none
where stored procs are orders of magnitude faster than LINQ RRS feed

  • Question

  •  


    According to answers I've seen on the web, stored procedures (and db functions) tend to perform better vs. LINQ to SQL or 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 an equivalent, reasonably complex query operation 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-to-SQL counterpart? Even in the scenario where the stored proc is nothing more than a parameterized query (no control flow or cursors), I don't see why it would offer any significant performance benefit over the T-SQL generated from an equivalent  LINQ query. These would both seem to be similar T-SQL queries. Would the difference be that stored procs are cached, but the LINQ to SQL query is not?  Is it possible for LINQ-to-SQL queries to approximate the performance of stored procs by querying against clustered-index views instead of tables. Would it be faster to compose LINQ queries from other LINQ queries, than to compose T-SQL queries out of many stored procs?

     

    In which cases are  scalar- or table- valued functions an order-of-magnitude faster (or slower)  vs. the LINQ equivalent? Maybe aggregate functions, e.g. MIN, SUM?

     

    In all cases described, let's assume that both the T-SQL and LINQ queries are written with equal attention performance optimization.

     


    T. Webster

     


    • Edited by T. Webster Friday, January 6, 2012 8:26 AM
    Friday, January 6, 2012 8:07 AM

Answers

  • Hi Webster,

    Welcome!

    Stored Procedure is supported in LINQ to SQL and Entity Framework now. As you know, EF and LINQ to SQL always translate LINQ querys to "T-SQL" and run on Ado.NET 2.0 now, so at runtime SP and LINQ are same. I think the performance hit is "Translation", for complex query, it's hard to let the provider translate or sometimes it is impossible for complex secnaro, but LINQ querys are readable and easy to write. Stored procedure is easy to achieve business logic on server and we just need to pass the name of SP to let Command excute

    -------

     EF (and LINQ to SQL) generate queries that are correct and easy to generate and test: not queries that are easy to read and perform well.

    -------

    So we can excute "T-SQL" directly though EF or LINQ to SQL.

    I'd like to share a link with you here: http://social.technet.microsoft.com/wiki/contents/articles/3901.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 9, 2012 9:14 AM
    Moderator

All replies

  • Hi Webster,

    Welcome!

    Stored Procedure is supported in LINQ to SQL and Entity Framework now. As you know, EF and LINQ to SQL always translate LINQ querys to "T-SQL" and run on Ado.NET 2.0 now, so at runtime SP and LINQ are same. I think the performance hit is "Translation", for complex query, it's hard to let the provider translate or sometimes it is impossible for complex secnaro, but LINQ querys are readable and easy to write. Stored procedure is easy to achieve business logic on server and we just need to pass the name of SP to let Command excute

    -------

     EF (and LINQ to SQL) generate queries that are correct and easy to generate and test: not queries that are easy to read and perform well.

    -------

    So we can excute "T-SQL" directly though EF or LINQ to SQL.

    I'd like to share a link with you here: http://social.technet.microsoft.com/wiki/contents/articles/3901.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, January 9, 2012 9:14 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side.Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 13, 2012 1:42 AM
    Moderator