Today's blog post was written by Argyris Anargyros, Development Principal at Sonoma Partners.
While working on a 2016 org upgrade from 2015, I was tasked with looking into a few performance issues that affected specific parts of the system, such as Connections and Activities. For Connections, the lookup to select the Connect To field took a very long time to load and would sometimes return a SQL time out error. For Activities, we were seeing long waits for quick find search results. While looking into these issues, we used both CRM and SQL Tracing, and we were able to identify a few problems that were easily fixed through native changes. These changes produced a dramatic improvement to the specific issue and the system as a whole. Some of these might seem obvious, but these can be good things to look for when you first come to an existing implementation.
Connections and Quick Find Views
First thing we noticed was that there were a bunch of Quick Find Query requests to pull in each entity listed as available to Connections. Microsoft does not provide a way to uncheck this availability, but we were able to identify a few unneeded custom entities, delete those entities, recreate them, and avoid the unneeded call to request for that data. The next piece we tackled was some of these Quick Find queries were taking a long time. After reviewing the Quick Find views, we found a lot of fields included as a part of the Quick Find. Each field that is selected can poetically create an index which should help searching, but what we found was that there were many fields of the same type being search on, like Owner, Regarding, and Modified By. This combination of fields to search on created a SQL statement that union 3 select statements to consolidate the results from the entity we were searching on, the users entity to cover the Modified By, Contact, and Accounts for the Regarding field. This union was not needed since we really did not need to have Modify By and Owner set to searchable by the Quick Find view. Once we removed all the unnecessary Quick Find fields, we had to wait a day or two for the indexes to clean themselves up, but we saw these long running queries go away.
Permissions and Business Units
Next we found SQL statements that were joining to the POA table and looking at Business Units to identify whether the user querying this data has permission to the individual records. Once I saw this I found it odd since the client I was working with has a single Business Unit. After reviewing there custom roles, we found that a lot of their permissions were set to BU instead of Org level, but with the single BU they are in essence giving folks Org level access. Once we changed all the permissions from Business Unit to Org access, the union to the POA and BU tables were eliminated and the response time of those queries became very small.