In This Topic
dotConnect for Mailchimp can translate a subset of SQL-92 SELECT statements to the corresponding Mailchimp API calls for remote execution. As remote execution provides much higher performance than local one, it makes sense to use SELECT statements that are translatable to API Calls.
SELECT statements that correspond to the following pattern and conditions can be converted to API calls and executed remotely:
SELECT result_columns FROM table [WHERE where_condition] [ORDER BY orderByClause] [LIMIT count OFFSET offset]
- result_columns can be a list of the columns, separated with commas, or "*", which means all the result columns.
- table - the name of the Mailchimp object.
- where_condition - one or more conditions, united with 'AND'. Condition must be a comparison of a column of the selected object and a constant or a parameter. No arithmetic or other functions are allowed. The following comparison operators are supported: =, <=, >=, !=, <, >, IN, LIKE, BETWEEN, IS NULL, IS NOT NULL. Only the columns that allows filtering can be included in where_condition, and only the supported operators vary for different columns. See the details below.
- orderByClause - one column name optionally followed by "ASC" or "DESC". Only the following columns can be used in the ORDER BY clause:
- Campaigns object - CreatedDate
- ABTestingCampaigns object - SendTime
- Lists object - WebId and CreatedDate
- Images and Files objects - CreatedDate and Size
If a SELECT statement contains any of the features, not explicitly mentioned above, it cannot be translated to Mailchimp API calls and by default, it is executed locally, which usually takes more time and resources than remote execution. (Local execution can be disabled completely if necessary - see Select Statement Execution).
Exceptions
If a statement contains any of the exceptions, listed below, it also cannot be translated to the Mailchimp API calls and is executed locally.
- WHERE clause with custom fields (merge tags) of the ListMembers object.
- LIMIT clause for objects other than Campaigns, ABTestingCampaigns, Lists, Conversations, Images, or Files or with count more than 100 cannot be converted to API calls.
Filterable Columns and Allowed Comparison Operators
Mailchimp supports only the following columns with the following comparison operators in the WHERE clause:
- Folders object - Type column with the "=" operator.
- Campaigns object - "=" operator for the Id, ListId, FolderId, ParentId, Title, Subject, Type, Status, FromName, and FromEmail columns; ">=" and "<=" operators for the SendTime column.
- ABTestingCampaigns object - "=" operator for the Status column, ">=" and "<=" operators for the SendTime and CreatedDate columns.
- ListMembers object - "=" operator for the ListId and Status columns.
- ListMergeFields object - "=" operator for the ListId and Type columns.
- SavedSegments object - "=" operator for the ListId column.
- StaticSegments object - "=" operator for the ListId column.
- InterestGroupings object - "=" operator for the ListId column.
- InterestGroups object - "=" operator for the ListId and GroupingId columns.
- Lists object -">=" and "<=" operators for the CreatedDate column.
- Templates object - "=" operator for the Type, Category, and FolderId columns.
- Locations object -"=" operator for the ListId column.
- Clients object - "=" operator for the ListId column.
- Conversations object - "=" operator for the ListId and CampaignId columns.
- ConversationMessages object - "=" operator for the ConversationId column.
- VIPs object - "=" operator for the ListId column.
- Images object - "=" operator for the Id column.
- Files object - "=" operator for the Id column.
- SentTo object - "=" operator for the CampaignId and Status columns.
- Opened object - "=" operator for the CampaignId column.
- NotOpened object - "=" operator for the CampaignId column.
- BounceMessages object - "=" operator for the CampaignId column, and ">" operator for the Date column.
Remote Execution Performance Tips
When querying the Campaigns object, an additional request is performed if the ContentHtml or ContentText column is queried. If you don't need the values of these columns, don't include them in the query.
When querying the Templates object, an additional request is performed if the Html column is queried. If you don't need its values, don't include it in the query.
In other cases, all the columns are actually queried regardless of the column list in the query, and omitting some columns in the SELECT clause does not affect performance.
Many objects depends on the other objects directly, and cannot be retrieved without the ID of the parent entity. In such cases parent objects are read first, and then the queried objects are read by the second request. If the ID of the corresponding parent object will be specified in the WHERE clause, the first step can be omitted, and this improves performance. Here is the list of such objects with the necessary parent ID fields:
- ListMembers, ListMergeFields, SavedSegments, StaticSegments, InterestGroupings, InterestGroups, Locations, and Clients objects - it's better to specify ListId.
- i??onversationMessages object - it's better to specify ConversationId
- SentTo, Opened, NotOpened, and BounceMessages objects - it's better to specify CampaignId.