Overview
The QBO3 DbStatement supports query output caching, particularly useful for dashboard or other "expensive" queries. Caching only applies to statements with a Return Type of 'DataSet'; caching is ignored for a DataReader or XmlReader. Strictly speaking, 'caching' in this case refers to persisting a copy of a DataSet in cache on a web server, so that subsequent requests don't re-query the database for the information.
When a DataSet is saved to cached, it is stored with a dynamically created key loosely based on {ClassName}/{Operation}_{CacheKey}?{Parameters}. CacheKey is discussed below. Parameters are rendered in whatever order they are passed on the query string, so the following URLs will result in different cache entries:
- Valuation.ashx/Dashboard?Dimension=Status,CreatedDate&SqlFilters=Active
- Valuation.ashx/Dashboard?SqlFilters=Active&Dimension=Status,CreatedDate
Thus, it is the developer's responsibility to ensure that different navigation links to the same data call the server in the same way, to maximize caching efficiency.
Configuration
DbStatement properties that apply to caching are:
- CacheDuration: (required) number of seconds to cache, and
- CacheKey: (option) specify extranet group caching requirements (e.g. {OrganizationID}, {SystemRoleID}, or combinations like {OrganizationID}_{SystemRoleID}
QBO3's extranet security requirements mandate some unusual requirements for caching data output. Because the same basic query may return drastically different results based on a UserID, it is often inappropriate to return the same cached DataSet for all users. On the other hand, caching a DataSet for every user does not exactly lighten the load on the servers overall. Breaking down most practical requirements, there is typically a need to cache a DataSet for a set of users, where the set is defined by an Organization, SystemRole, or some other such group.
This is where the CacheKey settings is needed. If no CacheKey is defined, the DataSet will be cached for all users in the same manner. However, the CacheKey may contain a string with a group identity column (e.g OrganizationID, SystemRoleID, BrokerID, etc.). If such a pattern exists in the cache key, it will be replaced with the first such group the logged in user has access to. More precisely:
- AbstractObject.cs InvokeStatement method calls statement.GetCacheKey(parameters, user)
- GetCacheKey extracts any {Group} expressions from the CacheKey, and for each one, substitutes with user.GetGroupID(group)
- GetGroupID will return 0 if a user has universal access; otherwise it will return the first PersonAccess row where GroupObject = {group}
Thus, in a multi-tenant system like Valuations, you might see the following in cache for a given DataSet:
- For universal access users: Valuation/Dashboard_Organization_0?Dimension=ClientID,CreatedDate
- For user belonging to Acme Valuations (OrganizationID = 51): Valuation/Dashboard_Organization_51?Dimension=ClientID,CreatedDate
- For user belonging to Zeta Valuations (OrganizationID = 981): Valuation/Dashboard_Organization_982?Dimension=ClientID,CreatedDate