Dashboard - multiple parameters in pub/sub between queries - poor performance?

Epicor ERP 10
,
1

Hi all,

In an attempt to make dashboards with multiple queries that require parameters more useful, I’ve adopted the approach of using an initial “selector” query, that lists the item(s) that should drive the other queries. For instance, I’ve developed an Indented BOM and Summarized BOM query that live on the same dashboard. The user enters a part number into an initial “Part Selection” tracker that runs a simple BAQ listing the part and its revisions. Using the publish and subscribe functionality, I’ve connected the two main query’s parameters to the part selector’s published PartNum and RevNum fields. When a user clicks a part/revision, the other two queries update.

This works, but is horrendously slow. Individually, each BAQ takes only a few seconds for smaller BOMs, but when refreshed on this dashboard, they take 4x as long. It appears (based on the loading indicator) that each query is being run multiple times. I hypothesized that this may be due the way publish events are designed. Perhaps when a row is selected, each published field triggers its subscribers, regardless of the existence of others? If true, this would be a design flaw and would mean that the Mandatory setting on BAQ parameters is being ignored.

Has anyone experienced this?

Thanks!

2

An update:

I was able to increase performance by combining parameters into a single delimited string, then splitting it out into the individual criteria on the subscribed BAQ.

For example, in my “lookup” BAQ, I included the field PartRevCombo. This was a colon delimited calculated field containing both the PartNum and RevisionNum.

CONCAT(Part.PartNum, ':', MainRev.RevisionNum)

Then in the subscribed BAQs I just use LEFT and RIGHT to split out the parameters to their correct column filters.

LEFT(@PartRevCombo, CHARINDEX(':', @PartRevCombo) - 1)
RIGHT(@PartRevCombo, LEN(@PartRevCombo) - CHARINDEX(':', @PartRevCombo))

image

By reducing the subscribed parameters to one, dashboard performance increased significantly.
My non-scientific opinion says that BAQs are being run multiple times regardless of if missing parameters are present. This has a hugely negative impact on performance.

If anyone has a better way around this issue, I’m all ears. Otherwise, I hope this helps someone!

2 Likes