locked
Performance Comparison Between EF and linq to sql RRS feed

  • Question

  • i have a doubt , when i add an EF , the context brings whole data from the table . Isn't it Reduces performance , as if there could be 100000 data in a table in an Enterprise Application (whole those data are coming to the context ), how is it delivering a good performance i m wondering about !!!

    can any body explain ???

    Thursday, January 12, 2012 12:49 PM

Answers

  • When you enumerate an entity set, EF constructs the corresponding SQL query and will run that to retrieve the results. So if you look with the debugger directly at context.tbl_Student it will constructs a query without a WHERE clause and will run that. But this is not your code that causes that, this is trying to enumerate the context.tlb_Student entity set using the debugger.

    Instead your code will try rather to enumerate the entity set returned by query so it will create a statement for the whole query that is with a WHERE clause and will retrieve only the needed entities.

    You could use SQL Profiler to see the additional SQL Statements being sent to the server when you use the debugger.

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Ind_Alchemist Monday, January 16, 2012 5:06 AM
    Friday, January 13, 2012 12:02 PM
  • var query = from Stud in context.tbl_Student where Stud.StudentId > 603 select Stud;

    doesn't mean that your client side code will first get the whole context.tlb_Student collection and will then select among those lines those matching your criteria.

    Instead the WHOLE query is translated to a single SQL Statement that will run on your server and you'll get only the matching rows. So it will NOT retrieve all rows but just those you asked for.

    Now while debugging if you want to browse the entities hold by context.tbl_Student you triggers a query that will retrieve all rows as you asked for the "context.tbl_Student" rows rather than the "from Stud in context.tbl_Student where Stud.StudentId > 603 select Stud" rows.

    Try http://msdn.microsoft.com/en-us/library/bb738633.aspx and in particular "Deferred Query Execution"...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Ind_Alchemist Monday, January 16, 2012 5:06 AM
    Friday, January 13, 2012 3:14 PM

All replies

  • On 1/12/2012 7:49 AM, Beginner_Developer wrote:
    > i have a doubt , when i add an EF , the context brings whole data from
    > the table . Isn't it Reduces performance , as if there could be 100000
    > data in a table in an Enterprise Application (whole those data are
    > coming to the context ), how is it delivering a good performance i m
    > wondering about !!!
    >
    > can any body explain ???
    >
     
    Why would you be querying for 100,000 records if you don't need all
    100,000 records?
     
     
     
    Thursday, January 12, 2012 1:08 PM
  • Hi,

    No it brings the data matching your where clause when you enumerate them.

    A common issue is to look at entity sets using the debugger and see all entity being brought down as you implicitely asked for them. It gives the impression that it will load all data in your code.

    If you are not in this case, be more explicit about why you thought it brings down all data (of course if you don't have a where clause it will).

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Thursday, January 12, 2012 2:38 PM
  • Ya Patrice

    this is the case :

    A common issue is to look at entity sets using the debugger and see all entity being brought down

    as you implicitly asked for them. It gives the impression that it will load all data in your code

    i wrote a query  :

    var query = from Stud in context.tbl_Student

                       where Stud.StudentId > 603

                       select Stud;

    when i check the value of context using debugger, it shows me the all table contents.

    thats what i am wandering , if it is fetching the whole table there , then how could it give a better performance. 

    Please Clarify my doubts. 


    Friday, January 13, 2012 9:20 AM
  • When you enumerate an entity set, EF constructs the corresponding SQL query and will run that to retrieve the results. So if you look with the debugger directly at context.tbl_Student it will constructs a query without a WHERE clause and will run that. But this is not your code that causes that, this is trying to enumerate the context.tlb_Student entity set using the debugger.

    Instead your code will try rather to enumerate the entity set returned by query so it will create a statement for the whole query that is with a WHERE clause and will retrieve only the needed entities.

    You could use SQL Profiler to see the additional SQL Statements being sent to the server when you use the debugger.

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Ind_Alchemist Monday, January 16, 2012 5:06 AM
    Friday, January 13, 2012 12:02 PM
  • thanks Patrice , but can  u please elaborate a little more .
    Friday, January 13, 2012 1:21 PM
  • var query = from Stud in context.tbl_Student where Stud.StudentId > 603 select Stud;

    doesn't mean that your client side code will first get the whole context.tlb_Student collection and will then select among those lines those matching your criteria.

    Instead the WHOLE query is translated to a single SQL Statement that will run on your server and you'll get only the matching rows. So it will NOT retrieve all rows but just those you asked for.

    Now while debugging if you want to browse the entities hold by context.tbl_Student you triggers a query that will retrieve all rows as you asked for the "context.tbl_Student" rows rather than the "from Stud in context.tbl_Student where Stud.StudentId > 603 select Stud" rows.

    Try http://msdn.microsoft.com/en-us/library/bb738633.aspx and in particular "Deferred Query Execution"...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Marked as answer by Ind_Alchemist Monday, January 16, 2012 5:06 AM
    Friday, January 13, 2012 3:14 PM
  • On 1/13/2012 8:21 AM, Beginner_Developer wrote:
    > thanks Patrice , but can u please elaborate a little more .
     
    Do you know the basics of ADO.NET, SQLCommand Objects and T-SQL?
     
    Friday, January 13, 2012 3:20 PM
  • Thanks a lot  Patrice to be so polite and helpful. :):):)

    It really counts for a fresher like me.

    You got the point wat i am interested in , in just one blow.

    This shows the empathy and caliber of yours.

    Once again thanks a ton , patrice :)

     

     


    Monday, January 16, 2012 5:02 AM