How to make this BAQ more efficient?

Epicor ERP 10
1

Hi all, I have this BAQ I use to view order totals for each rep. I’m using SalesRepList and ICE to split each order into multiple rows for each split, but I think this slows the report.

This BAQ is really slow and takes multiple minutes to get an output. I was wondering where I could improve on the BAQ or if there is a better way to accomplish this.

MonthlySalesByRep.baq (27.3 KB)

2

A real quick way to speed this up is to put table criteria on OrderHed using the OrderDate field to shrink the dataset, I would assume there is no need to bring in every order ever created.

1 Like
3

Also a good idea to add Company to your table links. This didn’t speed it up much for me, but you might have different results. I think your best bet is what @Anthony_Mattice said. Reduce those initial datasets to limit the amount of data to match. When I ran this, it pulled orders from over a decade ago, so a date filter seems to be the quickest way to limit the results.
Good luck!

2 Likes
4

@TrevorLenten @NateS My report when running only returns about 10k records of orders going back to 2020 (except for one 2018 order), as this is when we moved to Epicor and don’t have prior data. My report requires the last 5 years of data so I can’t really cut down the date.

1 Like
5

Then I’d take NateS’s advice with the joins. Use the key fields when joining tables.

6

A good way to improve performance is to understand when the BAQ slows down.

Create a new BAQ and add tables/fields one by one. When things slow down you will know what is causing it. Then look at improving performance for the last thing you just did. Hard to debug for you as everyone’s enviornment is different.

5 Likes
7

I took another look see and noticed in the query description it mentions Power BI. If you’re extracting the data to manipulate in BI there is no reason (maybe I’m wrong) to run the query against historical data every single time.

I would run the SQL directly against the db and if that is not an option, which may well be the case since your profile mentions 3rd party hosted, then I would perform the initial extract, save that to excel and then monkey with the query to cut that dataset down for future (Monthly? Weekly?) updates and append that data to the set. Just food for thought though.