0 / 0
Adding data caches in Watson Query

Adding data caches in Watson Query

Watson Query 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 Watson Query.

About this task

Watson Query 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, Watson Query 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

  1. Go to Data > Data virtualization > Virtualization > Cache management.
  2. Click Add new cache in the Active data caches tab.
  3. 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.

    Restriction: Cache entries are subject to certain restrictions in Watson Query. For more information, see Restrictions for cache entries.
  4. Click Test queries with cache. To find out whether the cache can match a query (or queries) at run time, follow these steps.
    • Enter a query in the SQL editor.
    • Go to the Select from existing tab and choose the queries for which you want to test cache impact.
      Tip: You can filter existing queries by creation date, creator, execution time, and types of query.
    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.
    1. 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
      Watson Query 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
      Watson Query determined that the cache does not match the selected queries, and hence, might not have any influence on query performance.
      Error
      Watson Query cannot validate whether the selected queries match the cache or not. Refer to the error message for details.
    2. Click Use this cache to apply cache to the selected queries.
  5. 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.
  6. Click Next.
  7. Enter a name for your cache, ensure that the summary is correct, and click Create cache.
Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more