404 Handler for EPiServer Performance Modifications

As I mentioned in the previous post EPiServer DDS Performance issues, we decided to modify the plugin 404Handler in order to overcome its limitations in our 60k redirects scenario. So, in this post we will briefly explain what modifications did we make and the performance bump we got by doing it.

1de

So without more wait, we will first have to modify the Upgrader.cs class which contains the initialization script that runs when the plugin is installed. In the method Create, we added a new step to create a new table called CustomRedirects where we are going to save the redirects instead of using DDS.


        private static void Create()
        {
            var dba = DataAccessBaseEx.GetWorker();

            Log.Information("Create 404 handler redirects table START");
            var createTableScript = @"CREATE TABLE [dbo].[BVN.NotFoundRequests](
	                                    [ID] [int] IDENTITY(1,1) NOT NULL,
	                                    [OldUrl] [nvarchar](2000) NOT NULL,
	                                    [Requested] [datetime] NULL,
	                                    [Referer] [nvarchar](2000) NULL
                                        ) ON [PRIMARY]";
            var create = dba.ExecuteNonQuery(createTableScript);
            Log.Information("Create 404 handler redirects table END");

            // New table CustomRedirects
            Log.Information("Create custom redirects table START");
            var createTableRedirectsScript = @"CREATE TABLE [dbo].[BVN.CustomRedirects](
                                                [ID][int] IDENTITY(1, 1) NOT NULL,
                                                [OldUrl] [nvarchar] (2000) NOT NULL,
                                                [NewUrl] [nvarchar] (2000) NOT NULL,
                                                [State] [int] NOT NULL,
                                                [NotfoundErrorCount] [int] NOT NULL,
                                                [WildCardSkipAppend] [int] NOT NULL,
                                                CONSTRAINT[PK_BVN.CustomRedirects] PRIMARY KEY CLUSTERED
                                                ([ID] ASC)
                                                WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY]";
            var createRedirects = dba.ExecuteNonQuery(createTableRedirectsScript);
            Log.Information("Create custom redirects table END");

            // If creation of both tables are correct go in here
            if (create && createRedirects)
            {
                Log.Information("Create 404 handler version SP START");
                var versionSp = @"CREATE PROCEDURE [dbo].[bvn_notfoundversion] AS RETURN " + Configuration.Configuration.CurrentVersion;

                if (!dba.ExecuteNonQuery(versionSp))
                {
                    create = false;
                    Log.Error("An error occured during the creation of the 404 handler version stored procedure. Canceling.");
                }

                Log.Information("Create 404 handler version SP END");
            }

            if (create)
            {
                Log.Information("Create Clustered index START");
                var clusteredIndex =
                    "CREATE CLUSTERED INDEX NotFoundRequests_ID ON [dbo].[BVN.NotFoundRequests] (ID)";

                if (!dba.ExecuteNonQuery(clusteredIndex))
                {
                    create = false;
                    Log.Error("An error occurred during the creation of the 404 handler redirects clustered index. Canceling.");
                }

                Log.Information("Create Clustered index END");
            }

            Valid = create;
        }

Now, we will modify the class DataAccessBaseEx.cs, which contains several direct queries to the database table NotFoundRequests, to allow direct queries to the new table. Take into account that the methods shown below are inside the class.


        private const string CustomRedirectstable = "[dbo].[BVN.CustomRedirects]";

        public DataSet GetCustomRedirectByOldUrl(string url)
        {
            var sqlCommand =
                $"SELECT * FROM {CustomRedirectstable} where [OldUrl] = @oldurl";
            var oldUrlParam = CreateParameter("oldurl", DbType.String, 4000);
            oldUrlParam.Value = url;

            var parameters = new List { oldUrlParam };
            return ExecuteSql(sqlCommand, parameters);
        }

        public DataSet GetCustomRedirectsByState(int state)
        {
            var sqlCommand =
                $"SELECT * FROM {CustomRedirectstable} where [State] = @state ORDER BY [OldUrl] DESC";
            var stateParam = CreateParameter("state", DbType.Int32, 0);
            stateParam.Value = state;

            var parameters = new List { stateParam };
            return ExecuteSql(sqlCommand, parameters);
        }

        public DataSet GetCustomRedirects()
        {
            var sqlCommand =
                $"SELECT * FROM {CustomRedirectstable}";

            return ExecuteSql(sqlCommand, null);
        }

        public DataSet GetCustomRedirectsByName(string name)
        {
            var sqlCommand =
                $"SELECT * FROM {CustomRedirectstable} where [OldUrl] LIKE @name OR [NewUrl] LIKE @name";
            var nameParam = CreateParameter("name", DbType.String, 4000);
            nameParam.Value = "%" + name + "%";

            var parameters = new List { nameParam };
            return ExecuteSql(sqlCommand, parameters);
        }

        public void CreateCustomRedirectToDb(string oldUrl, string newUrl, int state, int notFound, bool wildCardAppend)
        {
            Executor.Execute(() =>
            {
                var sqlCommand = @"INSERT INTO [dbo].[BVN.CustomRedirects] 
                                    (OldUrl, NewUrl, State, NotfoundErrorCount, WildCardSkipAppend) 
                                    VALUES 
                                    (@oldurl, @newurl, @state, @notfounderrorcount, @wildcardskipappend)";

                try
                {
                    IDbCommand command = CreateCommand();

                    var oldUrlParam = CreateParameter("oldurl", DbType.String, 4000);
                    oldUrlParam.Value = oldUrl;
                    var newUrlParam = CreateParameter("newurl", DbType.String, 4000);
                    newUrlParam.Value = newUrl;
                    var stateParam = CreateParameter("state", DbType.Int32, 0);
                    stateParam.Value = state;
                    var notFoundParam = CreateParameter("notfounderrorcount", DbType.Int32, 0);
                    notFoundParam.Value = notFound;
                    var wildCardParam = CreateParameter("wildcardskipappend", DbType.Int32, 0);
                    wildCardParam.Value = wildCardAppend ? 1 : 0;

                    command.Parameters.Add(oldUrlParam);
                    command.Parameters.Add(newUrlParam);
                    command.Parameters.Add(stateParam);
                    command.Parameters.Add(notFoundParam);
                    command.Parameters.Add(wildCardParam);
                    command.CommandText = sqlCommand;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Logger.Error("An error occured while creating a custom redirect. Ex:" + ex);
                }
                return true;
            });
        }

        public void UpdateCustomRedirectToDb(int id, string oldUrl, string newUrl, int state, int notFound, bool wildCardAppend)
        {
            Executor.Execute(() =>
            {
                var sqlCommand = @"UPDATE [dbo].[BVN.CustomRedirects] SET 
                                    OldUr = @oldurl, NewUrl = @newurl, State = @state, NotfoundErrorCount = @notfounderrorcount, WildCardSkipAppend = @wildcardskipappend 
                                    WHERE ID = @id";

                try
                {
                    IDbCommand command = CreateCommand();

                    var oldUrlParam = CreateParameter("oldurl", DbType.String, 4000);
                    oldUrlParam.Value = oldUrl;
                    var newUrlParam = CreateParameter("newurl", DbType.String, 4000);
                    newUrlParam.Value = newUrl;
                    var stateParam = CreateParameter("state", DbType.Int32, 0);
                    stateParam.Value = state;
                    var notFoundParam = CreateParameter("notfounderrorcount", DbType.Int32, 0);
                    notFoundParam.Value = notFound;
                    var wildCardParam = CreateParameter("wildcardskipappend", DbType.Int32, 0);
                    wildCardParam.Value = wildCardAppend ? 1 : 0;
                    var idParam = CreateParameter("id", DbType.Int32, 0);
                    idParam.Value = oldUrl;

                    command.Parameters.Add(oldUrlParam);
                    command.Parameters.Add(newUrlParam);
                    command.Parameters.Add(stateParam);
                    command.Parameters.Add(notFoundParam);
                    command.Parameters.Add(wildCardParam);
                    command.Parameters.Add(idParam);
                    command.CommandText = sqlCommand;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Logger.Error("An error occured while updating a custom redirect. Ex:" + ex);
                }
                return true;
            });
        }

        public void DeleteCustomRedirectToDb(string oldUrl)
        {
            var sqlCommand = $"DELETE FROM {CustomRedirectstable} WHERE [OldUrl] = @oldurl";
            var oldUrlParam = CreateParameter("oldurl", DbType.String, 4000);
            oldUrlParam.Value = oldUrl;
            var parameters = new List { oldUrlParam };
            ExecuteSql(sqlCommand, parameters);
        }

We also modified the CustomRedirect.cs class to be a POCO class instead of a DDS store. This class will be used by the DataStoreFactory.cs to convert the direct database queries into a object


    public class CustomRedirect 
    {
        private string _oldUrl;
        public int NotfoundErrorCount;

        /// <summary>
        /// Gets or sets a value indicating whether to skip appending the
        /// old url fragment to the new one. Default value is false.
        /// </summary>
        /// 
        /// If you want to redirect many addresses below a specifc one to
        /// one new url, set this to true. If we get a wild card match on
        /// this url, the new url will be used in its raw format, and the
        /// old url will not be appended to the new one.
        /// 
        /// true to skip appending old url if wild card match; otherwise, false.
        public bool WildCardSkipAppend { get; set; }

        public string OldUrl
        {
            get =&gt; _oldUrl.ToLower();
            set =&gt; _oldUrl = value;
        }

        public string NewUrl { get; set; }

        public int  State { get; set; }

        /// <summary>
        /// Tells if the new url is a virtual url, not containing
        /// the base root url to redirect to. All urls starting with
        /// "/" is determined to be virtuals.
        /// </summary>
        public bool IsVirtual =&gt; NewUrl.StartsWith("/");

        /// <summary>
        /// The hash code for the CustomRedirect class is the
        /// old url string, which is the one we'll be doing lookups
        /// based on.
        /// </summary>
        /// The Hash code of the old Url
        public override int GetHashCode()
        {
            return OldUrl != null ? OldUrl.GetHashCode() : 0;
        }

        public int Id { get; set; }

        public CustomRedirect()
        {
        }

        public CustomRedirect(string oldUrl, string newUrl, bool skipWildCardAppend)
            : this(oldUrl, newUrl)
        {
            WildCardSkipAppend = skipWildCardAppend;
        }

        public CustomRedirect(string oldUrl, string newUrl)
        {
            OldUrl = oldUrl;
            NewUrl = newUrl;
        }

        public CustomRedirect(string oldUrl, int state, int count)
        {
            OldUrl = oldUrl;
            State = state;
            NotfoundErrorCount = count;
        }

        public CustomRedirect(CustomRedirect redirect)
        {
            OldUrl = redirect._oldUrl;
            NewUrl = redirect.NewUrl;
            WildCardSkipAppend = redirect.WildCardSkipAppend;
        }
    }

Finally, we modified the DataStoreFactory.cs class to use the new CustomRedirect.cs class and the new queries in the DataAccessBaseEx.cs class.


        public static CustomRedirect GetCustomRedirectByOldUrl(string url)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            var customRedirectDs = dataAccess.GetCustomRedirectByOldUrl(url);

            CustomRedirect customRedirect = null;

            if (customRedirectDs.Tables[0] == null ||
                customRedirectDs.Tables[0].Rows.Count != 1)
            {
                return customRedirect;
            }

            var customRedirectRow = customRedirectDs.Tables[0].Rows[0];
            return GenerateCustomRedirectFromRow(customRedirectRow);
        }

        public static void Update(CustomRedirect currentCustomRedirect, int id)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            dataAccess.UpdateCustomRedirectToDb(
                 id,
                 currentCustomRedirect.OldUrl,
                 currentCustomRedirect.NewUrl,
                 currentCustomRedirect.State,
                 currentCustomRedirect.NotfoundErrorCount,
                 currentCustomRedirect.WildCardSkipAppend);
        }

        public static void Create(CustomRedirect currentCustomRedirect)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            dataAccess.CreateCustomRedirectToDb(
                currentCustomRedirect.OldUrl,
                currentCustomRedirect.NewUrl,
                currentCustomRedirect.State,
                currentCustomRedirect.NotfoundErrorCount,
                currentCustomRedirect.WildCardSkipAppend);
        }

        public static void Delete(CustomRedirect currentCustomRedirect)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            dataAccess.DeleteCustomRedirectToDb(
                currentCustomRedirect.OldUrl);
        }

        public static List FindAllByState(State state)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            var customRedirectDs = dataAccess.GetCustomRedirectsByState((int)state);

            var customRedirects = new List();

            if (customRedirectDs.Tables[0] == null ||
                customRedirectDs.Tables[0].Rows.Count == 0)
            {
                return customRedirects;
            }

            foreach (DataRow row in customRedirectDs.Tables[0].Rows)
            {
                customRedirects.Add(GenerateCustomRedirectFromRow(row));
            }

            return customRedirects;
        }

        public static List FindAll()
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            var customRedirectDs = dataAccess.GetCustomRedirects();

            var customRedirects = new List();

            if (customRedirectDs.Tables[0] == null ||
                customRedirectDs.Tables[0].Rows.Count == 0)
            {
                return customRedirects;
            }

            foreach (DataRow row in customRedirectDs.Tables[0].Rows)
            {
                customRedirects.Add(GenerateCustomRedirectFromRow(row));
            }

            return customRedirects;
        }

        public static List FindAllByName(string name)
        {
            var dataAccess = DataAccessBaseEx.GetWorker();
            var customRedirectDs = dataAccess.GetCustomRedirectsByName(name);

            var customRedirects = new List();

            if (customRedirectDs.Tables[0] == null ||
                customRedirectDs.Tables[0].Rows.Count == 0)
            {
                return customRedirects;
            }

            foreach (DataRow row in customRedirectDs.Tables[0].Rows)
            {
                customRedirects.Add(GenerateCustomRedirectFromRow(row));
            }

            return customRedirects;
        }

        private static CustomRedirect GenerateCustomRedirectFromRow(DataRow row)
        {
            return new CustomRedirect()
            {
                Id = Convert.ToInt32(row[0].ToString()),
                OldUrl = row[1].ToString(),
                NewUrl = row[2].ToString(),
                State = Convert.ToInt32(row[3].ToString()),
                NotfoundErrorCount = Convert.ToInt32(row[4].ToString()),
                WildCardSkipAppend = Convert.ToInt32(row[5].ToString()) == 1
            };
        }
    }

With all this code, we generated a new nuget package using the command

C:\nuget\nuget.exe pack -Properties version=10.3.12.2;author=Geta;configuration=Release BVNetwork.404Handler.Cms10.nuspec

This will generate the CMS 10 nuget package which can be later added to your site using a local nuget location for testing purposes. After the new plugin is included in your project, we compile it and go to the interface to see if everything is working as expected. We first import the xml file with the 60k rows using the plugin UI interface

2d3

And as soon as it finished you can press the show redirects checkbox to see how long does it take to load the redirects

2d4

And that is all. This helped us to keep the 60k redirects the client needed without sacrificing the user experience of the plugin. One final note, if the plugin does not create the new table, you will have to run the script directly in the database. I hope it will help someone and as always keep learning !!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s