locked
really is usefull raw sql when I want to load related entities? RRS feed

  • Question

  • I am using EF 4.4 and I know that the best performance that I get is using raw sql to query the information to the database.

    I use to do in this way:

    myContext.Mytable.SQLQuery("select * from MyTable").ToList<Mytable>();

    Well, the problema are that I always has other tables that have realtion with this main table, so I need to load related entities. So the best option is to use a unique query to the database with eager loading. However, raw sql does not let eager loading, so I only have the option to use linq to sql:

    myCOntext.Mytable.Include("MyOtherTable").ToList<Mytable>()

    But Linq is less efficient than raw sql so I have to decide, if I want to use raw sql, I need to do additional queries to the database to get the related entities. If I want only one query, that is the best from the point of view of the database server, then I have to use linq to sql that is less efficient.

    So at least, raw sql, in practice, it seems that is not very useful. am I right?

    Thanks.

    Tuesday, June 18, 2013 3:42 PM

Answers

  • Hi,

    What is the actual performance difference you saw ? Make sure also to include a where clause if needed. If some columns are not needed, don't retrieve them (especially pay attention to blob columns).

    As always this is a tradeoff (for example I used raw SQL for CTE queries : http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx )

    "I need to load related entities" : this is not needed just because you have a relation. This is usefull if you know that your code will really need to use those related data.

    I would say : by default use EF with lazy loading. Use eager loading if you are sure that your code will necessarily use those related data. Use SQL if you can't do otherwise (and I would expect to be rather because you are doing something special such as CTEs rather than "just" for a performance issue).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Proposed as answer by Chester Hong Wednesday, June 26, 2013 9:57 PM
    • Marked as answer by ComptonAlvaro Tuesday, September 16, 2014 10:47 AM
    Tuesday, June 18, 2013 4:23 PM

All replies

  • Hi,

    What is the actual performance difference you saw ? Make sure also to include a where clause if needed. If some columns are not needed, don't retrieve them (especially pay attention to blob columns).

    As always this is a tradeoff (for example I used raw SQL for CTE queries : http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx )

    "I need to load related entities" : this is not needed just because you have a relation. This is usefull if you know that your code will really need to use those related data.

    I would say : by default use EF with lazy loading. Use eager loading if you are sure that your code will necessarily use those related data. Use SQL if you can't do otherwise (and I would expect to be rather because you are doing something special such as CTEs rather than "just" for a performance issue).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Proposed as answer by Chester Hong Wednesday, June 26, 2013 9:57 PM
    • Marked as answer by ComptonAlvaro Tuesday, September 16, 2014 10:47 AM
    Tuesday, June 18, 2013 4:23 PM
  • In my case is common to know the related entities that I need, so I prefer to use eager loading. Other advantage of the eager loaded is that it is used only one query to the database. With lazy loading, I do many queries, one to get the main entities and many, one for each entity which I access to the related entities.

    I think that is better one connection to the database tan many, and more in my case, that I know the related entities.

    I have only one blob field in one table, but in my case I have another table to stores this information.

    I have done test and I try to get many entities many times (the same query in a loop), one query with linq and the other using raw sql, I can see that the difference of time can be very hugh. So this is the reason that if I can, I prefer to use raw sql, the problem is if I need to get related entities, in this case I linq that use only one query (with eager loading) or I use raw sql but then I need many queries (in the same way tan lazy loading, we could say manual lazy loading).

    Thanks.

    Thursday, June 27, 2013 7:13 AM