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. 

SugarCRM 101: Named vs. Concurrent Users

It is not uncommon to engage in conversations related to the manner in which SugarCRM licenses function. The topic usually surfaces while attempting to determine the specific license count one must purchase for a commercial implementation of SugarCRM, such as Professional, Corporate or Enterprise Edition. 

A proper user count ensures one is not paying for too many licenses, nor purchasing too few. But determining the proper user count is sometimes a bit tricky, given the various ways in which licenses function from vendor to vendor, or in the case of SugarCRM from one edition of the application to another.

Fortunately, there is nothing complicated about the subject and is easily explained with some simple real world usage examples, which is what we will be examining in this article.

First off, if you are intending on using SugarCRM Community Edition, there is no need to worry about user licenses. Community Edition can be used in conjunction with as many users as your hardware resources will permit. However, if you are considering a commercial edition of SugarCRM, including Professional, Corporate or Enterprise, you do need to take user licenses into consideration.

SugarCRM: SOAP API Gotcha

Technical issues have a funny way of resurfacing sometimes. For example, a number of months ago I came across a problem relating to the SOAP API and Users module and more recently I come across another oddity involving both elements. This time around, however, it seems the problem is broader in scope, applicable to all modules. 

For those of you that work with the SOAP API on a regular basis, take note that there is an issue with the get_entry_list() method. If you are unfamiliar with this method, it is used to retrieve a list of records from a given module such as a list of all contacts whose last name is "Smith." 

In addition to filtering the data retrieved, the method also permits one to control the number of records retrieved. This is helpful because it allows us to limit the amount of bandwidth and calls used to interact with SugarCRM. 

Going back to our example, we could use it to retrieve 20, 50 or 100+ records with one call, by simply specifying any of those numbers as a parameter to the method. This is also where we can run into trouble. As it turns out, there is an issue with the interpretation of this number. In short, if one attempts to retrieve a greater number of records than what exists in the target module, the method fails and none of the records are returned. 

In most cases, it is unlikely one would run into this problem, but if one is working with a module that typically does not contain a large number of records, the chances increase. The Users module exposed it to me because I was attempting to get a list of all users defined for a given instance. Because it is not a large implementation, retrieving 50 records in one call seemed feasible. But the process continually failed, leading me to the realization that the Users module only had approximately 30 records. 

Once I updated my code to retrieve an equal number of records as what existed in the module, all was fine.