.png?width=667&height=667&ext=.png)
Ensure Your Kentico EMS Site Is Running At Peak Performance
Introduction
If you are a long time user of Kentico or a Kentico developer who maintains sites that utilize Kentico EMS, this tip is for you. So stop what you are doing, go grab a cup of coffee (or something stronger) and continue reading to see how you can keep your online marketing solution running in top notch form. I should also mention that you won't need to change a single line of code to do so. Yes that's right, not a single line.
I am going to start with a question though. Have you ever heard the saying that "an ounce of prevention is worth a pound of cure"? If you have, then this post should make a ton of sense. If you have not, then maybe I can teach you something today.
The Scenario
In the last few weeks I have had a few different clients ask me and my team at BizStream to look into some performance optimizations on their Kentico websites. A common place to start for a task such as this is to utilize the Kentico SQL Debugging feature to look for queries that are taking up above average amounts of resources or running for longer periods of time than expected.
In a few of those scenarios we found a common item. A simple lookup to the Contacts table (OM_Contact) was taking more than a second, and in some cases more than many seconds. This is not typical for the system because Kentico has put great care into the performance aspect of the online marketing solution that it has. Especially with the introduction of Kentico 8.1 and Kentico 8.2.
In troubleshooting why a simple SQL select statement to lookup the current contact was taking so long, we noticed something very obvious. There was simply a large amount of data in the OM_Contact table. When I mean large, I mean millions of rows. It was quite surprising to see this much data at first, but then it dawned on me. The sites in question had all been running for quite some time. In fact, one of our customers has been using Kentico EMS since version 6.0 which means the site has been running for around 3 years. As time goes on the Contact management module simply just collects a lot of data. And searching through this much data was causing the query to run longer than it should, which in turn was technically degrading the performance of the site.
Quick Tip: Want to know how many rows your Contact table has in your installation and don't have access to SQL Management Studio ? Create a new Custom View in the Database Objects application in Kentico 8 (or System Tables application in Kentico 7) using this query:
SELECT Count(*) As 'ContactCount' FROM [dbo].[OM_Contact]
The Solution
Luckily Kentico provides a solution to this problem out of the box. It just happens to be a little hidden in the Settings application. If you navigate down into Settings -> On-line Marketing -> Contact Management -> Inactive Contacts you can see this functionality.
Most of these settings are pretty much self-explanatory. Basically you can configure the system to remove contact records that haven't had activity in X number of days, contacts that were created X number of days ago, and so on.
What I do think is interested here is the last setting that allows you to customize how you want to configure the SQL delete statement. Using the Custom SQL WHERE condition you can write the condition to only delete contacts without email addresses or without geo-location information, or that have a score of 0. Don't forget to wrap your condition in parenthesis though because what you type in the setting with be appended to the query. I have also verified with Kentico that this functionality will deleted the related OM_Activity, OM_ContactGroupMember, OM_IP, etc. etc. relationship tables.
There are a few more details about deleting inactive contacts included in the Kentico devnet Delete inactive contacts blog post from the product owner of the Kentico online marketing solution, Vita Janecek. Even though the post is from 2011 it is still relevant today.
So there you have it, the solution boils down to:
Simply enable the Delete Inactive contacts setting to keep from filling up your database with old or stale records. Configuring this will help keep your Kentico EMS site running at peak performance.
But…. You knew there had to be a catch right ? What if you have been using Kentico for a long period of time like my other customers. You have to take this one next fact into consideration. The system's method to removing these contact records is to run as a scheduled task (once a week by default), a set of DELETE statements for 1,000 contact records at a time, that match the Delete Inactive contacts condition. Yes that is right. It runs only 1,000 at a time. That might take a few days to clean up over a million records.
Bottom line, I recommended enabling this setting from the get go if you are using Kentico EMS.
What If I Forgot Or Did Not Have The Setting Enabled?
Well then you are like most of my customers. But I am hoping this next section can help you. Below is a SQL Statement to run bulk deletes on the OM_Contact table and related tables that are dependent based on constraints.
Let me be very clear, this script is not tested in all scenarios, it is not supported by Kentico, nor is it supported by me. Use this at your own risk, and please for everyone's sake take a backup before you use it. Also run it on development instance first. However, the following worked for me with a Kentico 8.1 and 8.2 database. In theory it should work in 7.0 and 8.0 as well. But again test, test, and re-test.
SQL Script For Cleanup
/* KenticoDeleteOMContact.sql */ /* Goal: Clear out OM_Contact in bulk */ /* Description: Bulk delete OM_Contact data */ /* and all of the related FK data by date */ /* Intended Verison: Kentico 8.x */ /* Author: Brian McKeiver (mcbeev@gmail.com)*/ /* Revision: 1.0 */ /* Comment: Removing 10k rows took ~ 8 secs */ DECLARE @imax INT DECLARE @i INT DECLARE @cnt INT DECLARE @batchSize INT DECLARE @whereParam NVARCHAR(max) DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME --date range to remove data from, start small to make sure it works !! --SET @StartDate = DATEADD(yyyy, -2, DATEADD(dd, 1, GETDATE())) SET @StartDate = '2/14/2015' --SET @EndDate = DATEADD(yyyy, -1, DATEADD(dd, 1, GETDATE())) SET @EndDate = '2/14/2015' --How many to delete at a time, be careful! SET @batchSize = 10000 --used for mass delete param SET @whereParam = '(ContactEmail IS NULL) AND (ContactCreated BETWEEN '''+ CAST(@StartDate as varchar(20)) +''' AND '''+ CAST(@EndDate as varchar(20)) +''')' --figure out how many times we need to loop to clear the data in bulk SELECT @cnt = Count(ContactID) FROM OM_Contact WHERE OM_Contact.ContactCreated BETWEEN @StartDate AND @EndDate And ContactEmail IS NULL PRINT 'Records found to delete based on searching ' + CAST(@StartDate as varchar(20)) + ' - ' + CAST(@EndDate as varchar(20)) + ' :' PRINT @cnt PRINT 'Starting to delete at: '+ CAST(GETDATE() as nvarchar) --figure out how many times we need to loop to clear the data in bulk, -- and make sure we have 1 extra iteration in case of less than 100 SET @imax = (@cnt / @batchSize) + 1 --loop counter SET @i = 1 SET NOCOUNT ON; --release the kracken WHILE (@i <= @imax) BEGIN EXEC [Proc_OM_Contact_MassDelete] @whereParam, @batchSize PRINT 'Deleted batch: '+ CAST(@i as nvarchar) SET @i = @i + 1 END PRINT 'Finished to delete at: '+ CAST(GETDATE() as nvarchar) PRINT 'Max Records deleted: ' + CAST(((@i - 1) * @batchSize) as nvarchar)
Running this script worked well for me. You can modify the batch size to change how many rows you delete at a time, and the where condition can be modified as well. Don't forget to change the start and end date parameters. I made them just one day in case someone copied and pasted the script and ran it blindly.
Conclusion
If you are using Kentico to power your website and utilize any of the digital marketing capabilities that the system has to offer, congratulations you are using a top notch platform. Not only should your website be easy to manage, but you should also be able leverage advanced marketing tools such as activity tracking, A/B testing, and content personalization to gain a high level of insight into your website visitor's behavior and patterns. But don't forget that the system could clog down if you have millions and millions of rows of data. Setting the Delete Inactive Contacts setting to is just the ounce of prevention needed in this case. Happy optimizing!
