Sitecore Transfer items from one database to another

In this article I will describe how to transfer items from one Sitecore database to another. Usually you transfer content by publishing. But in rare cases this is not an option:

  • You are not moving content from master to web
  • You are not moving content to a publishing target.
  • You are moving content to another path in another database.
  • You would like to avoid raising any events that would clear cache.

Before you start transferring content, you need to know the following:

  • Transfer does not retain the path structure, because a transfer is like a copy, just between databases. If you would like to retain the path, you must do it yourself.
  • The templates of the transferred items must exist in the target database. If not, Sitecore will raise an TemplateNotFoundException.
  • Transferring is done by copying the OuterXML of an item into a string and pasting this string to another database. This could be a very CPU and memory heavy process if you choose to copy the root of a large website.

Enough talk. Here is the code:

using Sitecore.Configuration;
using Sitecore.Data;
using Sitecore.Data.Items;
using Sitecore.Data.Proxies;
using Sitecore.Diagnostics;
using Sitecore.Exceptions;

namespace MyNameSpace
{
  public class ArchiveRepository
  {
    public void Put(Item source, Item destination, bool deep)
    {
      using (new ProxyDisabler())
      {
        ItemSerializerOptions defaultOptions = ItemSerializerOptions.GetDefaultOptions();
        defaultOptions.AllowDefaultValues = false;
        defaultOptions.AllowStandardValues = false;
        defaultOptions.ProcessChildren = deep;
        string outerXml = source.GetOuterXml(defaultOptions);
        try
        {
          destination.Paste(outerXml, false, PasteMode.Overwrite);
          Log.Audit(this, "Transfer from {0} to {1}. Deep: {2}", new[]{ AuditFormatter.FormatItem(source), AuditFormatter.FormatItem(destination), deep.ToString() });
        }
        catch (TemplateNotFoundException)
        {
          // Handle the template not found exception
        }
      }
    }

  }
}

Parameter source is the item to copy, parameter destination is the item in the destination database to copy to. Parameter deep determines if you transfer one item of the whole tree.

I created a few more methods to allow for transferring an item from one database to another whilst retaining the path structure. By adding the destination database as private property I hard-code the repository to a specific destination database.

The method Put(item) takes a source item, creates the item path if not found, then transfer the item:

    private readonly Database _database = Factory.GetDatabase("DestinationDatabase");

    public void Put(Item item)
    {
      EnsurePath(item);
      Put(item, true);
    }

    private void Put(Item item, bool deep)
    {
      Item destination = _database.GetItem(item.Parent.ID);
      Put(item, destination, deep);
    }

    private void EnsurePath(Item item)
    {
      foreach (Item ancestor in item.Axes.GetAncestors())
      {
        if (_database.GetItem(ancestor.ID) == null)
          Put(ancestor, false);
      }
    }

MORE TO READ:

Posted in c#, General .NET, Sitecore 6, Sitecore 7, Sitecore 8 | Tagged , , , | 1 Comment

All my Sitecore items are called __Standard Values – The fix

In the previous post i described how a tiny error in Sitecore made you believe that all of the Sitecore items are called __Standard Values.

The error occurs in the Danish language because Sitecore unintentionally added the word “__Standard Values” to the Display Name of several items.

A friend of mine asked for a fix. Here it is. You should:

  • Create a new .aspx page in the /sitecore modules/shell folder
  • Ensure that the website is running in the Danish (da) language
  • Copy the following contents to the .aspx page you created.
  • Call the page.

The code iterates through all Sitecore templates, identifies the __Standard Values templates where the display name is “__Standard Values” and deletes the contents in the Display Name field.

<%@ Page language="c#" EnableEventValidation="false" AutoEventWireup="true" %>

<script runat="server">
  
  void Page_Load(object sender, System.EventArgs e) 
  {
    Response.Buffer = false;
    Response.BufferOutput = false;
    Response.Write(string.Format("{0} {1}<br/>", Sitecore.Context.ContentDatabase.Name, Sitecore.Context.Language));
    Sitecore.Data.Items.Item templateRoot = Sitecore.Context.ContentDatabase.GetItem("/sitecore/templates");
    Iterate(templateRoot);
  }

  void Iterate(Sitecore.Data.Items.Item root)
  {
    foreach (Sitecore.Data.Items.Item child in root.GetChildren())
    {
      if (child.Name == "__Standard Values" && child["__display name"] == "__Standard Values")
      {
        Response.Write(string.Format("path: {0}. Name: {1}. Display Name: {2}<br/>", child.Paths.FullPath, child.Name, child["__display name"]));
        FixDisplayName(child);
      }
      if (child.HasChildren)
        Iterate(child);
    }
  }

  void FixDisplayName(Sitecore.Data.Items.Item item)
  {
    item.Editing.BeginEdit();
    item.Fields["__display name"].Value = string.Empty;
    item.Editing.EndEdit();
  }

</script>  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
  <head>
    <title>www.sitecore.net</title>
    <meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
    <meta content="C#" name="CODE_LANGUAGE">
    <meta content="JavaScript" name="vs_defaultClientScript">
    <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
    <link href="/default.css" rel="stylesheet">
  </head>
  <body>
    <form runat="server">
    </form>
  </body>
</html>

As always, use at own risk, take a backup before running etc.

Posted in c#, General .NET, Sitecore 7, Sitecore 8 | Tagged , | 1 Comment

All my Sitecore items are called __Standard Values

After I upgraded to Sitecore 7.5, all of my Sitecore items are called “__Standard Values“. This happens in the “Danish” language, not the “English”:

All items are called __Standard Values

All items are called __Standard Values

Don’t worry, your items have not been renamed. Sitecore have just made a tiny mistake, and added the phrase “__Standard Values” to the “Display name” of all __Standard Values:

Display Name is wrong

Display Name is wrong

  • Find the template
  • Find the __Standard Values of the template
  • Click View|Standard Fields
  • Find the field “Display name”
  • Delete the text “__Standard Values” in the field
  • Repeat for all __Standard Values that contains a text in the “Display name” field.

UPDATE:

I made an .aspx page that fixes the issue. Get the contents here.

Posted in Sitecore 7, Sitecore 8 | Tagged , , | 2 Comments

Cannot cache phrase for invariant language – Sitecore 8

When adding dictionary items to the CORE database in Sitecore 8, this error can occur:

Message: Cannot cache phrase for invariant language.
Source: Sitecore.Kernel
at Sitecore.Globalization.Translate.CachePhrase(String key, String phrase, Language language, DictionaryDomain domain)
at Sitecore.Globalization.ItemEventHandler.OnItemSaved(String dictionaryKey, Item item, Boolean reloadDomainCache, Boolean saveToDisk)
at … … … …

The error is related to a cached dictionary file. Sitecore caches all dictionary entries in a dictionary.dat file located in the /temp/ folder.

To solve the issue you should:

  • Delete the /temp/dictionary.dat file
  • Recycle the website

That’s it. Problem solved. Thanks to Sitecore Support for the solution.

Posted in Sitecore 8 | Tagged , | Leave a comment

Sitecore 8 and Engagement Plans

With the introduction of the xDB in Sitecore 7.5, Sitecore also changed the analytics API. The VisitorManager have been replaced by the Tracker.

The Tracker is one of the base API’s for Sitecore the new Sitecore Experience Platform (the new name for Sitecore DMS, which was the new name for Sitecore OMS – do you follow me?) and the API handles the tracking of users in Sitecore.

For automatic engagement plan handling, this means that instead of using the VisitorManager to enroll users in your engagement plan, you use the Tracker and the AutomationStateManager:

using System.Linq;
using Sitecore.Analytics;
using Sitecore.Analytics.Automation.Data;
using Sitecore.Analytics.Automation.MarketingAutomation;
using Sitecore.Data.Items;

public void AddUserToEngagementPlan(string user, Item engagementPlan)
{
  Tracker.Current.Session.Identify(user);
  AutomationStateManager manager = Tracker.Current.Session.CreateAutomationStateManager();
  manager.EnrollInEngagementPlan(engagementPlan.ID, engagementPlan.Children.First().ID);
}

Parameter user is the complete username with domain (for example extranet\bp). The engagementPlan is the engagementPlan item.

The remove a user from an engagement plan you simply call RemoveFromEngagementPlan:

public void RemoveUserFromEngagementPlan(string user, Item engagementPlan)
{
  Tracker.Current.Session.Identify(user);
  AutomationStateManager manager = Tracker.Current.Session.CreateAutomationStateManager();
  manager.RemoveFromEngagementPlan(engagementPlan.ID);
}

Thanks to Alin Parjolea for the code.

MORE TO READ:

Posted in c#, General .NET, Sitecore, Sitecore 8 | Tagged , , , , , | Leave a comment

Sitecore Job Viewer – see what Sitecore is doing in the background

This simple .aspx page has become one of my most used tools when working with Sitecore. It’s a job viewer, displaying which jobs are running and which jobs are finished:

Job Viewer

Sitecore Job Viewer

A Sitecore job is a thread running in the background. Jobs can run in parallel or in sequence: Jobs that share the same name are queued and executed in the order they arrived.

Getting a list of all jobs are pretty simple. This returns the jobs sorted by the time they were added to the queue:

public IEnumerable<Sitecore.Jobs.Job> Jobs
{
  get
  {
    return Sitecore.Jobs.JobManager.GetJobs().OrderBy(job => job.QueueTime);
  }
}

For each job you can extract all information about the job you need. My application lists the most used data, and when you hover the mouse over one line, it displays more info, including who started the job, and all messages added to the job:

Job Details

Job Details

All of this in a simple .aspx page that can be uploaded to the /sitecore modules/shell/ folder on an ad-hoc basis to see what the heck Sitecore is doing.

The code is simple. Copy it from here, paste it into a jobs.aspx page and upload the thing to your Sitecore.

<%@ Page language="c#" EnableEventValidation="false" AutoEventWireup="true" %>

<script runat="server">

  void Page_Load(object sender, System.EventArgs e)
  {
    repJobs.DataBind();
  }

  public IEnumerable<Sitecore.Jobs.Job> Jobs
  {
    get
    {
      if (!cbShowFinished.Checked)
        return Sitecore.Jobs.JobManager.GetJobs().Where(job => job.IsDone == false).OrderBy(job => job.QueueTime);
      return Sitecore.Jobs.JobManager.GetJobs().OrderBy(job => job.QueueTime);
    }
  }

  protected string GetJobText(Sitecore.Jobs.Job job)
  {
    return string.Format("{0}\n\n{1}\n\n{2}", job.Name, job.Category, GetJobMessages(job));
  }

  protected string GetJobMessages(Sitecore.Jobs.Job job)
  {
    System.Text.StringBuilder sb = new StringBuilder();
    if (job.Options.ContextUser != null)
      sb.AppendLine("Context User: " + job.Options.ContextUser.Name);
    sb.AppendLine("Priority: " + job.Options.Priority.ToString());
    sb.AppendLine("Messages:");
    foreach (string s in job.Status.Messages)
      sb.AppendLine(s);
    return sb.ToString();
  }

  protected string GetJobColor(Sitecore.Jobs.Job job)
  {
    if (job.IsDone)
      return "#737373";
    return "#000";
  }

  protected void cbShowFinished_CheckedChanged(object sender, EventArgs e)
  {
    repJobs.DataBind();
  }
</script>  

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
  <head>
    <title>Job Viewer</title>
    <link href="/default.css" rel="stylesheet">
  </head>
  <body style="font-size:14px">
    <form runat="server">

      <div style="padding:10px; background-color:#efefef; border-bottom:solid 1px #aaa; border-top:solid 1px white">
        <div style="float:left; width:200px; padding-top:4px">
          <asp:CheckBox ID="cbShowFinished" runat="server" Text="Show finished jobs" Checked="false" OnCheckedChanged="cbShowFinished_CheckedChanged" AutoPostBack="true" />
        </div>
        <div style="float:right;">
          <asp:Button ID="btnRefresh" runat="server" Text="Refresh" BackColor="Green" ForeColor="White" Width="100px" Height="30px" />
        </div>
        <div style="clear:both;height:1px">&nbsp;</div>
      </div>

      <div style="padding-top:0px">
        <asp:Repeater ID="repJobs" runat="server" DataSource="<%# Jobs %>">
          <HeaderTemplate>
            <table style="width:100%">
              <thead style="background-color:#eaeaea">
                <td>Job</td>
                <td>Category</td>
                <td>Status</td>
                <td>Processed</td>
                <td>QueueTime</td>
              </thead>
          </HeaderTemplate>
          <FooterTemplate>
            </table>
          </FooterTemplate>
          <ItemTemplate>
            <tr style="background-color:beige; color:<%# GetJobColor((Container.DataItem as Sitecore.Jobs.Job)) %>" title="<%# GetJobText((Container.DataItem as Sitecore.Jobs.Job)) %>">
              <td>
                <%# Sitecore.StringUtil.Clip((Container.DataItem as Sitecore.Jobs.Job).Name, 50, true) %>
              </td>
              <td>
                <%# Sitecore.StringUtil.Clip((Container.DataItem as Sitecore.Jobs.Job).Category, 50, true) %>
              </td>
              <td>
                <%# (Container.DataItem as Sitecore.Jobs.Job).Status.State %>
              </td>
              <td>
                <%# (Container.DataItem as Sitecore.Jobs.Job).Status.Processed %> /
                <%# (Container.DataItem as Sitecore.Jobs.Job).Status.Total %>
              </td>
              <td>
                <%# (Container.DataItem as Sitecore.Jobs.Job).QueueTime.ToLocalTime() %>
              </td>
            </tr>
          </ItemTemplate>
        </asp:Repeater>
      </div>

    </form>
  </body>
</html>

MORE TO READ:

Posted in c#, Sitecore 5, Sitecore 6, Sitecore 7, Sitecore 8 | Tagged , , , | 4 Comments

Sitecore EventQueue deadlocks – how to solve them and how to avoid them

The Sitecore 7.0+ EventQueue mechanism can in some rare occurrences cause a deadlock to occur.

You will notice that when it happens, your Sitecore Shell will come to a complete halt, the DMS becomes unresponsive, and you experience SQL timeout exceptions. Sitecore resumes when the deadlock is solved.

HOW TO SOLVE THEM

You will need SQL Server admin access to solve them. Open SQL Management Studio, go to the CORE database and run the following:

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

When the script has run, you will get some output like this:

Deadlocks in EventQueue

Deadlocks in EventQueue

The blocking_session_id is the session id that causes the deadlock. You need to kill the blocking session. Run the kill command in the SQL Management Studio:

kill 205;
kill 62;

Voila, the deadlock is gone.

WHO CAUSED THE DEADLOCK?

Well, lets see. Run the following SQL statement:

SELECT
spid,
status,
loginame=SUBSTRING(loginame,1,40),
hostname=SUBSTRING(hostname,1, 40),
blk = CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid),1, 40),
cmd,
waittype

FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

The output reveals the sinner:

Who caused the deadlock?

Who caused the deadlock?

Oops, looks like I am causing the deadlock myself.

HOW CAN I AVOID DEADLOCKS?

Deadlocks have a tendency to occur in multi-server environments (obviously, since the EventQueue is used to communicate between servers). They also have a tendency to occur in multi-developer enenvironmentswhere several developers use the same SQL server.

1) Check the EventQueue size

Deadlocks occur if your EventQueue is too large. Make sure you clean the EventQueue often and keep as little data as possible, preferably below 1000 items. The CleanupEventQueue task will help you:

<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
    <DaysToKeep>1</DaysToKeep>
</agent>

2) Let your CM server do the publishing

Most of the EventQueue contents is publish information. If possible, your architecture should allow on the CM server to publish. The CD servers should not.

3) Have enough power in the server rack

Deadlocks occur more often on heavily loaded environments, because the insert and select statements will be slower. Make sure you do not constantly run out of CPU or memory on your web and SQL servers.

MORE TO READ:

Posted in Sitecore 7 | Tagged , , | Leave a comment

Measuring Sitecore performance with HighResTimer

The HighResTimer is a Sitecore feature that has been around since Sitecore 4. It’s a simple timer that allows you to very precisely measure time in microseconds by using the underlying OS timers.

It is very easy to use:

public void MyMethod()
{
  // Initialize and start the timer:
  Sitecore.Diagnostics.HighResTimer ht = new Sitecore.Diagnostics.HighResTimer();
  ht.Start();
  try
  {
    // execute the code to be timed.
  }
  finally
  {
    // Dump the elapsed timespan to the log:
    Sitecore.Diagnostics.Log.Info("Time elapsed: " + ht.ElapsedTimeSpan.ToString(), this);
  }
}

The HighResTimer allows you to read the number of milliseconds or as I do in this example, read the time elapsed with a precision of 7 digits. The log line from the above example looks like this:

10112 12:25:28 INFO  Time elapsed: 00:00:00.0073862

More to read:

Posted in General .NET, Sitecore 4, Sitecore 5, Sitecore 6, Sitecore 7, Sitecore 8 | Tagged , , | Leave a comment

Sitecore and xDB – Setting up MongoDB on your developer machine

With Sitecore 7.5, Sitecore introduces xDB – the new MongoDB based DMS database. But before you say “oh no, yet another technology I have to deal with”, you should know that working with MongoDB is very easy. In fact it is easier than working with SQL Server.

STEP 1: DOWNLOAD MONGODB

Go to http://www.mongodb.org/downloads and download the 64 bit .zip file. Unzip the files in a directory on your computer.
I installed mine at c:\mongodb

STEP 2: SET UP THE SITECORE CONNECTIONSTRINGS.CONFIG

It is very likely that the Sitecore 7.5 (and Sitecore 8.x) connectionstrings.config are already set up correctly. If not look for the following lines, they need to point to your local machine:

<?xml version="1.0" encoding="utf-8"?>
<connectionStrings>
  <!--
    Sitecore connection strings.
    All database connections for Sitecore are configured here.
  -->
  ...
  ...
  ...
  <add name="analytics" connectionString="mongodb://localhost/analytics" />
  <add name="tracking.live" connectionString="mongodb://localhost/tracking_live" />
  <add name="tracking.history" connectionString="mongodb://localhost/tracking_history" />
  ...
</connectionStrings>

STEP 3: CREATE A .BAT FILE TO START UP MONGO

To start up mongo you need to run mongod.exe with a parameter determining where Mongo should put the database files. Create a .bat file with the following contents:

c:\MongoDB\bin\mongod.exe –dbpath “[yourfilepath]”

Replace c:\MongoDB\bin\ with the path to your Mongo files, and [yourfilepath] with the path to the folder where you would like to put your MongoDB databases.

You will need one .bat file for each of your Sitecore projects, as you would like to store a separate set of files per Sitecore.

STEP 4: START SITECORE AND ENJOY LIFE

To start up Sitecore, you run the .bat file and fire up your Sitecore and you are now running Sitecore with xDB on a local instance.

Sitecore 8 technical preview with MongoDB running

Sitecore 8 technical preview with MongoDB running

MORE TO READ:

 

 

Posted in Sitecore 8 | Tagged , , , | 10 Comments

Improve Sitecore Membership provider performance 2-20 times

The Sitecore Membership provider is built on top of the default .NET membership provider. The .NET membership provider is not known to be the fastest provider available. Sure, if you have a few thousand users in your database it performs well, but if you have, say, 50.000 or even 500.000 users, the provider becomes painfully slow.

500.000 users in Sitecore

500.000 users in Sitecore

But before you start rewriting the whole thing, there is a couple of things you can do to dramatically improve performance.

I would like to thank Ulrich Kronvold, Kristian Magius, Sergey Marchenko, Ivan Sheyenko and everyone that have been involved in getting these SQL scripts. None of the scripts are my own, but I think they are too good to keep for myself.

MODIFY STORED PROCEDURE [dbo].[aspnet_Membership_GetAllUsers] 

This script modifies the [dbo].[aspnet_Membership_GetAllUsers] stored procedure to select values based on indexed fields:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN

    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    /* DSZ
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName
    */

    SELECT @TotalRecords = COUNT(u.UserId)
    FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
    WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId

    SELECT
                u.UserName,
                m.Email,
                m.PasswordQuestion,
                m.Comment,
                m.IsApproved,
                m.CreateDate,
                m.LastLoginDate,
                u.LastActivityDate,
                m.LastPasswordChangedDate,
                u.UserId,
                m.IsLockedOut,
                m.LastLockoutDate
    FROM
                (SELECT
                        ROW_NUMBER() OVER (ORDER BY u.UserName) AS IndexId,
                        u.UserName
                FROM dbo.aspnet_Membership m (NOLOCK), dbo.aspnet_Users u (NOLOCK)
                        WHERE u.UserId = m.UserId AND u.ApplicationId = @ApplicationId AND m.ApplicationId = @ApplicationId)
                AS p INNER JOIN dbo.aspnet_Users u ON u.UserName = p.UserName INNER JOIN dbo.aspnet_Membership m ON m.UserId = u.UserId
        WHERE
                IndexId &gt;= @PageLowerBound AND IndexId &lt;= @PageUpperBound
    --ORDER BY UserName
    RETURN @TotalRecords
END

MODIFY STORED PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]

This script modifies the stored procedure [dbo].[aspnet_Membership_GetUserByEmail] to select fields based on indexed values:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetUserByEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail AND u.ApplicationId = m.ApplicationId

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END
GO

CREATE NEW INDEX [aspnet_Membership_index]

This script creates a clustered index on fields ApplicationID, LoweredEmail and UserID used by the 2 stored procedures above:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]') AND name = N'aspnet_Membership_index')
DROP INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] WITH ( ONLINE = OFF )
GO

CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] 
(
	[ApplicationId] ASC,
	[LoweredEmail] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NEW INDEX [aspnet_Users_Index]

This script creates a new clustered index on fields ApplicationID, LoweredUserName and UserID used by the 2 stored procedures above:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Users]') AND name = N'aspnet_Users_Index')
DROP INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users] 
(
	[ApplicationId] ASC,
	[LoweredUserName] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

With the changes above I managed to get at least twice the performance. Before the changes, the user manager took about 20 seconds to go to next page. After the change, it took only 2 seconds AND I could start using the search field again.

In general the performance gain was 2-5 times. The scripts does not seem to impact the stability of Sitecore, but use them with caution, as they are not official, not supported by me or Sitecore.

EDIT:

These scripts failed in an installation where I used the “switching” provider as my membership provider. Once I reverted to the default “sitecore” provider, the scripts worked. So be careful if you are using a switching provider, and remember to backup the database, or at least the original indexes and stored procedures.

Posted in Sitecore, Sitecore 6, Sitecore 7 | Tagged , , , | 3 Comments