none
Debate On LINQ (L2E) vs Stored Procedure RRS feed

  • Question

  • I have requirement where i need to choose between LINQ vs SP. I have gone thru many articles and blogs, but do not find any exact help. This is may be due it depends on your requirement.

    So I have decided like use LINQ where there is operation with single table only or there is only single request or single operation.

    Use stored procedure where there is more than one table requires for operation and more than one request.

    Can anyone tell me like what will be the performance impact if I choose LINQ for below points over stored procedure

    1. Simple Insert

    2. Simple Update

    3. Simple Delete

    4. Simple GetAll

    5. Simple GetByID

    As far as I know there will be no performance impact of simple CRUD operation whether you go with LINQ or Stored Procedure.


    Thanks, Rajesh Khunt
    Tuesday, January 10, 2012 8:10 AM

Answers

  • Hi Rajesh,

    Welcome to MSDN Forum!

    Based on the issue, in my opinion, if you have already created the stored procedure, you can use the stored procedure in Entity Framework. If the stored procedure has not created, you need to think if the Linq to Entities can satisfied your requirement. Not all the T-SQL can be translated from Linq to Entities. Furthermore, if your project have a high level of efficiency, the Linq to Entities may not as good as stored procedure, but it’s more easy to write and understand.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 11, 2012 9:54 AM
    Moderator
  • On 1/11/2012 11:34 AM, Rajesh Khunt wrote:
    > YES, you are right Allen
    >
    > Even we needs to have good knowledge on writing LINQ queries otherwise
    > whatever the SQL query generated from LINQ will create performance
    > impact also.
    >
    > I have gone thru many articles and found that the way you write LINQ
    > queries, it will generate SQL as per it.
    >
    > I want to make sure that is it correct? or there is Microsoft engine
    > behind generating SQL query from LINQ query, so whatever way you write
    > LINQ query.... the engine generate SQL as per best optimized way?
    >
     
    No, there are times you may have to use a SQL Profiler and see what the
    generated T-SQL is doing.
     
    You could have a situation where you Linq queried, got 1,000 records,
    went into a foreach loop, and it's going to go back 1,000 times to the
    database for each iteration to retrieve the data again, a double hit.
     
     
    Wednesday, January 11, 2012 8:44 PM

All replies

  • On 1/10/2012 3:10 AM, Rajesh Khunt wrote:
    > I have requirement where i need to choose between LINQ vs SP. I have
    > gone thru many articles and blogs, but do not find any exact help. This
    > is may be due it depends on your requirement.
    >
    > So I have decided like use LINQ where there is operation with single
    > table only or there is only single request or single operation.
    >
    > Use stored procedure where there is more than one table requires for
    > operation and more than one request.
    >
    > Can anyone tell me like what will be the performance impact if I choose
    > LINQ for below points over stored procedure
    >
    > 1. Simple Insert
    >
    > 2. Simple Update
    >
    > 3. Simple Delete
    >
    > 4. Simple GetAll
    >
    > 5. Simple GetByID
    >
    > As far as I know there will be no performance impact of simple CRUD
    > operation whether you go with LINQ or Stored Procedure.
    >
     
     
     
    You'll see sp_executesql being used by Linq and EF.
     
     
    Tuesday, January 10, 2012 11:24 AM
  • Hi Rajesh,

    Welcome to MSDN Forum!

    Based on the issue, in my opinion, if you have already created the stored procedure, you can use the stored procedure in Entity Framework. If the stored procedure has not created, you need to think if the Linq to Entities can satisfied your requirement. Not all the T-SQL can be translated from Linq to Entities. Furthermore, if your project have a high level of efficiency, the Linq to Entities may not as good as stored procedure, but it’s more easy to write and understand.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 11, 2012 9:54 AM
    Moderator
  • YES, you are right Allen

    Even we needs to have good knowledge on writing LINQ queries otherwise whatever the SQL query generated from LINQ will create performance impact also.

    I have gone thru many articles and found that the way you write LINQ queries, it will generate SQL as per it.

    I want to make sure that is it correct? or there is Microsoft engine behind generating SQL query from LINQ query, so whatever way you write LINQ query.... the engine generate SQL as per best optimized way?


    Thanks, Rajesh Khunt
    Wednesday, January 11, 2012 4:34 PM
  • On 1/11/2012 11:34 AM, Rajesh Khunt wrote:
    > YES, you are right Allen
    >
    > Even we needs to have good knowledge on writing LINQ queries otherwise
    > whatever the SQL query generated from LINQ will create performance
    > impact also.
    >
    > I have gone thru many articles and found that the way you write LINQ
    > queries, it will generate SQL as per it.
    >
    > I want to make sure that is it correct? or there is Microsoft engine
    > behind generating SQL query from LINQ query, so whatever way you write
    > LINQ query.... the engine generate SQL as per best optimized way?
    >
     
    No, there are times you may have to use a SQL Profiler and see what the
    generated T-SQL is doing.
     
    You could have a situation where you Linq queried, got 1,000 records,
    went into a foreach loop, and it's going to go back 1,000 times to the
    database for each iteration to retrieve the data again, a double hit.
     
     
    Wednesday, January 11, 2012 8:44 PM
  • Hi Rajesh,

    I'm afraid it doesn't generate T-SQL as best optimized, it always generate T-SQL as the most convenient way, so the T-SQL generated may not as efficient as you manully write. You can watch the T-SQL generated from SQL Profiler, as @darnold924 mentioned.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, January 12, 2012 2:13 AM
    Moderator
  • YES, you are right, it doesn't generate optimized T-SQL...

    Thanks for your help.


    Thanks, Rajesh Khunt

    Wednesday, February 29, 2012 10:09 AM