Best Practices
The following are some guidelines and recommendations that can help you optimize connector performance.
Design Queries that Use Query Folding
Query folding is a feature that enables the connector to delegate a comparison operation so that it is processed on the server instead of the client. To optimize connector performance, design your queries to filter on columns for which folding is supported. For a list of foldable columns in each table in the schema, see the Simba HubSpot JDBC Data Connector Reference Guide.
For example, when executing the following query, filtering is delegated to the server:
SELECT * FROM Deals WHERE Deal_Id = 6
Whenever possible, use columns that support query folding in your WHERE clauses. If there are any columns that do not support query folding in a WHERE clause, then for those columns the connector must use a less optimal filtering process. This process can consume many API calls and significantly decrease performance. If a WHERE clause contains some columns that support query folding and some that do not, then query folding is used where possible and the remaining filtering is processed by the connector instead of the server.
Design Queries that Use the Top N Command
If query folding is not supported in the table that you need to query, you can optimize connector performance by designing queries that use the Top N command.
Normally, when you execute a query that filters on a column that is not foldable, the connector retrieves all the data and then filters the data to return the appropriate result set. This process can be time-consuming and cause the connector to invoke many API calls. If you execute a query using the Top N command, then the connector retrieves only the specified data, and returns additional data only when you make an API call to retrieve the next page of data.
It is recommended that you use the Top N command when querying data from a table that does not contain foldable columns. To verify if a particular table contains any foldable columns, see the Simba HubSpot JDBC Data Connector Reference Guide.