BAQ Execution Total Time Strangeness

Epicor ERP 10
1

I am having a very strange issue with a BAQ. Simple BAQ OrderRel-OrderDtl-OrderHed-Customer. Criteria is OrderRel is Open and a Make. Test, returns 2186 records in 1171ms. Add criteria to OrderRel for ReqDate >= a parameter date (From) and ReqDate <= a parameter date (To). Test, enter From 10/25 To 11/24 and it take 43 seconds to return 943 records. Change ReqDate to NeedByDate. Test and returns 1046 records in 1.9 seconds.

I run the query in LinqPad as a SQL query using the ReqDate and it runs in less than a second. I been looking if there was any type of odd values in ReqDate and do not see any. We re-index and intgritchecks weekly on the database.

Thanks for any insight.

1 Like
2

Hi Scott,

A few things I would try:

  • put the OrderRel table first in the query
  • use inner joins instead of outer if possible
  • manually reindex for all the tables used in the query
  • try to clear the plan cache (restart the SQL server or use FLUSHPROCINDB)
  • take all the tables out of the query except OrderRel and then re-add them one-by-one. Run the query each time and see how it goes
  • try it in a different environment (test, pilot, etc.)
  • you said you reindex every week, do you update the statistics too ?
  • if you remove the query parameters and set fixed filters on the query with the same dates, does it take that long?

Dragos

3

Dragos, thanks, did a all those and we do update statistics. Ended up rebooting all our Epicor environment, still was slow using my test query. I removed all tables except for OrderRel and it ran as expected, very fast. Added OrderDtl (inner join) back and it ran fast, with using parameters. Change OrderDtl to first table, ran fast. Put my test query back the way it was when it was running slow, ran fast. Everything seems fine now, no real explanation why it is working now.