Writing Efficient Queries in Campaign Classic

This document explains how to write optimized queries running on Adobe Campaign to limit the workload on the database and improve user experience.

Achieve better performance by running fewer, but better, optimized queries.

Design queries using these tips to optimize them.

Efficient queries rely on indexes.
Perform joins using numeric fields instead of string fields.
Use an index for all joins.
Avoid performing outer joins. Whenever possible, use the Zero ID record to achieve outer join functionality.
Beware of functions like Lower(...).

When the Lower function is used, the Index is not used. Check queries using the "like" instruction or the "upper" or "lower" instructions carefully. Apply "Upper" on the user input, not on the database field.
Use the correct data type for joins.

Ensure that the "where" clause is the same type as the field.

A common mistake is: iBlacklist='3' where iBlacklist is a numeric field, and '3' signifies a text value.

Make sure you know what the execution plan of your query will be.

Avoid full table scans, especially for real-time queries or near real-time queries running every minute.
Use the query's Filtering dimension instead of using the exists such as operator.

In queries, exists such as conditions in filters are not efficient.

The exists such as condition is the equivalent of a sub-query in SQL:
```
select iRecipientId from nmsRecipient where iRecipientId IN (select
iRecipientId from nmsBroadLog where (...))
```
The best practice is to use the query's filtering dimension instead.

The equivalent of the filtering dimension in SQL is the inner join:
```
select iRecipientId from nmsRecipient INNER JOIN nmsBroadLog ON (...)
```

Defining links on the schema will determine the join conditions.

The linked table should have an unique index on the primary key and the join should be on this field.

Define keys on numeric fields instead of string fields.

Build a DEV platform with similar volumes, parameters, and architecture as the PROD platform.
Use a TEST (or pre-production platform), similar to the PROD environment. Use the same values for the TEST and PROD environments. As much as possible, use the same:
- Operating System.
- Version.
- Data.
- Application.
- Rights.
- Volumes.
Note:
A feature that works in a TEST environment may not work in a PROD environment where the data may be different. Try to identify the main differences in order to anticipate risks and to prepare solutions.
Make configurations that match the target volumes.

Large volumes require specific configurations. A configuration that worked for 100,000 recipients may not work for 10,000,000 recipients.

Consider how the system will scale when it goes live. Just because something works on a small scale does not mean that it will be suitable with greater volumes. Testing should be done with similar volumes to the volume in production. You should also evaluate the effect of changes in volumes (number of calls, size of the database) at peak hours, peak days, and across the life of the project.

How to write efficient queries