I want to improve the performance of some queries I am running to the Query API. What are some suggestions on when to use the Job API to cache data in CData Embedded Cloud?
Solved
Embedded Cloud - Guidance for Caching
Best answer by James T
Job API
When building applications with CData Embedded Cloud, the Job API's caching capabilities provide powerful solutions for common data integration challenges.
Once a table is cached, all queries to that table execute against the cache, not the live data source. Schedule your cache refresh intervals based on how current your data needs to be for your application's use case.
Control & Flexibility
CData Embedded Cloud gives you control to decide whether to implement caching and flexibility to decide how & when it should be used.
Have a query powering an analytics dashboard that is taking to long to update? Cache the underlaying table(s) to improve user experience. Requests to the Query API will be directed to the cached table.
Have a mission-critical integration that needs to always query the latest data? Use the Query API as usual, without configuring a cache job for these table(s)
Considerations
Here are some key reasons to implement table caching:
1. Overcome API Rate Limits
Many SaaS platforms impose strict limits on API requests (e.g. Salesforce, HubSpot, QuickBooks). By caching tables, your application queries the cached data instead of making repeated API calls to the live source. Cache updates run asynchronously on your specified schedule (hourly, daily, etc.), keeping you well within rate limit boundaries while maintaining data freshness.
2. Improve Performance with Large Data Sets
When working with tables containing thousands or millions of rows, querying live data can be slow and resource-intensive. Cached tables store data in a PostgreSQL database, enabling faster query responses (especially when accessing slow APIs). Your users experience quicker results, even when filtering, sorting, or aggregating large datasets with federated queries.
3. Enable Complex Multi-Table Joins (with MCP or direct REST API)
Cross connection joins (i.e. federated queries) and complex analytical queries can be impractical against live APIs. With cached tables, you can perform more sophisticated SQL operations—joining customer data from Salesforce with transaction data from QuickBooks, for example—all within the high-performance PostgreSQL cache environment.
4. Flexible Data Residency for Security & Compliance
The PostgreSQL caching database can be provided either by your OEM account OR by your end-users. This flexibility is crucial for handling sensitive data: customers with strict data governance requirements can host the cache in their own infrastructure, maintaining complete control over where their data resides.
5. Efficient Incremental Updates
When data sources support a timecheck column (like LastModifiedDate or UpdatedAt), the Job API performs incremental refreshes—syncing only new or changed records rather than reloading entire tables. This dramatically reduces sync times and API consumption as your datasets grow.
Job API
When building applications with CData Embedded Cloud, the Job API's caching capabilities provide powerful solutions for common data integration challenges.
Once a table is cached, all queries to that table execute against the cache, not the live data source. Schedule your cache refresh intervals based on how current your data needs to be for your application's use case.
Control & Flexibility
CData Embedded Cloud gives you control to decide whether to implement caching and flexibility to decide how & when it should be used.
Have a query powering an analytics dashboard that is taking to long to update? Cache the underlaying table(s) to improve user experience. Requests to the Query API will be directed to the cached table.
Have a mission-critical integration that needs to always query the latest data? Use the Query API as usual, without configuring a cache job for these table(s)
Considerations
Here are some key reasons to implement table caching:
1. Overcome API Rate Limits
Many SaaS platforms impose strict limits on API requests (e.g. Salesforce, HubSpot, QuickBooks). By caching tables, your application queries the cached data instead of making repeated API calls to the live source. Cache updates run asynchronously on your specified schedule (hourly, daily, etc.), keeping you well within rate limit boundaries while maintaining data freshness.
2. Improve Performance with Large Data Sets
When working with tables containing thousands or millions of rows, querying live data can be slow and resource-intensive. Cached tables store data in a PostgreSQL database, enabling faster query responses (especially when accessing slow APIs). Your users experience quicker results, even when filtering, sorting, or aggregating large datasets with federated queries.
3. Enable Complex Multi-Table Joins (with MCP or direct REST API)
Cross connection joins (i.e. federated queries) and complex analytical queries can be impractical against live APIs. With cached tables, you can perform more sophisticated SQL operations—joining customer data from Salesforce with transaction data from QuickBooks, for example—all within the high-performance PostgreSQL cache environment.
4. Flexible Data Residency for Security & Compliance
The PostgreSQL caching database can be provided either by your OEM account OR by your end-users. This flexibility is crucial for handling sensitive data: customers with strict data governance requirements can host the cache in their own infrastructure, maintaining complete control over where their data resides.
5. Efficient Incremental Updates
When data sources support a timecheck column (like LastModifiedDate or UpdatedAt), the Job API performs incremental refreshes—syncing only new or changed records rather than reloading entire tables. This dramatically reduces sync times and API consumption as your datasets grow.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
