This is a migrated thread and some comments may be shown as answers.

Lookup queries the database everytime

5 Answers 70 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
XXXX
Top achievements
Rank 1
XXXX asked on 31 Jan 2012, 02:52 PM
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.

5 Answers, 1 is accepted

Sort by
0
Jan Blessenohl
Telerik team
answered on 01 Feb 2012, 05:21 PM
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

0
XXXX
Top achievements
Rank 1
answered on 01 Feb 2012, 05:40 PM
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.
0
Jan Blessenohl
Telerik team
answered on 02 Feb 2012, 09:36 AM
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

0
XXXX
Top achievements
Rank 1
answered on 02 Feb 2012, 11:45 AM
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.

0
Thomas
Telerik team
answered on 03 Feb 2012, 01:41 PM
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

Tags
General Discussions
Asked by
XXXX
Top achievements
Rank 1
Answers by
Jan Blessenohl
Telerik team
XXXX
Top achievements
Rank 1
Thomas
Telerik team
Share this question
or