Lookup queries the database everytime

6 posts, 0 answers
  1. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 31 Jan 2012 Link to this post

    Hi all,
      I have a situation where I'm loading data into the database for each record I have to lookup ID's that are related to that record.
    What I have noticed is that the table holding the ID's gets queried for every record loaded, there are much fewer IDs than records to be loaded so I expected the cache mechanics to kick in so there would be less queries to the lookup table.
    Am I experiencing the same as described last in this thread http://www.telerik.com/community/forums/orm/general-discussions/l2-cache-not-working.aspx, that since there are more than 500 records/items in the lookup table it's not cached ?.

    Since I only need the ID but not the OA object could I change the function that gets the ID so it would be more efficent ?
    it looks something like this
     public bool TryFindID(short type, string externalID, out int ID)
     {
           Lookup dim= this.Lookup.FirstOrDefault(d => string.Equals(d.Source_Id, externalID) && short.Equals(d.type, type));
         if (dim != null)
         {
             ID = dim.Id;
             return true;
         }
         else
         {
             ID = int.MinValue;
             return false;
         }
     }

    Thanks in advance.
  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 01 Feb 2012 Link to this post

    Hi Björn,
    We have to find exactly this query result, with the same parameters for type and externalID in the cache, how often is that possible?

    If you do not need the object you can also say:

    this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>x.ID).FirstOrDefault()

    This will only give you the int values back.


    Greetings,
    Jan Blessenohl
    the Telerik team

    SP1 for Q3’11 of Telerik OpenAccess ORM is available for download

  3. DevCraft banner
  4. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 01 Feb 2012 Link to this post

    Thanks for the reply.

    The actual ratio between records and IDs can vary, but is expected to be more than 8.000 to 1

    Sadly the "this.Lookup.Where(....)" happens to return 0 when not found but 0 is a valid ID unless we take precaution to prevent the use 0 as ID. We are currently testing to load into memory "lite" version of the Lookup only with those 3 fields needed for the lookup.
    Hoping that the cost of having the Lookup values in memory is outweighted by the umproved speed.
  5. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 02 Feb 2012 Link to this post

    Hi Björn,

    You can also work with a

    this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>x.ID).Cast<int?>().FirstOrDefault()

    if you want to see a null.

    All the best,
    Jan Blessenohl
    the Telerik team

    SP1 for Q3’11 of Telerik OpenAccess ORM is available for download

  6. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 02 Feb 2012 Link to this post

    Thanks for that suggestion.
    But when I add .Cast(int?) to the query I get a runtime error

    The compiled query cache contains an entry for this query, but it's result converter is not producing the required type : 'System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
    Parameter name: targetType
    Actual value was System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.ArgumentOutOfRangeException

    I'm using ORM 2011.3.112
    The fix is instead of .Cast(int?) I have .Select(x=> (int?) x.ID).FirstOrDefault();

    I did some tests, in a loop I executed context.TryFindID about 10.000 times with the same parameters.
    It resulted in 10.000 requests sent to the database (SQLserver Activity monitor), I did 3 variations of the TryFindID function: the original and 2 that cast the id to int?.
    The few simple tests I did, did not show any noticable difference in performance.

    Thanks for your help.

  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 03 Feb 2012 Link to this post

    Hello Björn,

    the working way to do this is to use

    this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>(int?)x.ID).FirstOrDefault()


    I can reproduce the error that you reported, but I fear we will be able to fix this for the upcoming Q1 2011 release, but only afterwards.

    Greetings,
    Thomas
    the Telerik team

    SP1 for Q3’11 of Telerik OpenAccess ORM is available for download

Back to Top
DevCraft banner