Epicor BAQ Performance Tuning Checklist (Free)

0%

0 of 26 items complete

7 critical items still open - these are the highest-risk gaps.

Download checklist:

Reproduce and measure

Run the BAQ with the same parameters that are slow for the userCRITICALRecord the run time and row count before changing anythingCRITICALCheck whether the slowness is the query or the dashboard rendering and gridTest as a user with the same security group, not only as adminNote whether the issue is constant or only at certain times of day

Joins and tables

Every join includes Company and all key columns of the parent tableCRITICALRemove tables that are joined but contribute no output or criteriaUse inner joins where a match is always required instead of outer joinsAvoid joining the same large table twice if a subquery or one pass would doCheck that Customer and Vendor joins use CustNum and VendorNumCRITICAL

Criteria and filters

Large tables such as PartTran, LaborDtl and InvcDtl have a date or key filterCRITICALFilters sit in table criteria rather than in post-query display filtersUse parameters with sensible defaults so users do not run unbounded queriesAvoid LIKE with a leading wildcard on large tablesAvoid functions on filtered columns that stop an index from being used

Calculated fields and subqueries

Heavy calculated fields are not evaluated per row where a join would doCorrelated subqueries are replaced by joins or pre-aggregated subqueriesCRITICALSubqueries return only the columns neededReturn only the columns the dashboard displaysAvoid distinct or sorting on large result sets unless needed

Database and environment

Ask the DBA whether index and statistics maintenance ran recentlyCRITICALCheck for blocking or long-running sessions during the slow periodCompare against a test environment with similar data volumeReview execution plan for table scans on large tablesConfirm the BAQ is not called repeatedly by a BPM or a refresh timerAfter changes, retest and document the before and after times

Critical items usually explain the largest share of slowness. Fix those first, then re-test with the same parameters.

BAQ performance review

Optional: send us your three slowest queries and we will point out likely causes.

Optional. The tool and every export work without this. No spam.

How to use the checklist

Start with the measurement group so you know the baseline. Then work down: joins, criteria, calculated fields and finally the database. Change one thing at a time and re-run with the same parameters, so you can tell which change helped. Export the checked list and attach it to the support ticket or change record so the next developer knows what was tried.

The usual suspects

On most systems the same three issues explain the majority of slow queries. First, a missing Company or key column in a join that multiplies rows. Second, no date filter on a history table, so the query reads years of transactions. Third, a correlated subquery that runs once per row. Fix those and you will often see a ten times or better improvement without touching indexes.

Check join keys before anything else
Bound history tables by date
Replace per-row subqueries with joins

When it is not the query

If the query runs fast in the BAQ designer but the dashboard is slow, the cause is probably the grid, too many columns or a refresh timer. If it is slow only at certain hours, look for competing jobs such as MRP, backups or other reports. Heavy analytics belong on a replica or data warehouse rather than the live transactional database, which protects shop floor transactions.

Frequently Asked Questions

Why is my BAQ fast in the designer but slow on a dashboard?

The designer usually tests a limited number of rows. Dashboards may run the full set, add grid rendering and refresh on a timer. Reduce returned columns, add parameters and check refresh settings.

Do I need to add indexes?

Only after fixing joins and filters. Standard tables are indexed for key access. Custom indexes should be agreed with your DBA and Epicor support, because they can be overwritten or affect upgrades.

Does Epicor cache BAQ results?

Some data is cached on the client side, but you should not rely on caching for performance. Make the query efficient and bounded by parameters.

How can I find which BAQs are slow?

Use Epicor's server logging and tracing options, or ask the DBA to review long-running queries on the database. Then trace the offending query back to its BAQ by its text and tables.

If your users export Epicor data to spreadsheets because dashboards are too slow, Netray can help set up a read-only analytics layer off your live system.

Talk to an Expert Explore More Free Tools

Related Tools

Epicor

More Epicor tools

See all

Epicor BAQ Performance Tuning Checklist: Fix Slow Queries and Dashboards