Tuesday, July 19, 2011

Salesforce Sales Objects Schema in SQL Server

I've been working towards exporting the Salesforce Schema via the Partner API to SQL Server. The current export is starting to look a lot like the Salesforce supplied ERD.

Exported Salesforce Core Sales objects schema in SQL

Note that most of the Status/Stage objects are disconnected when using the Partner API as the data has been denormalized. Also, the Partner object isn't exposed via the Partner API.

Sunday, July 17, 2011

Improving the performance of a Sitecore Item search

Ran into an issue with a third party eCommerce module (ETP) for Sitecore storing tens of thousands of orders as items in the content tree of the master database. As the number of orders increased the inbuilt GetOrder method was getting progressively slower until page requests started timing out.

I traced the delay down to a SelectSingleItem call (using ILSpy to disassemble the code):

// ...
Sitecore.Data.Items.Item ordersItem = database.GetItem(ordersLink);
// ...
string query = string.Format(".//*[@{0}='{1}']", "OrderNumber", orderId);
// typical example query: ".//*[@OrderNumber='4919'] 
Sitecore.Data.Items.Item orderItem = ordersItem.Axes.SelectSingleItem(query);

Running the same query in the Developer Center gives a time of 105 seconds.

My initial attempt to speed this up was to try a Sitecore fast query. This was promising, but still took around 10 seconds.

Solution

The final solution was to create a Lucene Search Index for the Order Template and Order Number field.

In the web.config:

    <indexes>
      <!-- Existing "system" index -->
      <index id="eCommerceOrders" singleInstance="true" type="Sitecore.Data.Indexing.Index, Sitecore.Kernel">
        <param desc="name">$(id)</param>
        <templates hint="list:AddTemplate">
          <!-- Index the Order Template -->
          <template>{2769D69F-E217-4C0A-A41F-2083EC165218}</template>
        </templates>
        <fields hint="raw:AddField">
          <field target="orderNumber">OrderNumber</field>
        </fields>
      </index>
    </indexes>

Add the new index to the master database

        <indexes hint="list:AddIndex">
          <index path="indexes/index[@id='system']"/>
          <index path="indexes/index[@id='eCommerceOrders']"/>
        </indexes>

And then finally override the default OrderProvider to check the Index for the order first before falling back on the base implementation.

   Sitecore.Data.Database database = Sitecore.Configuration.Factory.GetDatabase("master");

   BooleanQuery completeQuery = new BooleanQuery();
   Term term = new Term("orderNumber", orderId);
   completeQuery.Add(new TermQuery(term), BooleanClause.Occur.MUST);

   Index eCommerceOrdersIndex = database.Indexes["eCommerceOrders"];
   if (eCommerceOrdersIndex == null)
   {
    return null;
   }

   IndexSearcher searcher = eCommerceOrdersIndex.GetSearcher(database);

   Hits hits;
   try
   {
    hits = searcher.Search(completeQuery);

    for (int i = 0; i < hits.Length(); i++)
    {
     Item orderItem = Sitecore.Data.Indexing.Index.GetItem(hits.Doc(i), database);
     if (orderItem != null)
     {
      // Existing code to convert Item to Order here...
     }
    }
   }
   finally
   {
    searcher.Close();
   }

See Also:

Wednesday, July 13, 2011

Configuring the Sitecore performance counters

Issue

When running a local Sitecore CMS 6.x install warning messages were being created about missing Counter category's from Sitecore.Diagnostics.PerformanceCounters.PerformanceCounter.

Counter category 'Sitecore.Data' does not exist on this server. Using temporary internal counter for 'Links.FieldsExamined'. 
Counter category 'Sitecore.Data' does not exist on this server. Using temporary internal counter for 'Links.DataRehttp://www.blogger.com/img/blank.gifad'. 
Counter category 'Sitecore.Data' does not exist on this server. Using temporary internal counter for 'Links.DataUpdated'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.http://www.blogger.com/img/blank.gifFullPublishings'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.IncrementalPublishings'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.ItemPublishings'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.ItemsExamined'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.ItemsPublished'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.ItemsQueued'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.Replacements'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.SmartPublishings'. 
Counter category 'Sitecore.Jobs' does not exist on this server. Using temporary internal counter for 'Publishing.TotalPublishings'. 

Solution

The Sitecore-specific performance counters need to be installed. There is an installer at http://sdn.sitecore.net/upload/sdn5/faq/administration/sitecorecounters.zip. On Windows 2008 R2 I needed to run this as an administrator.

After configuring the performance counters they can be selected in PerfMon

See Also:

Wednesday, July 6, 2011

Creating a SQL Table using FileStream via SMO

To create a SQL Server 2008 Table with a varbinary(max) column using a FileStream the article FILESTREAM Storage in SQL Server 2008 gives the following example:

USE Production;
GO
CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO

Replicating this from C# using SMO is reasonably straight forward except for the UNIQUE constraint which doesn't appear to be available as an SMO column property. To get round this a index can be created to achieve the same result.

Table documentStoreTable = new Table(db, "DocumentStore");

documentStoreTable.FileStreamFileGroup = "FileStreamGroup1";
// A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.
// DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID ())
Column uniqueRowGuidColumn = new Column(documentStoreTable, "DocGUID", DataType.UniqueIdentifier);
uniqueRowGuidColumn.Nullable = false;
uniqueRowGuidColumn.RowGuidCol = true;

DefaultConstraint dc = uniqueRowGuidColumn.AddDefaultConstraint("DF_DocumentStore_" + uniqueRowGuidColumn.Name);
dc.Text = "NEWID()";

documentStoreTable.Columns.Add(uniqueRowGuidColumn);

Index id = new Index(documentStoreTable, "unique" + uniqueRowGuidColumn.Name);
id.IndexKeyType = IndexKeyType.DriUniqueKey;
id.IndexedColumns.Add(new IndexedColumn(id, uniqueRowGuidColumn.Name));
documentStoreTable.Indexes.Add(id);

Monday, July 4, 2011

Sitecore for Salesforce Membership Provider IsApproved and IsLockedOut support

Sitecore for Salesforce (S4S) versions after 1.5.2011.912 add support for the following MembershipUser properties:

To use these properties the corresponding fields should be created in Salesforce for Contacts and configured against the Membership provider in the web.config.

<add name="salesforce" type="FuseIT.Sitecore.SalesforceSecurityProvider.SalesforceMembershipProvider, FuseIT.Sitecore.SalesforceSecurityProvider" 
             readOnly="false" connectionStringName="S4SConnString" applicationName="sitecore" 
             minRequiredPasswordLength="1" minRequiredNonalphanumericCharacters="0" 
             passwordStrengthRegularExpression=".*" requiresUniqueEmail="false" 
             passwordFieldName="SitecorePassword__c" userNameFieldName="SitecoreUsername__c" isEncryptedFieldName="SitecorePasswordEncrypted__c"
             
             isApprovedFieldName="SitecoreIsApproved__c" 
             isLockedOutFieldName="SitecoreIsLockedOut__c"
             
             passwordAttemptWindow="5"
             
             lastLoginDateFieldName="SitecoreLastLoginDate__c"
             lastLockoutDateFieldName ="SitecoreLastLockoutDate__c"
        failedPasswordAttemptCountFieldName = "SitecoreFailedPasswordAttemptCount__c"
             failedPasswordAttemptWindowStartFieldName = "SitecoreFailedPasswordAttemptWindowStart__c"
             />

Once configured the Sitecore User Manager can be used to Disable/Enable a Salesforce Contact user or Unlock them.