Best Practices
The following are some guidelines and recommendations that can help you optimize connector performance.
Configure Double-Buffering
You can enable double-buffering to improve performance in the connector. Double-buffering can increase the responsiveness of the connector by enabling it to prefetch data when executing queries. However, when double-buffering is enabled, the connector might consume more memory and resources.
The impact of double-buffering depends on how the transfer speed of your network compares to the data processing speed of the connector. If the transfer speed is significantly higher, then enabling double-buffering allows the connector to make full use of the network's capabilities. Conversely, if the transfer speed is considerably lower, the additional processes involved in double-buffering might cause a decrease in performance.
By default, double-buffering is disabled. To enable double-buffering,
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 Salesforce Marketing Cloud ODBC Driver with SQL Connector Reference Guide.
For example, when executing the following query, filtering is delegated to the server:
SELECT * FROM Campaigns WHERE Id=6
Whenever possible, use columns that support query folding in your WHERE clauses. If there are any columns in a WHERE clause that cannot be folded, 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 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 Salesforce Marketing Cloud ODBC Driver with SQL Connector Reference Guide.
Verify the Scopes Associated with Your Application
The specific scopes that are enabled in your Salesforce Marketing Cloud application determine the tables that you are able to interact with, in the data store. If the connector fails to run your queries or stored procedures, check the scopes that are associated with your application and make sure that you have the required scopes for working with the tables that are part of your query or stored procedure call.
For example, if the "Assets" scope is not enabled for your application, then you cannot retrieve data from the Assets table or execute the GetAssetFile() stored procedure.
For more information about scopes and permissions, see "Installed Packages Scope" in Marketing Cloud Package Development: https://developer.salesforce.com/docs/atlas.en-us.mc-app-development.meta/mc-app-development/data-access-permissions.htm.
Important:
Make sure that you select the "campaign_read" scope in your Salesforce Marketing Cloud application.
Related topics