DEV Community

Al hasib Tamim
Al hasib Tamim

Posted on Originally published at clearcutover.com

How to Fix Epicor Kinetic BAQ Timeouts: PartTran, TranGLC & Query Tuning

#sql #database #performance #erp

Originally published on Clear Cutover, where I fix Epicor Kinetic BAQ and SSRS report tickets at a fixed price.

Most Epicor Kinetic BAQ and SSRS report timeouts should be investigated as a SQL performance problem before blindly increasing timeout settings. Common culprits include reading millions of unindexed rows, joining tables at the wrong level of detail, implicit data-type conversions on generic keys, repeated subqueries, and expensive report datasets.

Here is a practical, step-by-step diagnostic guide for Epicor DBAs, developers, and Kinetic analysts.

How the Pieces Fit Together

Component Role Potential Bottleneck
Erp.PartTran Inventory transactions and adjustments Large scans when filters do not align with available indexes or lack selectivity
Erp.TranGLC General-ledger transaction links Broad joins across transaction types, incomplete keys, implicit conversions, or row multiplication
BAQ calculated fields Derive values for returned records Correlated subqueries repeating scans across thousands of outer rows
SSRS datasets & subreports Presentation, grouping, and subreports Transferring large raw datasets across the network or firing subreports per row

Step 1: Isolate the Expensive Table or Join

When a BAQ times out, do not guess which join is responsible. Isolate the execution:

  1. Test in the BAQ Designer first: Run the query with Analyze / Test. Does the timeout occur immediately at 30 seconds (standard command timeout) or after several minutes?
  2. Comment out TranGLC: Does the query drop from 90 seconds down to 2 seconds? If yes, the issue is generic key mapping or missing RelatedToFile discriminators.
  3. Filter to a single PartNum: Does the query run instantly for one part but time out across all parts? If yes, you have an unindexed date scan on PartTran.

Step 2: Check PartTran Filters & Index Alignment

PartTran is often the largest single table in an Epicor database, frequently holding tens of millions of rows.

The Date-Filter Trap

A common pattern in inventory reporting is filtering only by date:

WHERE pt.TranDate >= '2026-01-01'
Enter fullscreen mode Exit fullscreen mode

In many standard Epicor index configurations, composite indexes on PartTran begin with Company and PartNum before TranDate. A filter on TranDate alone cannot perform an efficient index seek across all parts; the engine must scan the table or a wide index subtree.

Practical Fixes

  1. Narrow the scope before joining: Combine date boundaries with specific TranType filters (e.g., STK-MTL, PUR-STK, MFG-STK) or specific warehouse/plant constraints.
  2. Avoid wrapping indexed columns in functions: Writing YEAR(pt.TranDate) = 2026 breaks SARGability and forces a full table scan. Always use range predicates: 
   pt.TranDate >= '2026-01-01' AND pt.TranDate < '2027-01-01'
Enter fullscreen mode Exit fullscreen mode

Step 3: Make TranGLC Joins Selective & Type-Safe

Erp.TranGLC is a polymorphic bridge table connecting general ledger distributions to invoices, receipts, jobs, and inventory transactions. Because it serves many tables, it presents two major performance traps:

1. Always Include RelatedToFile

Never join to TranGLC on transaction keys alone. If you do not specify RelatedToFile = 'InvcDtl' (or 'PartTran'), SQL Server must evaluate keys across millions of unrelated GL distributions from every module.

2. Avoid Implicit Conversions on Join Keys

In TranGLC, Key1, Key2, and Key3 are stored as nvarchar. However, the source keys in business tables are often integers (such as InvoiceNum, InvoiceLine, PackNum):

-- Illustrative type-safe join pattern:
ON gl.Company = d.Company
AND gl.RelatedToFile = 'InvcDtl'
AND gl.Key1 = CONVERT(nvarchar(50), d.InvoiceNum)
AND gl.Key2 = CONVERT(nvarchar(50), d.InvoiceLine)
Enter fullscreen mode Exit fullscreen mode

If data types do not match, SQL Server will convert the column on every row, disabling index seeks.

Step 4: Replace Repeated Scalar Lookups

A frequent cause of BAQ timeouts is retrieving the "latest transaction date" or "last cost" using a calculated field containing a subquery:

-- The slow scalar approach (runs once per row in the outer query!):
SELECT TOP (1) pt.TranDate
FROM Erp.PartTran AS pt
WHERE pt.Company = p.Company
  AND pt.PartNum = p.PartNum
ORDER BY pt.TranDate DESC, pt.TranNum DESC;
Enter fullscreen mode Exit fullscreen mode

If your outer query returns 50,000 parts, this subquery runs 50,000 times.

The High-Performance Fix: Common Table Expressions & Window Functions

Pre-aggregate or rank the data once in an inner SubQuery using ROW_NUMBER():

WITH RankedPartTran AS
(
    SELECT
        pt.Company,
        pt.PartNum,
        pt.TranDate,
        pt.TranNum,
        ROW_NUMBER() OVER
        (
            PARTITION BY pt.Company, pt.PartNum
            ORDER BY pt.TranDate DESC, pt.TranNum DESC
        ) AS RowIdent
    FROM Erp.PartTran AS pt
    WHERE pt.Company = 'EPIC01'
)
SELECT
    p.PartNum,
    rpt.TranDate AS LatestTranDate
FROM Erp.Part AS p
LEFT JOIN RankedPartTran AS rpt
    ON rpt.Company = p.Company
   AND rpt.PartNum = p.PartNum
   AND rpt.RowIdent = 1
WHERE p.Company = 'EPIC01';
Enter fullscreen mode Exit fullscreen mode

This scans PartTran exactly once instead of 50,000 times.

Step 5: Join on Company and the Complete Relationship Key

Epicor is a multi-company application. Omitting Company from any join condition can cause:

  1. Accidental Cartesian products across companies.
  2. Inability of SQL Server to utilize compound primary keys where Company is the leading column.

Step 6: Tune SSRS Datasets and Subreports

If the BAQ runs fast in the designer but the SSRS report times out in the System Monitor:

  • Avoid SSRS Subreports inside Tablix rows: A subreport embedded in a row executes a separate query against the report server for every single record rendered.
  • Filter in SQL, not in SSRS: Do not pull 100,000 rows into the RDL dataset and filter them with an SSRS expression. Push the predicate into the BAQ or RDD so SQL Server only streams what is actually printed.

The QueryTimeout = 0 Trap

When a BAQ or report times out, the tempting quick fix is setting QueryTimeout = 0 (or 600 seconds) in the BAQ execution settings or SSRS report properties.

Why this is dangerous:

  • 0 means infinite timeout. A poorly constructed Cartesian join or non-SARGable scan will sit on your SQL Server holding locks and consuming CPU/memory workers until the SQL instance restarts.
  • Always fix the underlying index alignment or subquery structure first.

Troubleshooting Summary

Symptom Probable Cause Action Item
Times out consistently at 30s Default command timeout reached Check execution plan; isolate if PartTran or TranGLC is causing table scans
Fast for 1 part, times out for all parts Broad PartTran scan without leading index key Add mandatory filters or date/plant boundaries; avoid YEAR(Date) functions
Slows dramatically after adding TranGLC Missing RelatedToFile or data-type mismatch Add explicit RelatedToFile = '...' filter and CONVERT() integer keys to nvarchar
Report hangs in ACTIVE state SSRS subreport row multiplication or network buffer Flatten subreports into single dataset; verify report server execution logs

About the Author

Al Hasib Tamim is an independent Technical ERP Analyst and contractor at Clear Cutover, specializing in Infor SyteLine data migrations and white-label Epicor Kinetic report, BAQ, and DMT subcontracting.

Explore the open-source diagnostic toolkit on GitHub: tamimhasib76/syteline-kinetic-erp-diagnostics.

Original article published on Clear Cutover.

Top comments (0)