BAQ 100x slower in Production than Dev/test (Vote for this Idea for an easier fix in the future)

Epicor ERP 10
2

So your statistics may be out of date @JasonMcD

I would suggest you run a quick statiscs update on all tables involved in this BAQ in Production and see what happens. 9/10 times it flies right after. I’m not sure how you are doing maintainence on your DB but statistics are as important if not more than indexes sometimes.

Try this (this takes a few seconds to run even on the largest of tables and it shouldn’t hurt much maybe a small performance hit for a little while but perfectly fine in my eyes.

UPDATE STATISTICS Erp.PartRev WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
.
.
.

Ask @jgiese.wci what a difference a statistics update can make. We’ve both been blow out of the water before. Also you may have old / cached plans on this BAQ in SQL you can change that by adding an OPTION(RECOMPILE) at the bottom of the query. This is kind of tricky to do, but if you don’t have an order by clause then go go
image

image

This forces SQL to ditch its old Plan and generate a new one. You pay for it a little by having to generate a new plan but the cost is small compared to the 100X you are seeing now.

Try one and then the other if the first one doesn’t work I’m curious as to what you find. Adding an index is fine but those suggestions are query specific and those indexes though will help your one query, may hurt other areas of the system so be careful.

8 Likes