JobHead LEFT OUTER JOIN JobMtl drastically slows down Job Traveler Report

Epicor ERP 10
1

Hi,
I’m trying to customize the Job Traveler report in SSRS and after syncing my copied base report with my duplicate JobTrav RDD, I noticed that the query line “LEFT OUTER JOIN JobMtl_” + Parameters!TableGuid.Value + " T7 ON T1.[Company] = T7.[Company] AND T1.[JobNum] = T7.[JobNum]" drastically reduced the speed of the report performance. Once I take out this query line the report performance is superb. The issue is I need some fields from this JOIN query line. Can you help direct me on a possible line of action to rectify this problem. The full dataset SQL is below:

="SELECT 
  T1.DueDate, 
  T1.IUM, 
  T1.ProdQty, 
  T1.ReqDueDate, 
  T1.StartDate, 
  T1.Calc_MultiJobParts, 
  T1.Calc_OrderQty, 
  T1.Calc_StockQty, 
  T2.AssemblySeq, 
  T2.BomSequence, 
  T2.Company, 
  T2.Description, 
  T2.DrawNum, 
  T2.IUM as JobAsmbl_IUM, 
  T2.JobNum, 
  T2.OverRunQty, 
  T2.PartNum, 
  T2.PullQty, 
  T2.RequiredQty, 
  T2.RevisionNum, 
  T2.Calc_BCAsmSeq, 
  T2.Calc_BCJobNum, 
  T2.Calc_BCPartNum, 
  T2.Calc_BCRevNum, 
  T2.Calc_comment, 
  T3.CommentText, 
  T3.Instructions, 
  T3.DaysOut, 
  T3.DueDate as JobOper_DueDate, 
  T3.EstProdHours, 
  T3.EstSetHours, 
  T3.Machines, 
  T3.OpCode, 
  T3.OpDesc, 
  T3.OprSeq, 
  T3.PrimaryProdOpDtl, 
  T3.PrimarySetupOpDtl, 
  T3.ProdStandard, 
  T3.RunQty, 
  T3.StartDate as JobOper_StartDate, 
  T3.StdFormat, 
  T3.Calc_BCOpCode, 
  T3.Calc_BCOprSeq, 
  T3.Calc_DispStatus, 
  T3.Calc_OPText, 
  T3.Calc_OPType, 
  T3.Calc_PurPoint, 
  T3.Calc_VendorId, 
  T3.Calc_VendorName, 
  T3.OpCode_OpDesc, 
  T4.CapabilityID, 
  T4.ConcurrentCapacity, 
  T4.DailyProdRate, 
  T4.OpDtlSeq, 
  T4.ProdCrewSize, 
  T4.ResourceGrpID, 
  T4.ResourceID, 
  T4.SetUpCrewSize, 
  T4.SetupOrProd, 
  T4.Calc_BCCapabilityID, 
  T4.Calc_BCResGrpID, 
  T4.Calc_BCResourceID, 
  T4.Calc_CapbltyDesc, 
  T4.Calc_ResDesc, 
  T4.Calc_ResGrpDesc, 
  T4.Calc_SchedResDesc, 
  T4.Calc_SchedResGrpDesc, 
  T4.Calc_SchedResGrpID, 
  T4.Calc_SchedResID, 
  T5.ResourceGrpID as ResourceTimeUsed_ResourceGrpID, 
  T5.ResourceID as ResourceTimeUsed_ResourceID, 
  T5.WhatIf, 
  T8.AssemblySeq AS AssemblySeq_SubAsmbl, 
  T8.RelatedOperation AS RelatedOperation_SubAsmbl 
FROM 
  JobHead_ " + Parameters!TableGuid.Value + " T1 
  LEFT OUTER JOIN JobAsmbl_ " + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company 
  AND T1.JobNum = T2.JobNum 
  LEFT OUTER JOIN JobOper_ " + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company 
  AND T2.JobNum = T3.JobNum 
  AND T2.AssemblySeq = T3.AssemblySeq 
  LEFT OUTER JOIN JobOpDtl_ " + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company 
  AND T3.JobNum = T4.JobNum 
  AND T3.AssemblySeq = T4.AssemblySeq 
  AND T3.OprSeq = T4.OprSeq 
  LEFT OUTER JOIN ResourceTimeUsed_ " + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company 
  AND T4.JobNum = T5.JobNum 
  AND T4.AssemblySeq = T5.AssemblySeq 
  AND T4.OprSeq = T5.OprSeq 
  AND T4.OpDtlSeq = T5.OpDtlSeq 
  LEFT OUTER JOIN JobMtl_ " + Parameters!TableGuid.Value + " T7 ON T1.[Company] = T7.[Company] 
  AND T1.[JobNum] = T7.[JobNum] 
  LEFT OUTER JOIN SubAsmbl_ " + Parameters!TableGuid.Value + " T8 ON T2.Company = T8Company 
  AND T2.JobNum = T8.JobNum 
  AND T2.AssemblySeq = T8.Parent
"
2

Do you get any data back and is it right.

Without knowing what you are wanting and also the complexity of your job materials for any job I think your join is too simplistic - ie bring back every material that matches on the job. If you have a material that is used on more than one operation/one assembly it does not have enough information to make an efficient join.

As I said I don’t know what you are trying to achieve but I think your join should be on T3 with T7 on company, job number, assembly and t3.operseq = T7.relatedoperation

3

Can you post a screenshot of your RDD relationship from JobHead to JobMtl?

4

Yes I do get data back and it is right. The problem is that it takes an average of 1min 45secs for the result to display in SSRS whereas the Crystal version of the same report displays within 10secs. I figured out the JobMtl JOIN is the problem because when I take it out, the report runs as fast as the Crystal version.

5

image
image.png1234×823 131 KB

6

Interesting, I wonder why they didnt add AssemblySeq as another relationship Key… But despite that, how big is your Job? Even with that flaw a Job with like 200-400 Materials should run okay.

7

Not sure if this is causing your slowness, but you’re creating two different branches of One-To-Many relationships in your main query. The out-of-the box job traveler uses subreports to separate these branches. As you have it written, the number of rows returned by the query and then managed through your tablix grouping will be the number of operations TIMES the number of material lines.

By the way, I usually stay away from the Sync dataset button in Report Styles because it will make the report dataset match the RDD but only the in most basic way. It doesn’t apply any logic such as the reasoning above, so you might end up with some improper data structures.

8

Do your jobs always have materials? If so, in the report statement, change the join from left to inner.

Might be worth while to rebuild the join in the RDD too. “ExternalPrimaryKey” in the Key field looks strange to me. I feel like that usually just says PrimaryKey.

9

Thanks to everyone that contributed to this post. Your comments truly enriched my experience. The performance issue was resolved by using SQL Tuning Advisor tool in Management Studio to tune the query and create the required indexes which dramatically improved the performance from 1min 45sec to 16sec.

10

Just keep in mind when you do Major Upgrades your Indexes might be deleted, so you will have to rebuild them.

2 Likes