New Posts New Posts RSS Feed: Query skipping cache/FilterQuery
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Query skipping cache/FilterQuery
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Randar View Drop Down
Newbie
Newbie
Avatar

Joined: 13-May-2011
Location: Toronto
Posts: 23 		Post Options Post Options   Quote Randar Quote  Post ReplyReply Direct Link To This Post Topic: Query skipping cache/FilterQuery
    Posted: 22-Jun-2011 at 8:46am
I have an EntityManager in a Singleton. For some reason, one of my queries won’t use the client side cache.  Whenever I use SQL Profiler, I keep seeing the following query:

SELECT TOP (1)
[Extent1].[ID] AS [ID],
[Extent1].[CompanyID] AS [CompanyID],
[Extent1].[Description] AS [Description]
FROM  [dbo].[tblGLAccountTypes] AS [Extent1]
INNER JOIN [dbo].[tblPvxCompanies] AS [Extent2] ON [Extent1].[CompanyID] = [Extent2].[ID]
WHERE (1 = [Extent2].[Organization]) AND (9 = [Extent1].[ID])

Stepping through the code, I can see exactly when it is being called.  It’s being called on the last line of the following code:

Public Overrides Sub LoadRecord(ByVal em As EntityManager, ByVal ID As Long)
        Dim emg As EntityManager_GL = DirectCast(em, EntityManager_GL)
        Dim item = (From queryItem In emg.efGLAccountTypes
              Where queryItem.ID = ID
              Select queryItem).FirstOrNullEntity

My original theory was that it was because of the DirectCast, but I’ve got other code that does the same casting and seems to work.  My other theory is I have setup a FilterQuery setup in my BOS code like this:

        Protected Overrides Function FilterQuery() As Boolean
            QueryFilters.AddFilter(Of DomainModel.emGL.efGLAccountType)(Function(q) q.Where(Function(c) c.efPvxCompany.efOrganization.ID = organzationId))
            Return MyBase.FilterQuery()
        End Function                                               

Does adding a filterquery negate the client side cache?  Is there a way to change that?  I sort of assume the logic would be, if you retrieved the value in the past, the value should still be cached.
Back to Top
Randar View Drop Down
Newbie
Newbie
Avatar

Joined: 13-May-2011
Location: Toronto
Posts: 23 		Post Options Post Options   Quote Randar Quote  Post ReplyReply Direct Link To This Post Posted: 22-Jun-2011 at 8:52am
I actually just commented out the line in the FilterQuery and confirmed.  If you have a FilterQuery, the client side cache is not used.

Is there a way to change that behaviour?  I would like it to just use the FilterQuery the first time and then let the client cache the results.  We are planning on writing a way to invalidate the client side cache, possibly through a polling mechanism.
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 23-Jun-2011 at 1:00pm
Hi Randar,
 
When you execute a query, it will hit the datasource unless you have explicitly set QueryStrategy.CacheOnly or this exact same query is already in the QueryCache. (i.e. was executed before)
The above will happen even if the entity you are looking for is already in the EntityCache.
 
If you are actually querying by key, (i.e. myKey.ToKeyQuery().With(EntityManager_GL ).FirstOrNullEntity(); ) then it will first look for for the entity in the EntityCache and only look for it in the datasource if it is not in cache.
 
I couldn't verify the impact of having or not a filter in the query interceptor. Could you provide a sample reproducing the issue?
 
You can find more about about QueryCache, EntityCache, and QueryStrategy in the DevForce Resource Center.
 
Regards,
   Silvio.
Back to Top
Randar View Drop Down
Newbie
Newbie
Avatar

Joined: 13-May-2011
Location: Toronto
Posts: 23 		Post Options Post Options   Quote Randar Quote  Post ReplyReply Direct Link To This Post Posted: 23-Jun-2011 at 3:12pm
I have left the QueryStrategy the default, which is DefaultQueryStrategy.  So it should go to the cache first and datasource second.  I am running the exact same query over and over so it should go to the cache.  I have also verified that just commenting out the one QueryFilter in the first post will then use the client cache first.  The best sample is the code in the first post.

So is this a bug then?  I really hope there is a way to change this behaviour.
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 23-Jun-2011 at 4:11pm
Randar,
The DefaultQueryStrategy uses a Optimized FetchStrategy. Per the DRC:
 
Optimized
Check the query cache to see if the current query has previously been submitted (and, if necessary, inverted) successfully. If so, satisfy the query from the entity cache, and skip the trip to the datasource.
If the query cache contains no query matching or encompassing the current query, then determine if all entities needed to satisfy the query correctly from the cache can be retrieved into the cache. If so, apply the DataSourceThenCache FetchStrategy. Otherwise, apply the DataSourceOnly FetchStrategy. See the discussion on query inversion for more detail.
 
So, in fact a query would only skip the trip to the datasource if the query is in the QueryCache.
What operations are performed between these queries? The reason I ask is that removing an entity from an EntityManager will clear the entire QueryCache and re-executing a query will hit the datasource again. Also, you can always check if the query is actually in the QueryCache:
var isQueryInQueryCache = myQuery.InQueryCache(EntityManager_GL.QueryCache);
 
I've tried the snippet you provided earlier, but I couldn't verify the behavior. In my setting, a query will never hit the datasource if it's in the QueryCache; regardless of having or not a filter in the interceptor.
Since there might be another detail that is different between my setting and yours, a sample solution (preferably against NorthwindIB) would help a great deal in trying to determine the issue.
 
Regards,
Silvio.


Edited by sbelini - 23-Jun-2011 at 4:26pm
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 23-Jun-2011 at 6:25pm
Randar,
 
Looking deeper in your snippet we realized that because you are using 'Select queryItem' after the where clause the result is considered a projection and the query will not be placed in the QueryCache:
 
Public Overrides Sub LoadRecord(ByVal em As EntityManager, ByVal ID As Long)
Dim emg As EntityManager_GL = DirectCast(em, EntityManager_GL)
Dim item = (From queryItem In emg.efGLAccountTypes
Where queryItem.ID = ID
Select queryItem).FirstOrNullEntity
 
 
Eliminating the 'Select' from the query will resolve the issue and not affect the result:
 
Public Overrides Sub LoadRecord(ByVal em As EntityManager, ByVal ID As Long)
Dim emg As EntityManager_GL = DirectCast(em, EntityManager_GL)
Dim item = (From queryItem In emg.efGLAccountTypes
Where queryItem.ID = ID).FirstOrNullEntity
 
That will resolve the issue about the query hitting the datasource everytime.
 
Regards,
   Silvio.
 
 
Back to Top
Randar View Drop Down
Newbie
Newbie
Avatar

Joined: 13-May-2011
Location: Toronto
Posts: 23 		Post Options Post Options   Quote Randar Quote  Post ReplyReply Direct Link To This Post Posted: 24-Jun-2011 at 12:28pm
So that works.  It is now using the cache consistently.  Can you give me a bit more explanation of why that made a difference?  Why would the select make any difference?
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down