DevTools for .NET

Not answered Lookup queries the database everytime

Bjössi

Posted on Jan 31, 2012 (permalink)

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.

Reply

Bjössi

Posted on Feb 1, 2012 (permalink)

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.

Reply

Bjössi

Posted on Feb 2, 2012 (permalink)

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.

Reply