Unions and performance

So I have a query where I need to combine the JobAsm and JobMtl tables through a union. For some reason, the query takes a LONG time to run, even if I parameterize all levels to one (albeit large) job.

Do all unions have performance issues? or is it likely that I could be doing something that is hurting performance?

do both queries run quickly on their own? when combined into the union it is slow?

unions have a little overhead, but should be much different than running the queries on their own.

BAQ designer has Get Query Execution Plan action, so you can download query plan and find what is hurting performance.

2 Likes

Yeah, for the most part I think. This is the one for the the assemblies

image

This is the one for the materials

image

Then the union is joined to a UD table which has the job number, assembly sequence, and material sequences as key 1 2 and 3.

It just ran for over an hour, then I killed it and it shows 2939 rows and says it’s can’t continue because it’s in kill state.

any tips on how I read this? I had to change the extension to txt because I couldn’t upload .sqlplan to the website.

PackOrNotUpdateableTest.txt (99.9 KB)
?

Because I know you can read SQL easily Ken here is the query

select 
	[UD08].[Key1] as [UD08_Key1],
	[UD08].[Key2] as [UD08_Key2],
	[UD08].[Key3] as [UD08_Key3],
	[UD08].[Key4] as [UD08_Key4],
	[UD08].[Key5] as [UD08_Key5],
	[UD08].[ShortChar02] as [UD08_ShortChar02],
	[UD08].[Number01] as [UD08_Number01],
	[JobMtlTable].[JobMtl_RequiredQty] as [JobMtl_RequiredQty],
	[JobMtlTable].[JobMtl_Company] as [JobMtl_Company],
	[JobMtlTable].[JobMtl_JobNum] as [JobMtl_JobNum],
	[JobMtlTable].[JobMtl_AssemblySeq] as [JobMtl_AssemblySeq],
	[JobMtlTable].[JobMtl_MtlSeq] as [JobMtl_MtlSeq],
	[JobMtlTable].[JobMtl_PartNum] as [JobMtl_PartNum],
	[JobMtlTable].[JobMtl_Description] as [JobMtl_Description],
	[Part].[ClassID] as [Part_ClassID],
	[JobMtlTable].[JobAsmbl1_PartNum] as [JobAsmbl1_PartNum],
	[JobMtlTable].[JobOper_OpCode] as [JobOper_OpCode],
	[UD08].[ShortChar01] as [UD08_ShortChar01],
	[UD08].[Character01] as [UD08_Character01],
	[PackStatusSuggest].[Calculated_CountofStatus] as [Calculated_CountofStatus],
	[PackStatusSuggest].[Calculated_StatusSuggest] as [Calculated_StatusSuggest]
from Ice.UD08 as UD08
inner join  (select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[JobNum] as [JobMtl_JobNum],
	[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
	[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[JobMtl].[Description] as [JobMtl_Description],
	[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
	[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
	[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobMtl as JobMtl
inner join Erp.JobAsmbl as JobAsmbl1 on 
	JobMtl.Company = JobAsmbl1.Company
	and JobMtl.JobNum = JobAsmbl1.JobNum
	and JobMtl.AssemblySeq = JobAsmbl1.AssemblySeq
inner join Erp.JobOper as JobOper on 
	JobMtl.JobNum = JobOper.JobNum
	and JobMtl.Company = JobOper.Company
	and JobMtl.AssemblySeq = JobOper.AssemblySeq
	and JobMtl.RelatedOperation = JobOper.OprSeq
where (JobMtl.JobNum = @JobNum)
union
select 
	[JobAsmbl].[Company] as [JobAsmbl_Company],
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
	(0) as [Calculated_MtlSequenceAssy],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobAsmbl].[Description] as [JobAsmbl_Description],
	[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
	[JobAsmbl2].[PartNum] as [JobAsmbl2_PartNum],
	[JobOper1].[OpCode] as [JobOper1_OpCode]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobAsmbl as JobAsmbl2 on 
	JobAsmbl.Company = JobAsmbl2.Company
	and JobAsmbl.JobNum = JobAsmbl2.JobNum
	and JobAsmbl.Parent = JobAsmbl2.AssemblySeq
inner join Erp.JobOper as JobOper1 on 
	JobAsmbl.Company = JobOper1.Company
	and JobAsmbl.JobNum = JobOper1.JobNum
	and JobAsmbl.Parent = JobOper1.AssemblySeq
	and JobAsmbl.RelatedOperation = JobOper1.OprSeq
where (JobAsmbl.JobNum = @JobNum))  as JobMtlTable on 
	UD08.Company = JobMtlTable.JobMtl_Company
	and UD08.Key1 = JobMtlTable.JobMtl_JobNum
	and UD08.Key2 = JobMtlTable.JobMtl_AssemblySeq
	and UD08.Key3 = JobMtlTable.JobMtl_MtlSeq
inner join Erp.Part as Part on 
	JobMtlTable.JobMtl_Company = Part.Company
	and JobMtlTable.JobMtl_PartNum = Part.PartNum
left outer join  (select 
	[GroupPackStatus].[UD081_ShortChar01] as [UD081_ShortChar01],
	[GroupPackStatus].[UD081_ShortChar03] as [UD081_ShortChar03],
	(count( GroupPackStatus.UD081_ShortChar02 )) as [Calculated_CountofStatus],
	((case when CountofStatus = 1 then max( GroupPackStatus.UD081_ShortChar02 ) else '' end)) as [Calculated_StatusSuggest]
from  (select 
	[UD081].[ShortChar01] as [UD081_ShortChar01],
	[UD081].[ShortChar03] as [UD081_ShortChar03],
	[UD081].[ShortChar02] as [UD081_ShortChar02]
from Ice.UD08 as UD081
group by [UD081].[ShortChar01],
	[UD081].[ShortChar03],
	[UD081].[ShortChar02])  as GroupPackStatus
group by [GroupPackStatus].[UD081_ShortChar01],
	[GroupPackStatus].[UD081_ShortChar03])  as PackStatusSuggest on 
	UD08.ShortChar01 = PackStatusSuggest.UD081_ShortChar01
	and UD08.ShortChar03 = PackStatusSuggest.UD081_ShortChar03
where (UD08.Key1 = @JobNum)

first glance removes the PackStatusSuggest frorm the query.

does it run quicker then?

I will look closer in a bit…

slightly, but that’s what I need to add…

we can add it later was seeing if this was the bottleneck quickly without looking at the sql

To the where clause for both JobMtl and JobAsmbl add company as well as Job Number. It will change your index scans to seeks.

For Example JobMtl.JobNum = @JobNum and JobMtl.Company = @Company

1 Like

That seems to help some. Still is pretty slow, but it completes on one of the smaller jobs.

It should be fast. Wrap it up even more in Sub-Queries and ask for the JobNum at the Sub-Query Node perhaps. I run a complex Query that goes to JobOper etc… it runs btw 2-4sec for 3K rows. Also filter out what you don’t need.

Don’t mind some of the parenthesis, prob left-over =) from playing around with it.


Sample 2:


Upload your .baq

So I did a webex with Ken, and he was able to put the query direct into SQL and it returns the whole thing in a matter of seconds. We were playing around with some things, and noticed that if I add the JobHead table to the top level, it will finish. Even though I am not using anything from that table. We think Epicor is “helping” somewhere and it’s causing problems. I even got rid of the union altogether. The problem is happening when I have to join the count and suggestion back to the line data.

PackOrNotUpdateableTest.baq (85.5 KB)

Here’s the BAQ. I don’t think it will mean much to anyone else though because UD08 needs to be populated with packing data. But you can take a look. For reference on UD08, Key1 is the job number, Key2 is the AsmSeq, Key3 is the MtlSeq (for assemblies, Key3 is 0, for materials Key2 is the parents assembly seq). ShortChar01 is the part number, Shortchar03 is the parent part number, Character01 is the part description, ShortChar02 is the pack status (indicates whether the part gets assembled or packed serperately, or a couple of other options. This determines whether is needs to show up on a packing list or not)

And what I am doing is looking for the combination of part number and parent part number and seeing if it has been seen in the system before, and how many different ways it’s been packed/assembled. If it has been seen, and the pack status is the same for every time it’s been seen, it will return a suggestion of the same pack status. If it hasn’t been seen, or has been packed more than one way, the query will not return a suggestion and someone needs to look at the print and determine how they are going to do it this time. The goal is to try to learn what we did before and basically default the pack status to that so it reduces the amount of time to go through and set pack statuses for new jobs.

Because I need to look at history, I can’t really filter out much on the counting side, but if I just run the counting portion, it runs very quickly. The problem comes when I rejoin that counting portion back to the UD08 table. I need to do that to get the suggestions for new jobs. Like I stated earlier, it runs fine in SQL, but through epicor, it gets caught up.

I’m playing around some more with adding the JobHead table, and I think I can make it workable by doing that.

So I tried adding the counting to the original query to put into the dashboard and it’s not working. Some jobs will pull the info, others will not.

Would this be a good example of when you would want to use an external query? Since it works in SQL fine? One of the problems with this is that it’s an updateable dashboard, so I don’t know how the external would work with that.

So I tried to be fancy with this, and used the BAQ constant for current company, and it did not like it. (but I didn’t know that’s what it was)

Now, I hard coded the company to RAPAT, and it flies. Go figure.

(I thought I posted my update a while ago, but I forgot to hit send)

So I have original query working really fast now.

However, I still have a hard time (times out most of the time) with the history when I join it back to the query. Any ideas why that would be?

This might be above and I missed it but what does joining history back to the query mean? Can you post the BAQ file?

I have a sub query that groups and counts the number of unique pack statuses (like pack, assemble etc) this is table UD08. I want to look through the whole table and any time the child part and the parent are the same combination, I want to see how many different ways it has been set. Basically giving me a history. I am using that sub query to join to current job information and give suggestions whenever the same child parent combo is the same and there is only 1 unique status. So the “history” is simply a sub query which I would like to join to the top level to give the suggestions.

The BAQ is up above. It hasn’t changed, other than adding “company” to the criteria.

Gotcha - I will take a look again

So I’m back to try and figure out how to make this work well enough to use. The join of two values seems to bog it down to unusable speeds. I need to more ideas for what else I can try. Thoughts I’ve had so far.

  1. Concatenate Parent part number and Child part number as populate a key field with these. It would speed up things I think, but potentially cause problems because I really only want the job number, assembly sequence, and material sequence as the identifying parts of the table. Part numbers can change to easily (for example a bolt length was wrong, and we need another part number for a 1/4" longer bolt)

  2. Basically the same as above, but instead of a key field, just use another character field. This one I’ll probably try today.

  3. Create some sort of update process that populates the pack suggestions in the table at regular intervals. This would take the load of when the end user is populating new jobs with pack status instructions, but would add another process to be managed. Also this managing process would use the same slow query, so it may still time out and break.

Any other ideas of things that I could try?

this post qualifies as TLDR today, however have you tried looking at the execution plan for this query and see where it is getting hung up? Sorry if it has already been discussed.

It has been discussed, but I don’t really know what I am looking at, or what to do about the results. It looks like there a couple of “Clustered Index Seek” at 20-30%. Let me get my test one set back up the way that I think it should work and I’ll recheck it and see if anyone can tell me what I should do with the results.

1 Like