BAQ calculated field addition kills performance

,

I’m working on an obsolete part report that is taking a look at some part usage. More specifically Sales Order Usage for the past 12 months, as well as Component Part usage for the past 12 months. Everything performance wise is hunky dory when I have the fields separate. But as soon as I try to add the two together the performance for a single part jumps from 371 ms to 15000 ms

My first thought was I was having issues with having to do IS NULL checks in both of the calculated fields. But there is only a problem when I add the two. Any ideas?

I came across the exact same issue, with coalesce. Took about 2 seconds to run, but when I combined them I just got timeouts.

Never did come up with a solution, I used cubes to build the sales and usage data nightly and just used that instead.

There may be a better solution, but I couldn’t figure it out. This happened in SQL as well, so it’s probably not just a BAQ issue.

are you trying to get a monthly aggregate or just when the last time it was used?

aggregate

Have you looked at the inventory usage report to see if that gets you what you need?

The need for the report I was making was truly custom, so the Inventory Usage Report would have given me part of the requirement, but not the extra 60%. But appreciate the suggestion.

What does your query look like? Specifically those calculated fields.

Unfortunately i don’t have the original BAQ that was causing me issues anymore. I was having countless issues with inconsistent BAQ performance at the time, and I ended up having to move the query to a SQL view/External Datasource to get around the issues.

1 Like