Results are cached to disk when they exceed the memory size specified by the ServiceSQLDiskCacheMemSize service attribute (see ServiceSQLDiskCacheMemSize). Caching to disk is enabled in the following cases:
A query that includes post-processing options such as ORDER BY and GROUP BY clauses requires that the OpenAccess SDK SQL engine retrieves the entire result set and then performs the ORDER BY or GROUP BY processing. If this result set is large, then the results will be cached to disk. The post-processing option is processed on disk and results will be retrieved from disk as requested by the client application. This approach avoids a large memory requirement.
If the IP is not cursor-based and the result set is large, the OpenAccess SQL engine caches results being returned by the IP to disk. After the IP completes returning all the results, the OpenAccess SQL engine retrieves results from disk as requested by the client application.
Note: When the query is processed on disk, the data size for XO_TYPE_CHAR, XO_TYPE_VARCHAR, XO_TYPE_WCHAR, XO_TYPE_WVARCHAR, XO_TYPE_BINARY, and XO_TYPE_VARBINARY columns cannot exceed 64 KB.
A query that includes post-processing options such as ORDER BY and GROUP BY clauses requires that the OpenAccess SDK SQL engine retrieves the entire result set and then performs the ORDER BY or GROUP BY processing. If this result set is large, then the results will be cached to disk. The post-processing option is processed on disk and results will be retrieved from disk as requested by the client application. This approach avoids a large memory requirement.