Query Caching in Data Warehouse

Database
Highlighted
Teradata Employee

Query Caching in Data Warehouse

Hi,

Does Teradata make use of "Query Caching" for the fast retrieval of tactical query results? If so, which tables in the data dictionary are used for this purpose? What are the pros and cons of making use of this concept in data warehouse?

2 REPLIES
Teradata Employee

Re: Query Caching in Data Warehouse

Teradata caches query plans to avoid the cost of parsing and optimizing each time. See Request Cache in the manuals. There are ways to optimize the use if the request cache especially by using parameterized requests.

The request cache is not in a dictionary table and cannot be viewed, it is stored locally in the memory of each parsing engine. The use of a cached plan can be seen in DBQL for the request.

Teradata does not cache query results. It does cache very frequently used data in memory locally within the Amos to speed retrieval and reduce IO. The user or administrator do not control or manage this, it is automatically managed by the system based on the frequency of access of the data.

When we have considered caching query results, we have found insufficient number of cases where the exact same result is being requested repeatedly - except in benchmarks that did not control for this. Therefore Teradata has focused on optimizing requests that are structurally the same but have different parameters defining which rows to return.
Teradata Employee

Re: Query Caching in Data Warehouse

Right, it means that query plan is generated at run time and optimizer chooses the best plan. Definitely the STATISTICS should have their part in this regard.

And yes, you are right that due to diversity of query & results, it'd not be a good techneque to store queries alongwith the associated results, as it will be an additional cost. Recently, I studied such techneque, that's why I wanted to confirm.

Thanks.