Data Virtualization
Managers can create a cache entry to save query data and results and
optimize query performance.
Before you begin
Ensure that you meet the following requirements.
- You have the required permissions to run the SQL statement.
- You are using a query that qualifies the table name with a schema name to define and create the
cache entry. For more information, see Creating schemas.
- You have considered all restrictions for crating caches. For more information, see Restrictions for cache entries in Data Virtualization.
About this task
Data Virtualization users require interactive query performance to access to data of any size,
any format, and any source. To avoid query executions that are time-consuming, Data Virtualization
Managers can create a cache entry to improve the performance by caching
the result set of your queries.
It is recommended to limit the number cache entries, irrespective of their size. Having many
cache entries increases the cost of compiling and evaluating user queries, and decreases service
performance.
Procedure
- Go to .
- Click Add new cache in the Active data
caches tab.
- Enter-valid queries in the SQL editor to create your cache.
Note:
- You must include a schema name in your query. For more information, see Creating schemas.
- You cannot include SQL terminators in your query.
You can use the Syntax assistant to facilitate the writing of SQL
statements. Additionally, you can filter and search the content of your SQL statements.
- Click Test queries with cache. To find out whether
the cache can match a query (or queries) at run time, follow these steps.
Your queries display in the Selected queries section. You can
edit and remove selected queries before you test a query with cache by following these steps.
- Click Test queries to estimate impact of cache on your selected
queries.
The
Status column in the
Selected queries section
provides the following information.
- Matched to cache
- Data Virtualization determined that the cache matched with the selected
queries, and if you choose to create the cache, those queries are likely to use the cache at run
time. Whether query performance improves or not depends on other factors in the overall query
plan.
Note: There is no guarantee that queries will use the cache. Other near-identical caches
might override the utility of the cache that you are creating.
- Not matched to cache
- Data Virtualization determined that the cache does not match the selected queries, and hence,
might not have any influence on query performance.
- Error
- Data Virtualization cannot validate whether the selected queries match the cache or not. Refer
to the error message for details.
- Click Use this cache to apply cache to the selected
queries.
- Click Next. To set a refresh rate for the cache,
follow these steps.
A frequent refresh prevents the cache from becoming stale in time.
Set the refresh frequency according to these values.
- None, manual only: If you select this option, you can
refresh the cache manually by using the action menu.
- Minutes: Select the interval
in minutes to refresh your cache. For example, you can refresh every 15 minutes.
- Hourly: Select time (minute) of the hour to refresh
your cache. For example, you can refresh every hour at the 30 minutes past the hour.
- Daily: Select time of the day to refresh your cache.
For example, you can refresh every day at 12:00 AM.
- Weekly: Select days of the week and time of the day
to refresh your cache. For example, you can refresh every Monday, Wednesday, and Friday at 12:00 AM.
- Monthly: Select weeks of the month, days of the
week, and time of the day to refresh your cache. For example, you can refresh on the first day of
the month at 12:00 AM.
- Click Next.
- Enter a name for your cache, ensure that the summary is correct, and click
Create cache.