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 Marketo ODBC Data Connector Reference Guide.
For example, when executing the following query, filtering is delegated to the server:
SELECT * FROM Leads WHERE Id = 6
Note:
When filtering data from the Leads table, if more than 1000 rows are returned, Marketo returns an error.
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 Marketo ODBC Data Connector Reference Guide.
Enable Request Throttling
You can enable request throttling to improve performance in the connector.
Request throttling allows the connector to limit the number of requests that are submitted to the data source from each user account in a given period of time. This can be used to maintain a reasonable response time for data source requests.
By default, request throttling is disabled. To enable request throttling, set the UseThrottling key in your DSN or connection string to 1.
For more information, see Use Throttling
Insert Columns One at a Time
Column insertion is supported for the Leads table only. When executing an INSERT statement on the Leads table, do not specify more than one column.
For example, you can execute the following query to insert a new row with an email address:
INSERT INTO Leads (Email_Address) VALUES ('ODBCINSERTEST3@yahoo.com')
Inserting a new row that contains new data for multiple columns is not supported.
Load Tables One at a Time
Marketo limits each account to a maximum of ten concurrent requests. If you load multiple tables at once, you may reach this limit. To avoid this, load tables one at a time.