Dynamic Query and General Performance

I was wondering if I could get some direction on figuring out what I could do to make this BAQ load faster, we recently migrated our app servers to in house and also upgraded to 10.2, since then certain dashboards/BAQ/BOs have slowly taken longer and longer to run. I looked at memory usage and that seems fine, and the BAQs were running mostly fine before the migration and upgrade. It currently take over 5 minutes for the dashboard I’m using for testing to load.

Along with this I will be doing more digging and testing on how to make our Epicor more performant in general, using the performance tuning pdf. Are there any other quick tips for making Epicor faster that I can take a look at. Thanks

Could be the database indexes and statistics need updating / rebuilding? That could explain the slow degradation over time, especially if you are querying tables with lots of new data.


This makes sense, we did also migrate our DB and its possible that when they migrated that they didnt set any weekly maintenance up on the databases. I will take a look at this. Thanks @bmanners!

If your issue is only in BAQ, try removing the Company Join bw the Tables (as logn as Query is not Cross Company).

Tried doing this and it took slightly longer haha. Slightly as in a normal amount where its not a real factor to the speed it would seem, Although this BAQ does check a lot of tables, it is relatively quite simple as far as its logic goes.

I’ve seen the same thing happen after changing/upgrading/migrating. There’s no one right answer as far as I know, but sometimes adding an extra index or two can tip the balance back in your favour with BAQs if you’re joining on non-standard keys.

Yeah, I learned that the full text service was off, so I am not even sure how long that has been happening. Also we currently only have a single tempdb, set up, and the epicor manual was telling me to have 1 per every 2 cores. So I am going to figure out how to create a couple more tempdb’s for the server, and I am currently running the full text indexer, which is taking a very long time. hopefully the indexing will fix the extreme issue, and adding extra indexes, and tempdb’s will make the performance really good.

Thanks for the input on this. Its nice learning all the options I can take to really optimize epicor.

The indexes finally finished the rebuild and the BAQ ran in 1/10th of the time it was taking before. It turns out that the full text daemon on the SQL server had gotten turned off by someone else, and therefor the indexes haven’t actually built for I have no idea how long.