EpicorFree calculatorPDF, Excel, Word exportEpicor Kinetic

Epicor BAQ Runtime and Row Volume Estimator

A BAQ that returns in two seconds on a test company can take minutes in production. This estimator models the two things that drive that gap: how many rows are read and how many rows come out after joins. Use it to reason about a design before users depend on it.

Your numbers

Rows in driving table

rows

For example the row count of Erp.OrderDtl or Erp.JobOper.

Rows kept by your WHERE filter

10 %

Percent of the driving table that survives your criteria.

Number of joined tables

joins

Average rows matched per join

rows

1.0 for pure lookups, higher for one-to-many joins such as header to detail.

Filter can use an index

Rows processed per second

rows/s

Calibrate by timing a known query on your server.

Result row width

bytes

Your results

Result rows after joins

86,400

Row count the BAQ returns to the grid, dashboard or report.

Estimated runtime (seconds)

0.9093

Driving rows after filter

50,000

Rows read from the driving table

50,000

Total rows of work

136,400

Result size (MB)

32.959

A planning model. Real runtime depends on SQL Server plans, statistics, locking and server load. Confirm with the BAQ Designer Analyze tab and SQL profiling.

Save results:

Estimated runtime vs filter selectivity

Sweeps Rows kept by your WHERE filter from 1 to 100, holding your other inputs fixed.

BAQ performance review

Optional: send us your slowest BAQs and dashboards and we will suggest indexes and rewrites.

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

How to use the estimator

Enter the row count of the table your BAQ starts from, the share of those rows that survive your filter, and the number of joins with an average match count. Choose whether the filter hits an index. The tool reports result rows, total rows of work and an estimated runtime, plus a chart that sweeps filter selectivity so you can see how sharply a tighter filter helps.

Get driving row counts from SQL Server or a quick count BAQ.
Use a fanout of 1.0 for lookups to Part or Customer.
Calibrate rows per second using a query whose runtime you already know.

The model behind the numbers

Result rows equal filtered driving rows multiplied by the fanout once per join. Work equals the rows read from the driving table plus the rows produced. A table scan reads every driving row regardless of the filter, while an index seek reads roughly the filtered rows only. Runtime is work divided by throughput. It is deliberately simple so the levers stay visible: selectivity, fanout and index use.

Reading the result

If result rows are far above what a human can read, the BAQ is probably feeding a dashboard, export or another process and should be tuned for that. A large gap between rows read and result rows suggests a missing index or a filter applied too late. A fanout above 1.0 across many joins grows result rows exponentially, which is the classic cause of a BAQ that suddenly slows when one customer has many lines.

Common mistakes

Developers often tune the final SELECT while the problem is an unfiltered join to a large detail table. Calculated fields that call functions per row, and subqueries in the select list, add work not captured here. Always add date or company filters as parameters, avoid LIKE with a leading wildcard, and check the generated SQL when a BAQ misbehaves.

Frequently Asked Questions

Why is my BAQ slow only in Live and not in Pilot?

Live usually has many more rows and different statistics. A query that scans a table looks instant on a small Pilot database but takes far longer on years of production data. Compare row counts first.

Does this replace the BAQ Designer analyze feature?

No. The estimator is a quick what-if for design decisions. The Designer and SQL Server execution plans show what actually happens on your data and should be used to confirm.

What fanout should I enter for header to detail?

Use the average number of detail lines per header, such as lines per order or operations per job. If it varies widely, run the estimate for both the average and a heavy case.

How do I measure rows per second?

Time a representative BAQ with known row work, then divide rows read plus rows returned by seconds. Repeat on your production server at a quiet time, since results vary with load.

Netray can review slow BAQs and dashboards against your actual Epicor database and suggest indexes and rewrites. This estimator works fine without us.

Talk to an Expert Explore More Free Tools

Related Tools

Epicor

More Epicor tools

See all

Back to All Tools