SugarCRM: MS-SQL Performance

SugarCRM and Microsoft SQL Server. It is one of those subjects that seems to be surrounded by a lot of mystery -- and misinformation. 

As I mention in my book, Implementing SugarCRM 5.x, there is nothing inherently wrong with using Microsoft SQL Server (MS-SQL) in conjunction with SugarCRM. There are certainly unique issues to be mindful of, but that is not any different than any other software. 

Because the vast majority of the SugarCRM user community utilizes MySQL, instead of MS-SQL, these unique issues tend to have a mysterious aura about them, leading some to believe they cannot be overcome. I would argue most of that perception is the result of lack of exposure to the platform and to some extent, the rivalry between open and closed source software. In my experience, while solutions to some of the issues can be difficult or tedious to implement, there are fixes for the vast majority of the common problems.

Perhaps one of the trickier issues to address in such environments is the issue of performance, or lack thereof. It is worth noting that performance is a tricky subject regardless of which CRM solution we are discussing, so do not take my comment as a knock on SugarCRM. That being said, there is one specific issue that comes up on a regular basis and has a significant impact on performance.

Before we get into the details of the matter, it is important to note that many SugarCRM installations will never encounter the problem. The reason for this requires us to review some SugarCRM development history. 

Support for MS-SQL was originally introduced in version 5.0 of the product. Since then, enhancements to the related code have been coupled with database structure changes, not unlike that which happens on MySQL. Unfortunately, circumstances along the way often impeded the process of applying those database changes when upgrading an instance of SugarCRM. Many of those problems can be traced back to the manner in which SugarCRM code interacts with MS-SQL and have been corrected over time. As a matter of fact, SugarCRM 6.2.4 introduced a number of important changes that fall into that category. 

It is important to note this because these failed database changes tend to be the source of performance problems. This also explains why it is not a universal problem, as a fresh installation of SugarCRM would use a properly structured database from day one. 

So, what is this mysterious problem? 

It is actually not all that mysterious. Upon defining the structure of a database, one must define tables and the type of fields it will contain, e.g. text, numeric, date, etc. Within the world of MS-SQL the two common choices for text fields are VARCHAR and NVARCHAR. The difference between them is that NVARCHAR is intended to be used for fields whose values may include double byte and Unicode characters, i.e. A simple ñ, or more elaborate Kanji characters. 

SugarCRM architects initially selected VARCHAR as their choice for places where text fields were to be used, but over the years, the need to change them to NVARCHAR became more pressing, given they provide better Internationalization support. In response, the code required to apply these changes was added to the upgrade process. However, as referenced earlier, this process was prone to problems and resulted in SugarCRM instances where the database structure did not match what the SugarCRM code was expecting. Herein is the source of our problems. 

To further illustrate our problem, we need to also examine the manner in which data is queried.

To query fields of type NVARCHAR, one typically uses a query similar to the following:

SELECT * FROM contacts WHERE last_name = N'Magaña'

In contrast, querying a VARCHAR field would use a query similar to the following:

SELECT * FROM contacts WHERE last_name = 'Magaña'

The N' notation before the value in the first query is significant. That tells MS-SQL that the query is specifically looking for data in an NVARCHAR field. But what happens if the field we are querying is not defined as NVARCHAR, as would be the case for situations where the upgrade scripts did not properly execute?

In a nutshell: performance would degrade. 

To correct the problem we need to identify the queries causing the degradation and then ensure the text fields said queries reference are changed to NVARCHAR. Problematic queries can be identified by enabling the option to log slow queries, found under Admin > System Settings. 

With the option enabled, proceed to use SugarCRM as you normally would. Periodically pause and examine the sugarcrm.log file and look for any queries that may have been added to it. Take a close look at those queries, noting any field where the query uses the N' notation. Those are the fields which you then need to confirm are indeed configured as NVARCHAR within the database structure. That process is completed via the MS-SQL Management Studio tool, but is beyond the scope of this article.

Once the change is applied to the field, its benefits should become immediately apparent. There is no need to restart the server or the like.

IMPORTANT: I recently stumbled upon some behavior that appears to be a bug. In short, some of the queries I was examining used the N' notation on datetime fields. You can safely ignore those should you happen to stumble upon any.

Lastly, in the interest of full disclosure, you may have noticed me mention elsewhere that I do not recommend the use of MS-SQL for SugarCRM installations. That comment would seem to somewhat contradict at least part of the above article. Allow me to explain the reasons for me not recommending it.

In my experience, I have found that the intricacies of low level technical issues such as the performance problems described herein are often not appreciated until well after someone placing blame on the software, vendor, consultant, etc. for the problems. While it is true that some issues are the results of bugs, it is also true that there are ways to correct them, albeit tedious. This leads to my second point on the reasons for my comments. 

Implementing tedious fixes/changes naturally takes time. Billable time. To make it even clearer: it will cost you money for someone like myself or similar individual to apply these type of changes and/or fine tune your system. SugarCRM support will only offer limited assistance on this topic. Depending on the gravity of the situation, this cost could be significant and many folks only come to this realization once they find themselves experiencing problems. That is an aside to the potential system downtime, which is even more detrimental. 

Of course, that is just a general rule of thumb and a lot of my reasoning really just boils down to making sure clients are made aware of potential issues upfront, as they should be.

If you have some tips you have used, share them in the Comments section below. Thanks in advance.