The TTL of the query is the amount of time that the results of a query are cached before the results are re-requested from a database. The following sequence of events occurs:
- A query is issued via one of the ADOdb
- ADOdb checks to see if the same query has been requested within the TTL value provided.
- Note that a statement can always request a 'fresh' result set by providing a 0 (Zero) value TTL.
- If there is a stored resultset available, it uses that.
- If there is no result available, or the result is too old, the query is issued to the database, and the resulting recordset is both returned to the requester and stored for later requests.
Determining Appropriate TTL
In real-world applications, the TTL of a query will likely be either extremely short, or extremely long. Take the following examples:
The most common usage for cached queries is returning static data sets, for example a database-provided key-value pair to populate a select list. The permanancy of the data may be such that the TTL of the query requesting it might not need be no more than once per day, in seconds therefore it would be:
60 * 60 * 24 = 86400
Determining the appropriate TTL for a short life request is much more difficult.
As system design has moved towards real-time response, the TTL for short-life requests has decreased. The critical factor has become the frequency with which a recordset is requested, rather than how often the data changes.
For example, in a large system where a single specific query is requested 100 times a minute, but the returned data changes once per hour, the TTL of such a request might be as low as 60 seconds.
On a well configured system, the results of such a request would likely be cached by the system or server itself, but a heavily loaded server might benefit from the reduction of preparation and issuing of the query from 100 to 1 times per minute. If the query itself is complex, or suffers from poor index design, the benefit could be even greater.