Some reasons for cache not being used :
1. The logical query has an additional column as compared to the seeded query.
2. The logical query has where clause on a column that is not in the Select statement of the seeded query.
3. Limited additional aggregation.
For example, if a query with the column qtysold is cached, a request for RANK(qtysold) results in a cache miss. Additionally, a query requesting qtysold at the country level can get a cache hit from a query requesting qtysold at the country, region level.
4. ORDER BY clause made up of columns in the select list. Queries that order by columns not contained in the select list result in cache misses.
5. The Operators in the query have to be the same.
1. The logical query has an additional column as compared to the seeded query.
2. The logical query has where clause on a column that is not in the Select statement of the seeded query.
3. Limited additional aggregation.
For example, if a query with the column qtysold is cached, a request for RANK(qtysold) results in a cache miss. Additionally, a query requesting qtysold at the country level can get a cache hit from a query requesting qtysold at the country, region level.
4. ORDER BY clause made up of columns in the select list. Queries that order by columns not contained in the select list result in cache misses.
5. The Operators in the query have to be the same.
6. Caching cannot be used for constrained Prompts.
7. Cache entries become specific to users depending on the way data-level security is setup in the RPD. In this case the Logical SQL needs to be compared to determine if cache can be reused.
8. If session variables are used as follows in the connection pool, caching will not work since this results in the cache being specific to a user.
8. If session variables are used as follows in the connection pool, caching will not work since this results in the cache being specific to a user.
- Shared Login checkbox is checked in the repository connection pool object.
- Shared Login username value is set to:
VALUEOF(NQ_SESSION.USER)
- Shared Login password value is set to:
:PASSWORD
In this case, no reports will hit cache.
- Shared Login username value is set to:
VALUEOF(NQ_SESSION.USER)
- Shared Login password value is set to:
:PASSWORD
In this case, no reports will hit cache.
9. Limited additional aggregation.
For example, if a query with the column qtysold is cached, a request for RANK(qtysold) results in a cache miss. Additionally, a query requesting qtysold at the country level can get a cache hit from a query requesting qtysold at the country, region level.
10. ORDER BY clause made up of columns in the select list. Queries that order by columns not contained in the select list result in cache misses.
11. Check if the cache parameters in Nqsconfig.ini are configured incorrectly.
Make sure MAX_CACHE_ENTRY_SIZE parameter is not bigger than the DATA_STORAGE_PATHS specified capacity,.
12. DISTINCT attribute the same. If a cached query eliminates duplicate records with DISTINCT processing (for example, SELECT DISTINCT...), requests for the cached columns have to also include the DISTINCT processing; a request for the same column without the DISTINCT processing will be a cache miss.
10. ORDER BY clause made up of columns in the select list. Queries that order by columns not contained in the select list result in cache misses.
11. Check if the cache parameters in Nqsconfig.ini are configured incorrectly.
Make sure MAX_CACHE_ENTRY_SIZE parameter is not bigger than the DATA_STORAGE_PATHS specified capacity,.
12. DISTINCT attribute the same. If a cached query eliminates duplicate records with DISTINCT processing (for example, SELECT DISTINCT...), requests for the cached columns have to also include the DISTINCT processing; a request for the same column without the DISTINCT processing will be a cache miss.
No comments:
Post a Comment