10-20-2014 02:58 AM
Running through some tests on our UAT 8.1 System our users logged an issue whereby the Notes/History tab of the Contacts (and Leads) entity is at times showing an error 'Error Loading results' and at other times take upwards of a 2 minutes to return something like 1 or 2 results.
Using SLXProfiler and taking a look at the NotesHistoryList.js file, I found that a condition is being added to the SQL Query when the Notes/History Tab is being viewed in the Contact or Lead entity. This condition joins on the HistoryAttendees table. Sure enough, when profiling the query that is run it seems to be this join that is causing the bottle neck:
SELECT count(*) as y0_ FROM sysdba.HISTORY this_ left outer join sysdba.HISTORYATTENDEE ha1_ on this_.HISTORYID=ha1_.HISTORYID WHERE ( (((ha1_.ENTITYID = 'C6UJ9A0009YH' or (this_.CONTACTID = 'C6UJ9A0009YH' and ha1_.ISPRIMARY = 'T')) or (this_.CONTACTID = 'C6UJ9A0009YH' and ha1_.ENTITYID is null)) and this_.TYPE <> '262156') ) AND (THIS_.USERID = 'U6UJ9A0000FX' OR THIS_.COMPLETEDUSER = 'U6UJ9A0000FX' OR (THIS_.OPPORTUNITYID IN (SELECT O.OPPORTUNITYID FROM sysdba.OPPORTUNITY O INNER JOIN sysdba.SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000FX' AND O.SECCODEID = S_AA.SECCODEID)) OR THIS_.CONTACTID IN (SELECT C.CONTACTID FROM sysdba.CONTACT C INNER JOIN sysdba.SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000FX' AND C.SECCODEID = S_AA.SECCODEID)) OR THIS_.ACCOUNTID IN (SELECT A.ACCOUNTID FROM sysdba.ACCOUNT A INNER JOIN sysdba.SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000FX' AND A.SECCODEID = S_AA.SECCODEID)) OR THIS_.LEADID IN (SELECT L.LEADID FROM sysdba.LEAD L INNER JOIN sysdba.SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000FX' AND L.SECCODEID = S_AA.SECCODEID)) OR THIS_.TICKETID IN (SELECT T.TICKETID FROM sysdba.TICKET T INNER JOIN sysdba.SECRIGHTS S_AA ON (S_AA.ACCESSID = 'U6UJ9A0000FX' AND T.SECCODEID = S_AA.SECCODEID)) ))
In SLXProfiler this query took 84786(ms) to run! That's somewhere in the region os 100 x slower than the equivalent query when viewing the Accounts Notes/History tab.
If I run the query in SQL Management Studio it took around 90 seconds to run. Removing any reference to the HistoryAttendees table reduces this query to around 1 second in SQL. Specifically if i remove the:
ha1_.ENTITYID = 'C6UJ9A0009YH'
condition, the query time returns back to something sensible.
We've only got a handful of users testing this at the moment and in in a live setting we will have 10-15x more.
We're quite worried that this behaviour will bring the system to a complete halt - so is there anything we can do, something we may be doing wrong or is this a bug we'll have to wait to be fixed?
Non-Clustered indexes exist on both tables already by the way.
10-23-2014 02:25 PM
If you're using the OOTB indexes for HISTORYATTENDEE you may benefit by changing the primary key from non-clustered to clustered. This should eliminate the table scan on the HISTORYATTENDEE table (which is the likely culprit). This will change it to a clustered index scan...not as good as an index seek but better than a table scan. You should try this on a non-production box first.
10-24-2014 03:36 AM
Thanks Mike, I really appreciate the help with this issue.
I gave this a shot and it does indeed remove the Table Scan. Whilst the Query Execution Plan is 20x faster, the actual query still takes around 90-100 seconds to run.
Users also reported no improvement in testing.
We have around 450k rows in our History table at this point.
Short of manually disabling the Particpants/Attendees functionality through code (i.e. removing the contextual condition of the grid for Leads/Contacts in NotesHistoryList.js), I'm not quite sure what else we can do. I'm not exagerating when I say this is one of the tabs we use the most.
Would this problem be considered a bug?
10-24-2014 07:49 AM
We are currently evaluating a change to this view that would allow you to only see the history on the primary contact. We have a bundle that can apply that if you call into support.
John PeronaDirector, Infor CRM Development
10-24-2014 08:28 AM
Plesae also try creating the following index:
/* The Query Processor estimates that implementing the following index could improve the query cost by 88.5806%. */ USE [Saleslogix] GO CREATE NONCLUSTERED INDEX [HistoryListFix] ON [sysdba].[HISTORY] ([TYPE]) INCLUDE ([HISTORYID],[ACCOUNTID],[CONTACTID],[OPPORTUNITYID],[USERID],[COMPLETEDUSER],[TICKETID],[LEADID]) GO
10-24-2014 08:54 AM
Thanks John & Mike!
I addded the index and got a 60 sec improvement on the above query, which is a huge improvement. However, I'm pretty sure the users will still have issues over this timing given the amount of time this tab is used, so I will give support a call and see what difference the bundle can make.
10-24-2014 10:19 AM
Glad to help!
If, after installing the bundle, you decide you like the way the data was presented before, I would recommend examining the execution plan and the query in the Database Engine Tuning Advisor. You should also check to see if each table involved has the primary key defined as a clustered index and that your database statistics are up to date (rebuilding an index will force statistics to update). Statistics are used to create optimal execution plans, and can make more of a difference, in terms of performance, than indexes by themselves.