Using the driver : Bulk operations

Bulk operations

The driver supports Oracle Eloqua bulk operations with some limitations.

Note: Bulk operations are most efficient for queries that return large amounts of data for a relatively small set of columns. For example, SELECT folderid,name,country,c_website FROM Account WHERE country='Switzerland' has only four columns but many rows.

Bulk operations for some Select queries with a Top n clause are not supported because it is usually faster to use a standard query to fetch more columns for a few rows than to use a bulk operation. Nevertheless, you can use the BulkTopThreshold connection property to control, in part, how the driver handles queries with a Top n clause. The default value of BulkTopThreshold is 1000.

Queries on Account and Contact tables have additional limitations. The following criteria must be met for queries on Account and Contact tables.

The result must have multiple rows.

The result cannot have more than 250 columns.

The result must include at least one user-defined column.

The query must either have no Top n clause, or the value of n in the Top n clause must be greater than the value specified in the BulkTopThreshold custom property.

The query can only include columns that the bulk interface supports. For more information, see the table below.

Table 9. Columns that cannot be retrieved using bulk operations
Account table	Contact table
accessedAt	accessedAt
createdBy	bouncebackDate
currentStatus	currentStatus
Description	createdBy
folderId	Description
Permissions	folderId
scheduledFor	Permissions
sourceTemplateId	scheduledFor
updatedBy	sourceTemplateId
	subscriptionDate
	updatedBy
	unsubscriptionDate

The following table provides some query examples and describes why they would not take advantage of bulk operations and offers suggestions for modifying them. However, there will obviously be use cases where an application will use queries that cannot be returned using bulk operations.

Query	Description
SELECT * FROM Contact WHERE Country='Switzerland'	There are more than 250 columns in the Contact table. To take advantage of bulk operations, constrain the SELECT statement to a set of less than 250 columns.
SELECT * FROM ContactList WHERE Region='East'	The ContactList table is not supported for bulk operations.
SELECT Id, C_Website FROM Account WHERE Id=17	This returns one row.
SELECT Id, C_Website FROM Contact WHERE Country='Switzerland' AND Region='EAST'	More than one criterion or comparison operator is used in the WHERE clause. Tips: If a query has zero or one comparison operators (meaning one of =, <=, <, >, >=, <>) in the WHERE clause, the query will usually be processed using the bulk operations. If a query contains the LIKE operator in the WHERE clause, the query is not processed using the bulk operations.

In this section:

Efficient queries