Navigation Lists Very Slow

(0) Share

Report

Posted on by Joe Powers

985

We recently upgraded from 2015R2 to 2018R2. I created and activated a simple Sales Order workflow to handle credit limit exceeded situations. We now notice that Sales Order Transaction Navigation Lists have become EXTREMLY slow to load, on the order of 20-60 seconds for a simple list of open orders in the last 60 days (The corresponding Smartlist opens in less than a second). Additionally, I tried to add a column to the Navigation List for Workflow status but the column comes up with no data in it (Workflow Submitter or Approver columns work fine) . This was also observed in GP2015R2 also. I think it is trying to reference data from the old Workflow 1.0 tables ?? Can anyone verify these issues??

*This post is locked for comments

All responses (6)

Answers (0)

Burley 18 on at

Like (0)

Report

RE: Navigation Lists Very Slow

Has anyone figured out the cause of this or a fix? I'm noticing the same thing with the Purchase Order navigation list when Workflow for requisitions is used.
gdetrich 5 on at

Like (0)

Report

RE: Navigation Lists Very Slow

Hi Joe -

I am experiencing similar issues to what you describe above. Did you ever determine the specific cause and a fix?

Thanks much!

Geoff
Joe Powers 985 on at

Like (1)

Report

RE: Navigation Lists Very Slow

There are only 4200 records in WFI10002. This problem did not start until upgrading to 2018R2, so something happened there. I just applied the latest Hotfix update but that did not help. I have read thru both of the supplied links prior to today and did not see anything that applies. The server FAR exceeds the system requirements and as I said these lists had no problems prior to the upgrade. The lists are slow when running directly on the server also. Here is the stored procedure you asked about:

USE [KHGP]

GO

/****** Object: StoredProcedure [dbo].[wfGetNavListFields] Script Date: 3/6/2019 10:07:05 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[wfGetNavListFields] @WorkflowInstanceID char(37), @Workflow_User char(85), @Workflow_Type_Name char(51), @WfBusObjKey char(201), @Workflow_Name char(51) OUTPUT, @WorkflowStepInstanceID char(37) OUTPUT, @Workflow_Step_Type smallint OUTPUT, @Workflow_Due_Date datetime OUTPUT, @Workflow_Due_Time datetime OUTPUT, @Workflow_Approver char(85) OUTPUT, @Workflow_Originator char(85) OUTPUT, @Workflow_Error int OUTPUT AS BEGIN SET NOCOUNT ON declare @number_of_assignees int declare @is_user_assigned_task tinyint declare @WorkflowTaskAssignedTo char(85) declare @Workflow_Step_Name char(51) declare @Workflow_Status smallint select @WorkflowInstanceID=UPPER(@WorkflowInstanceID) select @Workflow_User=UPPER(@Workflow_User) select @Workflow_Status=Workflow_Status, @Workflow_Name=Workflow_Name, @Workflow_Originator=Workflow_Originator from WFI10002 where UPPER(WorkflowInstanceID)=@WorkflowInstanceID select @Workflow_Error=0 create table #doc_steps_table (Workflow_User char(85), WorkflowInstanceID char(37), WorkflowStepInstanceID char(37), Workflow_Step_Type smallint, Workflow_Name char(51), Workflow_Step_Name char(51), WF_Step_Predecessor char(51), Workflow_Step_Sequence int, Workflow_Task_Assigned tinyint, Number_of_Assignees int, Workflow_Due_Date datetime, Workflow_Due_Time datetime, WorkflowTaskAssignedTo char(85), Workflow_Type_Name char(51), Workflow_Originator char(85), WfBusObjKey char(201)) if @Workflow_Status in (5,6,7) begin select @Workflow_Name=isnull(@Workflow_Name,''), @WorkflowStepInstanceID=isnull(@WorkflowStepInstanceID,''), @Workflow_Due_Date=isnull(@Workflow_Due_Date,''), @Workflow_Due_Time=isnull(@Workflow_Due_Time,''), @Workflow_Approver=isnull(@WorkflowTaskAssignedTo,''), @Workflow_Originator=isnull(@Workflow_Originator,'') end else begin insert into #doc_steps_table select @Workflow_User as Workflow_User, @WorkflowInstanceID as WorkflowInstanceID, a.WorkflowStepInstanceID as WorkflowStepInstanceID, a.Workflow_Step_Type as Workflow_Step_Type, a.Workflow_Name as Workflow_Name, a.Workflow_Step_Name as Workflow_Step_Name, a.WF_Step_Predecessor as WF_Step_Predecessor, a.Workflow_Step_Sequence as Workflow_Step_Sequence, 0 as Workflow_Task_Assigned, 0 as Number_of_Assignees, '' as Workflow_Due_Date, '' as Workflow_Due_Time, '' as WorkflowTaskAssignedTo, @Workflow_Type_Name as Workflow_Type_Name, b.Workflow_Originator, @WfBusObjKey as WfBusObjKey from WFI10003 a, WFI10002 b where a.WorkflowInstanceID=b.WorkflowInstanceID and a.WorkflowInstanceID=@WorkflowInstanceID and a.Workflow_Step_Status=2 declare DOC_STEPS cursor local fast_forward for select Workflow_User, WorkflowStepInstanceID, Workflow_Step_Name from #doc_steps_table open DOC_STEPS fetch next from DOC_STEPS into @Workflow_User, @WorkflowStepInstanceID, @Workflow_Step_Name while @@FETCH_STATUS=0 begin select @is_user_assigned_task=0 select @number_of_assignees=COUNT(WorkflowStepInstanceID) from WFI10004 where WorkflowStepInstanceID=@WorkflowStepInstanceID and Workflow_Action_Date='' if (select COUNT(WorkflowStepInstanceID) from WFI10004 where WorkflowStepInstanceID=@WorkflowStepInstanceID and Workflow_Action_Date='' and UPPER(WorkflowTaskAssignedTo)=@Workflow_User)>0 begin select @is_user_assigned_task=1 end if @number_of_assignees>0 begin if @number_of_assignees=1 begin select top 1 @Workflow_Due_Date=Workflow_Due_Date,@Workflow_Due_Time=Workflow_Due_Time, @WorkflowTaskAssignedTo=WorkflowTaskAssignedTo from WFI10004 where WorkflowStepInstanceID=@WorkflowStepInstanceID end else begin select top 1 @Workflow_Due_Date=Workflow_Due_Date,@Workflow_Due_Time=Workflow_Due_Time from WFI10004 where WorkflowStepInstanceID=@WorkflowStepInstanceID select @WorkflowTaskAssignedTo='Multiple Users' end update #doc_steps_table set Workflow_Task_Assigned=@is_user_assigned_task, Number_of_Assignees=@number_of_assignees, Workflow_Due_Date=@Workflow_Due_Date, Workflow_Due_Time=@Workflow_Due_Time, WorkflowTaskAssignedTo=@WorkflowTaskAssignedTo where UPPER(Workflow_User)=@Workflow_User and WorkflowStepInstanceID=@WorkflowStepInstanceID end else begin update #doc_steps_table set Workflow_Task_Assigned=@is_user_assigned_task, Number_of_Assignees=@number_of_assignees where UPPER(Workflow_User)=@Workflow_User and WorkflowStepInstanceID=@WorkflowStepInstanceID end fetch next from DOC_STEPS into @Workflow_User, @WorkflowStepInstanceID, @Workflow_Step_Name end close DOC_STEPS deallocate DOC_STEPS select top 1 @Workflow_Name=isnull(Workflow_Name,''), @WorkflowStepInstanceID=isnull(WorkflowStepInstanceID,''), @Workflow_Step_Type=ISNULL(Workflow_Step_Type,0), @Workflow_Due_Date=isnull(Workflow_Due_Date,''), @Workflow_Due_Time=isnull(Workflow_Due_Time,''), @Workflow_Approver=isnull(WorkflowTaskAssignedTo,''), @Workflow_Originator=isnull(Workflow_Originator,'') from #doc_steps_table order by Workflow_User, Workflow_Task_Assigned desc, Workflow_Due_Date, Workflow_Due_Time, WF_Step_Predecessor, Workflow_Step_Sequence end if object_id('tempdb..#doc_steps_table') is not null drop table #doc_steps_table END

GO

Is there anyone out there running 2018R2 and using workflow that sees the same issue after upgrade ?
Derek Albaugh on at

Like (0)

Report

RE: Navigation Lists Very Slow

If it is just repeating the above over and over and over again, over a longer period of time, then you'd need to look at the tables being called and what the stored procedures are doing.

For example, it is looking in the WFI10002 table for a specific record. How many records are in this table that it would need to filter through to find the records it is looking for?

It also appears to be using the wfGetNavListFields stored procedure, so you'd need to look at what is being done/called by this procedure as well.

If the navigation lists do eventually load/show and there isn't any error, then it's mainly just a matter of how long SQL is taking to process the scripts being run by Dynamics GP.

A good start would be the System Requirements for Dynamics GP, specifically the amount of available RAM and CPUs we recommend, keeping in mind these requirements are MINIMUMS, you'll most likely need more than the minimum requirements.

The Dynamics GP performance white paper is also a good start, which you can find here:

community.dynamics.com/.../performance-with-dynamics-gp-where-do-i-start

mbs.microsoft.com/.../MDGP2010_WhitePaper_Performance

Thanks
Joe Powers 985 on at

Like (0)

Report

RE: Navigation Lists Very Slow

I have run a DEXSQL.log and there does not appear to be one single very long running script but many runs of these statements with changing ORDER or INVOICE numbers:

/* Date: 02/27/2019 Time: 8:33:18

stmt(50784016):*/

{ CALL ##zDP_1625732S_1 ( '2019.01.29', 2, 11, 'ORD42885', '7062786339' ) }

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(01000) Native Err:(5701) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'master'.*/

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(01000) Native Err:(5703) stmt(0):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english.*/

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(00000) Native Err:(5703) stmt(0):*/

*/

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(00000) Native Err:(5703) status(0):*/

SWSTATUS DUMP*/

/* Date: 02/27/2019 Time: 8:33:18

stmt(50805248):*/

BEGIN SET DATEFORMAT ymd

SET NOCOUNT ON

SET CURSOR_CLOSE_ON_COMMIT OFF

SET ANSI_NULLS ON

SET ANSI_WARNINGS OFF

SET ANSI_NULL_DFLT_ON ON

SET ANSI_PADDING ON

SET NUMERIC_ROUNDABORT OFF

END

/* Date: 02/27/2019 Time: 8:33:18

stmt(50802016):*/

use KHGP

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(01000) Native Err:(5701) stmt(50802016):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'KHGP'.*/

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(00000) Native Err:(5701) stmt(50802016):*/

*/

/*

/* Date: 02/27/2019 Time: 8:33:18

SQLSTATE:(00000) Native Err:(5701) status(0):*/

SWSTATUS DUMP*/

/* Date: 02/27/2019 Time: 8:33:18

stmt(50802016):*/

select top 1 WorkflowInstanceID from WFI10002 where Workflow_Type_Name='Sales Transaction Approval' and WfBusObjKey='ORD42885~2'

/* Date: 02/27/2019 Time: 8:33:18*/

Prepared stmt(50812520):

AND THESE:

* Date: 02/27/2019 Time: 8:33:18

stmt(50802016):*/

select top 1 WorkflowInstanceID from WFI10002 where Workflow_Type_Name='Sales Transaction Approval' and WfBusObjKey='ORD42887~2'

/* Date: 02/27/2019 Time: 8:33:18

stmt(50802016):*/

BEGIN DECLARE @stored_proc_name char(27) DECLARE @retstat int DECLARE @param5 char(51) DECLARE @param6 char(37) DECLARE @param7 smallint DECLARE @param8 datetime DECLARE @param9 datetime DECLARE @param10 char(238) DECLARE @param11 char(238) DECLARE @param12 int set nocount on SELECT @param5 = '' SELECT @param6 = '' SELECT @param7 = 0 SELECT @param8 = '1900.01.01' SELECT @param9 = '00:00:00' SELECT @param10 = '' SELECT @param11 = '' SELECT @stored_proc_name = 'KHGP.dbo.wfGetNavListFields' EXEC @retstat = @stored_proc_name '0B64B3AB-67DA-426C-A615-C46ABE892062', 'KHIND\JoePowers', 'Sales Transaction Approval', 'ORD42887~2', @param5 OUT, @param6 OUT, @param7 OUT, @param8 OUT, @param9 OUT, @param10 OUT, @param11 OUT, @param12 OUT SELECT @retstat, @param5, @param6, @param7, @param8, @param9, @param10, @param11, @param12 set nocount on END

/* Date: 02/27/2019 Time: 8:33:18*/

BEGIN UPDATE ##1625732 SET Workflow_Name = ?, Workflow_Originator = ?, WorkflowInstanceID = ?, Workflow_Type_Name = ? WHERE Workflow_Name = ? AND Workflow_Originator = ? AND WorkflowInstanceID = ? AND Workflow_Type_Name = ? AND DOCDATE = ? AND SOURCE = ? AND DOCTYPE = ? AND DOCNUMBR = ? AND CUSTNMBR = ? SELECT @@rowcount END

/* Date: 02/27/2019 Time: 8:33:18*/

Prepared stmt(50812520):

/* Date: 02/27/2019 Time: 8:33:18

stmt(50784016):*/

{CALL ##zDP_1625732N_1(25,'2019.01.30',2,11,'ORD42887','7122777496',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)}

I could send the entire log if it helps.
Derek Albaugh on at

Like (0)

Report

RE: Navigation Lists Very Slow

The best way to verify the information or what is causing it to take 20-60 seconds would be to run a dexsql.log first, then a SQL Profiler trace, to see what scripts or process is taking the longest.

Usually with a dexsql.log, we see which procedures or scripts are taking the longest to run, and then once identified, we can look at why that is.

As for Workflow 1.0 tables, those were all held in SharePoint databases as blob data, so its unlikely that it's looking for data in those tables.

Thanks