UD Fields ruin BAQ Performance?

Epicor ERP 10
1

UD fields that are killing performance, 10+ minutes to return data with the UD fields in the BAQ. Has anyone else experienced this type of situation?

I rebuilt a BAQ from the E9x IsSummary type to the E10.1 style of using sub-queries and all was looking good, 5-7 seconds to return data, until I added more fields from tables in the main query left out for testing.

Note: this is the same BAQ from another thread: BAQ: Get Query Execution Plan - #6 by Randy - ERP 10 - Epicor User Help Forum. The query SQLPlan analysis tool in SQL did help, a worthy tool that I need to learn better. It seemed to miss identifying the UD fields were the performance issue though, or could of been user error. :wink:

1 Like
2

The join to UD fields is a single inner join it should have minimal performance impact. What does the query look like?

1 Like
3

Agreed, I’ve never seen this issue before. The +criteria is “Open…” on all three Order tables plus the SubQuery Criteria you can see if the capture. There is one UD field from Part and two from OrderHed in this BAQ. There is about 14 calculated columns and about 50-ish total columns. On the plus side I cleaned up some chaff from the E9 verison.

image
image.png996Ă—643 39.3 KB

4

I was able to add the UD fields to one of the PartAlloc sub-query and they work now.

5

How did you figure out that was what you needed add? I have some queries that are slow and I would like to be able to figure out why.

1 Like
6

@Banderson Lots of banging head against desk. :crazy_face:

More seriously, but not far off from the truth, I started by rebuilding the BAQ. I added the tables, minus the E9 “Is Summary” ones and without any of the Calculated fields. Started with a set of columns for testing, in this case the basics: OrderNum/Line/Rel, Open Order/Line/Rel, Part#, ShipTo.City/State, CustID, plus others from the first BAQ. That all tested quickly (sub 3 seconds).

Then added the calculated fields that didn’t require the sub-queries. Tested OK, moved onto the first sub-query and it’s calculated fields, then the second, tested again. It was at about a 5 second run time. At this point I honestly thought it was the E9 style BAQ that was slowing down SQL. I added the remaining columns from the original BAQ, this was about 10 columns which included the UD ones. Tested and bam it was slow again.

As a test, I re-copied the original BAQ and removed the UD’s. It’s still slower than the new BAQ so rebuilding E9 style BAQs could help in your case.

7

So basically trial and error. uggghhh. I don’t have any queries coming from E9 so I won’t have your specific problem, but I would like to figure out how to performance tune some fairly simple Update able queries that take a little bit to load, but a long time to save.

8

Ahhh!!! I’m afraid that could be a separate issue than the BAQ query. Our updateable BAQs also are slower on saving data updates than loading data into the grid.

Are you using standard business object to update or a custom BPM / SC Workflow to update the data?

9

yeah, JobEntry BPM. I had to add a Pre-Processing BPM to set the DSPBuyIt flag, because it won’t save the buy it change without that, and flag is only the TT tables, so you can’t just put it in the normal query and map it. Otherwise it’s standard as far as the query goes. I have an extended UD field in the query, but the standard BPM seems to handle that.

I do have some other data directive BPM’s in the system to handle the backflush flag because we’ve had some problems with backflush and purchase direct both being checked and have had double issue problems. Maybe that’s slowing things down?