none
Stored Procedure of Linq?

    Question

  • Please give me a direct and real answer which one is better in performance stored procedure or Linq?

    Please answer only with facts and not with guess. As i have been looking for this answer and all the forums are filled with "I think,According to me and I guess". So please reply only with the facts and valid links.


    Abhinav Moudgil

    Monday, December 16, 2013 11:50 AM

Answers

  • When you use linq a SQL statement is generated with very generic syntax.  When it is executed by Sql Server a plan is created and the statement executed.

    When you use a linq to access a stored procedure, again a simple SQL statement is generated, but it is a call to the stored procedure.  When the stored procedure is created the access plan is created at that point.

    So after that it is easy to see that the main difference is that creation of the access plan.  Depending on the complexity of the SQL this amount of time will vary.  That is the biggest "time" difference between the two.

    Of course the SQL generated by linq vs the SQL in the stored procedure can also affect the time.  Bad SQL in the stored procedure can cancel any benefit.

    A "problem" with using a stored procedure is that the items returned are not easily updatable from linq, so if the purpose of the linq is to access rows of data for update purposes this might not be the best solution.


    Lloyd Sheen

    Monday, December 16, 2013 4:15 PM
  • Hello,

    If the amount of data and both of the query statement will return the same collection, then the store procedure will perform better. Because when we use linq, it will first translate the lambda expression to be corresponding sql statement.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 17, 2013 9:04 AM
    Moderator

All replies

  • When you use linq a SQL statement is generated with very generic syntax.  When it is executed by Sql Server a plan is created and the statement executed.

    When you use a linq to access a stored procedure, again a simple SQL statement is generated, but it is a call to the stored procedure.  When the stored procedure is created the access plan is created at that point.

    So after that it is easy to see that the main difference is that creation of the access plan.  Depending on the complexity of the SQL this amount of time will vary.  That is the biggest "time" difference between the two.

    Of course the SQL generated by linq vs the SQL in the stored procedure can also affect the time.  Bad SQL in the stored procedure can cancel any benefit.

    A "problem" with using a stored procedure is that the items returned are not easily updatable from linq, so if the purpose of the linq is to access rows of data for update purposes this might not be the best solution.


    Lloyd Sheen

    Monday, December 16, 2013 4:15 PM
  • Hello,

    If the amount of data and both of the query statement will return the same collection, then the store procedure will perform better. Because when we use linq, it will first translate the lambda expression to be corresponding sql statement.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 17, 2013 9:04 AM
    Moderator