none
Improving Linq Queries RRS feed

  • Question

  • Hi there!

     

    I'm not an expert in using linq and I just used VS2010 performance wizard and found out that 61% of my app performance goes to Linq. Although I'm sure that's probably right in my case, since most of my stuff so far are CRUDs, I was wondering if my queries could be optmized to reduce process time.

     

    I'm using EF4.1 and when I need to select 1 row, I usually do it like this:

    public static Group GetGroupById(int id)
            {
                using (IntranetModelContainer db = new IntranetModelContainer())
                {
                    var g = from f in db.Groups
                            where f.Id == id
                            select f;
                    return g.SingleOrDefault();
                }
            }       
    


    Is there a better to get single rows from the db?

     

    Thanks

    Thursday, November 3, 2011 7:37 PM

Answers

  • Lucas,

    Yes, that's pretty much the main way you would get a single entity.   They only other way you could do it, is if you already had the EntityKey value handy and it might be slightly faster to do a GetObjectByKey, but probably only if you didn't have to look up the EntityKey first:

    EntityKey key = new EntityKey("IntranetModelContainer.Groups", "Id", id);
    
                    Group g = (Group) ((IObjectContextAdapter)db).ObjectContext.GetObjectByKey(key);
    

     

     

     


    Tom Overton
    • Marked as answer by Lucas Phillip Thursday, November 3, 2011 8:32 PM
    Thursday, November 3, 2011 8:05 PM

All replies

  • On 11/3/2011 3:37 PM, Lucas Phillip wrote:
    > Hi there!
    >
    > I'm not an expert in using linq and I just used VS2010 performance
    > wizard and found out that 61% of my app performance goes to Linq.
    > Although I'm sure that's probably right in my case, since most of my
    > stuff so far are CRUDs, I was wondering if my queries could be optmized
    > to reduce process time.
    >
    > I'm using EF4.1 and when I need to select 1 row, I usually do it like this:
    >
    > public  static  Group GetGroupById(int  id)
    >          {
    >              using  (IntranetModelContainer db =new  IntranetModelContainer())
    >              {
    >                  var  g =from  fin  db.Groups
    >                          where  f.Id == id
    >                          select  f;
    >                  return  g.SingleOrDefault();
    >              }
    >          }
    >
    >
    > Is there a better to get single rows from the db?
     
    Your way must it must always go back to the database even if the object
    you are looking for is already in memory.
     
    This GetObjectByKey checks to see if the object is in memory before it
    tries to get it from the database.
     
     
    Thursday, November 3, 2011 8:02 PM
  • Lucas,

    Yes, that's pretty much the main way you would get a single entity.   They only other way you could do it, is if you already had the EntityKey value handy and it might be slightly faster to do a GetObjectByKey, but probably only if you didn't have to look up the EntityKey first:

    EntityKey key = new EntityKey("IntranetModelContainer.Groups", "Id", id);
    
                    Group g = (Group) ((IObjectContextAdapter)db).ObjectContext.GetObjectByKey(key);
    

     

     

     


    Tom Overton
    • Marked as answer by Lucas Phillip Thursday, November 3, 2011 8:32 PM
    Thursday, November 3, 2011 8:05 PM
  • "GetObjectByKey tries to retrieve an object that has the specified EntityKey from the ObjectStateManager. If the object is currently not loaded into the object context, a query is executed in an attempt to return the object from the data source. For more information, see Object Queries (Entity Framework)."

     

    So GetObjectByKey will only be faster if I executed the select twice in the same context right? Since after I do my select I store the object in memory and only work with that reference, I would not see any improvement I guess.

    Is that correct?

    Thursday, November 3, 2011 8:17 PM
  • Yes, that's correct.  That's what I meant when I said only if you didn't have to look up the key first, meaning you already had gotten the object the first time from the database and you use the key subsequently to get the entity from memory.

     


    Tom Overton
    Thursday, November 3, 2011 8:30 PM
  • On 11/3/2011 4:17 PM, Lucas Phillip wrote:
    > "GetObjectByKey tries to retrieve an object that has the specified
    > EntityKey
    > the ObjectStateManager
    > If the object is currently not loaded into the object context, a query
    > is executed in an attempt to return the object from the data source. For
    > more information, see Object Queries (Entity Framework)
    >
    > So GetObjectByKeywill only be faster if I executed the select twice in
    > the same context right? Since after I do my select I store the object in
    > memory and only work with that reference, I would not see any
    > improvementI guess.
    >
    > Is that correct?
    >
     
    Do you know that you don't have to get an object by it's key before you
    delete it if you know the object's key in the database? You can make a
    stub object with only the ID and delete it.
     
     
     
    Thursday, November 3, 2011 9:01 PM
  • I did not now. Not very effective though, because of those two "exceptions"...

     

    I'll give it a further look when I have the time. Deleting rows is not a commom thing in my project, so the overhead of loading the entity to delete it is not that big of a deal. Might be handy in other scenarios.

     

    Thanks

    Friday, November 4, 2011 1:46 AM
  • On 11/3/2011 9:46 PM, Lucas Phillip wrote:
    > I did not now. Not very effective though, because of those two
    > "exceptions"...
    >
    > I'll give it a further look when I have the time. Deleting rows is not a
    > commom thing in my project, so the overhead of loading the entity to
    > delete it is not that big of a deal. Might be handy in other scenarios.
     
    I have deleted 1,000(s) in one shot, so it makes perfect sense to me to
    not do 2,000 actions. One action to go get, and another action to delete.
     
    I also flip the script and use the technique when I am updating too,
    with the object that was detached from the context log ago while in
    session and it needs to update. No action to go get it and update it,
    just update it.
     
    Friday, November 4, 2011 3:09 AM