Query Cache Persistance.

[Former Member]

This is for version 11.5 sp3. What does dynamically changing the query cache duration using iGrid.getQueryObject().setCacheDuration(NEWVALUE); do to an existing cache? The application is to cache a query result until the end of a shift. In order to accomplish this, we are thinking of calculating the time to the end of shift when a query is used and changing the value for the cache duration accordingly. What isn't apparent is what it will do to the existing cached query results or what happens when there are multiple users for the same query results making changes to the cache duration. Is there a better way to establish the query cache duration for a specific period such as the duration of a shift rather than a relative period such as a number of hours from it's first use?

Sparks Morgan

Entegreat, Inc.

[Former Member]

Sparks,

That's kind of a interesting requirement.

What if you just run the queries with a fixed cache duration and then at the end of the shift, you schedule a transaction that runs all of the queries that are cached, and when the transaction runs, it will turn off the query caching or set the cache duration to 1 minute (in effect clearing the cache).

[jcgood25]

Just as an FYI - time based query templates do not cache, so if you are using [SD] / [ED] or the DateColumn in an SQL query, any data mode in a TagQuery, Mapped Start/End Dates in an Xacute query, etc. you will not benefit from any caching at all.

[Former Member]

Good point and since the requirement seems to be around a shift, I'm betting the queries are actually time based.

[Former Member]

The queries in question are not time based. The data in the database is sort of a snapshot of the current operations and are updated at the end of the shift resulting in data changes in the query without changing any parameters. Kinda different but that is how it works.

[Former Member]

> Sparks,

>

> That's kind of a interesting requirement.

>

> What if you just run the queries with a fixed cache

> duration and then at the end of the shift, you

> schedule a transaction that runs all of the queries

> that are cached, and when the transaction runs, it

> will turn off the query caching or set the cache

> duration to 1 minute (in effect clearing the cache).

So, running the query at the end of the shift with short or no duration would set the parameters for the cache so it would actually expire. Then, the next time the query is run with the standard parameters it would have to be refreshed as the "end or shift" cache had expired. Is that the gist of how it would work?

[jcgood25]

Is your database query slow for some reason? If you are just querying a snapshot table then you will benefit from some database side caching and not really gain any additional benefit from using the xMII query caching.

[Former Member]

It isn't part of our project. It is a "current production status" database so the queries are static though the data changes. The update process is part of a production scheduling system to which we have no access. The update is performed nominally as an "end of shift" operation but there are some exceptions. We may have to update the cache based on changes to values of certain fields in the database or on some other basis if we find the end of shift option to be unsatisfactory. The process that Ryan suggested would seem to work for that situation as well. The cache update transaction could be triggered by the update process or even scheduled to poll for some "cache reset flag" and execute the affected queries with the cache duration programmatically set to a minimum value (or 0).

[sufw]

Sparks,

another suggestion might be to run a scheduled job (batch file) on the server which deletes all the files in C:\Lighthammer\Illuminator\Cache at shift change.

While this is easy to set up and maintain, it's a bit of a 'sledgehammer' approach that will clear your entire cache - you lose control over exactly which query caches you want to flush...

[jcgood25]

Using a bat file to delete these files would be like using a 'sledgehammer' to hammer finish nails into window and door trim. If you want to clear the cache, then how about the 'right tool for the right job', just use an html loader action block in a scheduled BLS transaction:

<a href="http://localhost/Lighthammer/Illuminator?Service=QueryCaching&Mode=ClearCache">http://localhost/Lighthammer/Illuminator?Service=QueryCaching&Mode=ClearCache</a>

Regards,

Jeremy

[sufw]

hmm.. should have remembered that. Thanks Jeremy!

[jcgood25]

No worries mate