none
Is Linq with stored procedures better in performance ?

    Question

  • I am new to Linq and startign a new project. Kindly guide and share your worthy experience with me.

    For me attraction in Linq is that it reduced development time. But the proejct I am going to start is a rebuld web based project in which performance is much important.

    I read Linq to Stored procedures is faster as with stored procedures it do not have to convert expressions (Linq quries) into Expression Tree (I also come to know it is major time consuming part).

    Kindly also guide by complied quries with linq meant is use of Linq with SP ?

     

     

    Monday, July 04, 2011 4:23 AM

Answers

  • Good question.

     

    I think they should be at about equal. The Linq query has to be compiled and cached, but it should not take a long time and it happens only once.

     

    If someone is a SQL expert, then (s)he could be able to write a perfect tsql script. Or a DBA could improve it in the future, optimize it based on the experience on the actual data structure and how it is used.

     

    As I know you cannot use a stored-procedure to make a compiled query, if you do you will get an error message.

     

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 04, 2011 4:49 PM
  • I have found a pretty good forum topic where someone wrote up the advantages and back draws Linq vs stored procedure.

    http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 04, 2011 4:54 PM

All replies

  • I am new to Linq and startign a new project. Kindly guide and share your worthy experience with me.

    For me attraction in Linq is that it reduced development time. But the proejct I am going to start is a rebuld web based project in which performance is much important.

    I read Linq to Stored procedures is faster as with stored procedures it do not have to convert expressions (Linq quries) into Expression Tree (I also come to know it is major time consuming part).

    Kindly also guide by complied quries with linq meant is use of Linq with SP ?

    Monday, July 04, 2011 4:24 AM
  • Can any one reply please.

     

    Monday, July 04, 2011 7:00 AM
  • Hi Haansi,

     

    Yes, building the expression tree can slow down your application in some cases. This can be improved by the compiled queries which provides for compilation and caching of queries for reuse.

     

    You can find here an interesting article about the performance of Linq and the Compiled queries:

    http://peterkellner.net/2009/05/06/linq-to-sql-slow-performance-compilequery-critical/

     

    and here you can find more tips to boost up your application:

    http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 04, 2011 8:19 AM
  • Check this link 

     

    http://www.codeproject.com/KB/linq/performance_comparisons.aspx

    Marked as answer , If this link is Useful


    J.Arun Prakash http://royalarun.blogspot.com/
    Monday, July 04, 2011 11:09 AM
  • I am new to Linq and startign a new project. Kindly guide and share your worthy experience with me.

    For me attraction in Linq is that it reduced development time. But the proejct I am going to start is a rebuld web based project in which performance is much important.

    I read Linq to Stored procedures is faster as with stored procedures it do not have to convert expressions (Linq quries) into Expression Tree (I also come to know it is major time consuming part).

    Kindly also guide by complied quries with linq meant is use of Linq with SP ?

    Hi

    if you want know which is better to according to work fast

    linq is more faster than other databases and other datasources to work

    and ling is a combined query language

    but ling is working with sql server only

    it can not support other databases

    storedprocedures are more comfort to work than ling because here you can put sql statements one procedure you just call the procedure by using the procedure name.

    so according to work fast linq is better and easy to undestand stored procedures are best

     

    Monday, July 04, 2011 12:49 PM
  • If you wanna test by your self what is faster, user a Timer. Start it before operation, and stop it just after. Then you will see exactly which is faster, and you will see an actual time. 

    Depends, its not necessary that is the Linq always faster.


    Mitja
    Monday, July 04, 2011 12:58 PM
  • Thanks Peter,

    Can you also guide how is performance of Linq with complied quries vs Linq with SQL Server stored prcedures which are again in complied form ?

    Thanks again.

    Monday, July 04, 2011 4:15 PM
  • Good question.

     

    I think they should be at about equal. The Linq query has to be compiled and cached, but it should not take a long time and it happens only once.

     

    If someone is a SQL expert, then (s)he could be able to write a perfect tsql script. Or a DBA could improve it in the future, optimize it based on the experience on the actual data structure and how it is used.

     

    As I know you cannot use a stored-procedure to make a compiled query, if you do you will get an error message.

     

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 04, 2011 4:49 PM
  • I have found a pretty good forum topic where someone wrote up the advantages and back draws Linq vs stored procedure.

    http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.
    Monday, July 04, 2011 4:54 PM
  • Hi

    Generally the stored procedures are more performant then querying data directly. But you know I give you a trick to meusure this performance with a practice way. 

    You could build a PerformanceCounter on that object and meusure the both situations where you use query and where you use the stored procedure with the same table.

     

    Reguards


    The complexity resides in the simplicity Follow me at: http://smartssolutions.blogspot.com
    Monday, July 04, 2011 5:51 PM