Batch Size Sending Limitations

After researching the limitations that exist for sending large batches of emails, here is some of the items I found to apply. The question of how large an email send can be is more of a question of what is practical.  Campaign Enterprise does not set any limitations on the number of emails that can be sent in one batch, but there are Operating System and system resource limitations that are limiting.

To understand the problem, you have to look at what the machine has to do when asked to send 1 million emails in one batch.  When the Campaign Enterprise is first started, it accesses your database using the query defined in the campaign, using a SQL Statement, Table, or Stored procedure.  The result of the query is a list of records which is theoretically a snapshot of a portion of your database based on your criteria.  Since Campaign will have to cycle through this list, maybe for hours depending on your sending speed, this list will have to be stored in memory.  If the entire 1 million record list with all the data associated was loaded into memory, not only would that take a lot of time to transfer this data from the disk into memory, this assumes you have enough memory to handle such a large set of information, so this turns out not to be practical. To balance speed versus resources, what Campaign Enterprise does is it loads into memory just the Unique ID data column of the records that are selected by your query.  In this way, a list of the selected records can now be cycled through, and each record is loaded into memory in turn and the data from that record can be used to send each email out.

Where we have seen the bottlenecks in sending large email batches is in the initial database query. When encountering large batch bottleneck, the SQL Statement being used is not optimized.  Typical problems with a query not being optimized are:

- Columns being used in conditional statements or in table joins are not indexed
- Calculations are being done in the conditional statements

Remember, you can have a perfect SQL Statement or stored procedure, but if you have ONE of these problem elements it will reduce the query time to a crawl.  Many times I have had clients say “this has worked perfectly for years and not it died and nothing has changed”, only to encounter that a column they were using dropped its index or the index was so fragmented it was more of a burden than a help.

Efficiency in Campaign Enterprise starts with efficiency at an Operating System and Database System level.  There is nothing Campaign Enterprise can do to compensate for these outsides systems being deficient.  This is not to say Campaign Enterprise is perfect and its the “other guy”, but more than not it is our experience that the culprit of performance is at the machine/database level.​

Now practically, what do you do when you need to send over 1 million emails?  When datasets get large usually you have to use a different database design strategy.  The key is not how many emails can I send out in one batch, it is how can I sent multiple batches to get all my emails out without exceeding the computer’s limitations.  One strategy to use for sending large batches of emails is to use or create a new column in your database that will know if a record has been sent.  You can do this in Campaign by filtering on this column, and setting the data to “sent” after the email is sent. When you run the campaign again, it will only send to those that have not been sent to yet. There are other ways of accomplishing batching but they all depend on database design strategy.