Tuning queries by using cache
Caching is a well-known method for improving read performance in databases. Synapse SQL supports a feature called Result set caching. As the name implies, this enables the results to be cached and reused if the query doesn't change. Once result set caching is enabled, the subsequent query executions directly fetch the results from the cache instead of recomputing the results. The result set cache is only used under the following conditions:
- The query being considered is an exact match.
- There are no changes to the underlying data or schema.
- The user has the right set of permissions to the tables referenced in the query.
You can enable result set caching at the database level in Synapse SQL using the following SQL statement:
ALTER DATABASE [database_name] SET RESULT_SET_CACHING ON;
You can also turn result set caching on from within a session by using the following command:
SET RESULT_SET_CACHING { ON | OFF };
Note...