How to Delete Inactive Contacts in Batches in Kentico 8
When your database is very large, and you suddenly enable the deletion of inactive contacts (Settings application -> On-line marketing -> Contact management -> Inactive contacts -> Delete inactive contacts), the scheduled task responsible for the deletion may result in a timeout as it tries to delete all inactive contacts at once.
This was changed in Kentico 9, where the same task deletes only 1,000 contacts at once and runs only during the off-peak period (2–6 AM).
In Kentico 8, however, you will need to create a custom scheduled task to delete contacts in batches to avoid timeouts and decreased performance.
It is all done using the following API method:
CMS.OnlineMarketing.ContactInfoProvider.DeleteContactInfos(whereCondition, batchLimit);
A reference for the method can be found here: https://devnet.kentico.com/docs/8_2/api/html/M_CMS_OnlineMarketing_ContactInfoProvider_DeleteContactInfos_1.htm
As you can see, the amount of processed contacts can be easily adjusted via the batchLimit parameter.
However, the most important part of the method is the whereCondition parameter. You have to ensure that you provide a relevant where condition SQL query. Otherwise, incorrect contacts will be deleted, or worse—if you don’t provide any where condition at all—all contacts will be deleted!
For example, the following where condition would be used to delete all contacts with no email address from ALL sites:
string whereCondition = "(ContactEmail = '' OR ContactEmail IS NULL)";
Once you have all your custom logic defined, all you need to do is to create a custom scheduled task according to our documentation: https://docs.kentico.com/display/K82/Scheduling+custom+tasks
An Example of a Custom Scheduled task
To make the custom implementation easier for you, feel free to use the example below. It creates a scheduled task that deletes 1,000 contacts every time it is executed (it loads all necessary values from the Settings application -> On-line marketing -> Contact management -> Inactive contacts and, therefore, can be used instead of the original contact deletion task):
- Open your web project in Visual Studio
- Add a new class into the App_Code folder (or CMSApp_AppCode -> Old_App_Code if the project is installed as a web application) and name the class DeleteInactiveContactsInBatches.cs.
- Delete the generated code and use the following code instead:
using System;
using CMS;
using CMS.Scheduler;
using CMS.SiteProvider;
using CMS.DataEngine;
using CMS.OnlineMarketing;
using CMS.DataEngine.Query;
[assembly: RegisterCustomClass("Custom.DeleteInactiveContactsInBatches",typeof(Custom.DeleteInactiveContactsInBatches))]
namespace Custom
{
/// <summary>
/// Summary description for DeleteInactiveContactsInBatches
/// </summary>
public class DeleteInactiveContactsInBatches : ITask
{
#region "Public methods"
/// <summary>
/// Executes the task.
/// </summary>
/// <param name="task">Task to process</param>
public string Execute(TaskInfo task)
{
DeleteContactsForAllSites();
return string.Empty;
}
#endregion
#region "Private methods"
/// <summary>
/// Delete old (inactive) contacts for all sites.
/// </summary>
private void DeleteContactsForAllSites()
{
InfoDataSet<SiteInfo> sites = SiteInfoProvider.GetSites().TypedResult;
foreach (SiteInfo site in sites)
{
if (AllowDeleteContactsForSite(site))
{
DeleteContactsForSite(site.SiteID);
}
}
}
/// <summary>
/// Indicates if contacts should be deleted for given site.
/// </summary>
private bool AllowDeleteContactsForSite(SiteInfo site)
{
return (site != null)
&& SettingsKeyInfoProvider.GetBoolValue(site.SiteName + ".CMSEnableOnlineMarketing") // checks if the "Enable on-line marketing" option is enabled
&& SettingsKeyInfoProvider.GetBoolValue(site.SiteName + ".CMSDeleteInactiveContacts"); // checks if the "Delete inactive contacts" option is enabled
}
/// <summary>
/// Deletes contacts for specified site.
/// </summary>
private void DeleteContactsForSite(int siteId)
{
string siteName = SiteInfoProvider.GetSiteName(siteId); // gets the site name
/* Gets all values from the Settings application -> On-line marketing -> Contact management -> Inactive contacts */
int lastActivityOlderThen = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSLastActivityOlderThan"); // Last activity older then (days)
int contactCreatedBefore = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSContactCreatedBefore"); // Contact created before (days)
int contactLastLogon = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSContactLastLogon"); // Contact last logon before (days)
int contactLastModified = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSContactLastModified"); // Contact last modified (days)
int contactMergedBefore = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSContactMergedWhen"); // Contact merged before (days)
bool contactsMergedIntoSiteContact = SettingsKeyInfoProvider.GetBoolValue(siteName + ".CMSContactMergedSite"); // Merged into site contact only
bool contactsMergedIntoGlobalContact = SettingsKeyInfoProvider.GetBoolValue(siteName + ".CMSContactMergedGlobal"); // Merged into global contact only
int contactIsAnonymous = SettingsKeyInfoProvider.GetIntValue(siteName + ".CMSContactIsAnonymous"); // Contact is anonymous
string contactCustomQuery = SettingsKeyInfoProvider.GetValue(siteName + ".CMSContactCustomQuery"); // Custom SQL WHERE condition
var where = new WhereCondition();
/* checks all the values and creates an appropriate SQL where conditions */
if (lastActivityOlderThen > 0)
{
DateTime limitDateTime = DateTime.Now.AddDays(-lastActivityOlderThen);
var groupedActivities = ActivityInfoProvider.GetActivities()
.Column("ActivityActiveContactID")
.GroupBy("ActivityActiveContactID")
.Having(new WhereCondition().WhereLessOrEquals(
new AggregatedColumn(AggregationType.Max, "ActivityCreated"), limitDateTime));
var existingActivity = ActivityInfoProvider.GetActivities()
.TopN(1)
.Column("ActivityActiveContactID")
.WhereEquals("ActivityActiveContactID", "ContactID".AsColumn());
where = where.WhereIn("ContactID", groupedActivities)
.Or(new WhereCondition().WhereLessOrEquals("ContactCreated", limitDateTime).WhereNotExists(existingActivity));
}
if (contactCreatedBefore > 0)
{
where = where.WhereLessOrEquals("ContactCreated", DateTime.Now.AddDays(-contactCreatedBefore));
}
if (contactLastLogon > 0)
{
where = where.WhereLessOrEquals("ContactLastLogon", DateTime.Now.AddDays(-contactLastLogon));
}
if (contactLastModified > 0)
{
where = where.WhereLessOrEquals("ContactLastModified", DateTime.Now.AddDays(-contactLastModified));
}
if (contactMergedBefore > 0)
{
where = where.WhereLessOrEquals("ContactMergedWhen", DateTime.Now.AddDays(-contactMergedBefore));
}
if (contactsMergedIntoSiteContact)
{
where = where.WhereNotNull("ContactMergedWithContactID");
}
if (contactsMergedIntoGlobalContact)
{
where = where.WhereNotNull("ContactGlobalContactID");
}
if (contactIsAnonymous == 1) // adds an additional SQL query after checking the value of the "Contact is anonymous" drop-down list (0 => Doesn't matter)
{
where = where.WhereTrue("ContactIsAnonymous");
} else if (contactIsAnonymous == 2)
{
where = where.WhereFalse("ContactIsAnonymous");
}
where = where.Where(contactCustomQuery); // adds the value of the "Custom SQL WHERE condition"
if (!String.IsNullOrEmpty(where.ToString())) // adds the final SQL query to ensure only site relevant contacts are deleted
{
if(siteId > 0)
{
where = where.WhereEquals("ContactSiteID", siteId);
}
else
{
where = where.WhereNull("ContactSiteID");
}
}
string whereCondition = where.ToString(true);
if (!String.IsNullOrEmpty(whereCondition))
{
ContactInfoProvider.DeleteContactInfos(whereCondition,1000); // deletes inactive contacts in batches (each batch having 1,000 contacts)
}
}
#endregion
}
}
- Save the file
- Log into the Kentico Administration Interface and open the Scheduled tasks application
- Select and edit the Delete inactive contacts task
- Disable the task by unchecking the Task enabled checkbox
- Click the Save button
- Click Tasks tab (it will return you back to the list of scheduled tasks)
- Click the New task button and fill in the properties of the new task according to the following screenshot:
- Check the Run task in separate thread checkbox to ensure the task won’t affect the main thread
- Click Save
-
Well done! You have just created a custom scheduled task that will delete inactive contacts in batches! It will run every 12 hours, but feel free to adjust it according to your needs. For example, you could set it to run every hour between 02:00 and 06:00.
Disclaimer: Even though the code should work as described, please make sure you test it first to ensure it meets your needs.