Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
Posts: 25
Registered: ‎09-27-2013

SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

Hello,

 

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.

 

Thanks.

 

 

Employee
Posts: 629
Registered: ‎04-24-2009

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

Hi Nathan,

 

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.

 

Thanks

 

Mike

Copper Contributor
Posts: 25
Registered: ‎09-27-2013

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

 

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?


 

Moderator
Posts: 440
Registered: ‎05-07-2008

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

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 Perona

Director, Infor CRM Development

Infor CRM

Infor CRM

Employee
Posts: 629
Registered: ‎04-24-2009

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

Hi Nathan,

 

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

 

Thanks

 

Mike

Copper Contributor
Posts: 25
Registered: ‎09-27-2013

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

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.

 

cheers,

 

Nathan

Highlighted
Employee
Posts: 629
Registered: ‎04-24-2009

Re: SLX 8.1 (WC 03) - Contact and Lead Notes/Histroy Tab - Error.

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.

 

Thanks

 

Mike