I’ve noticed recently on my 10.2.600.5 instance some very odd and poor performance on BAQs with seemingly innocuous things. For example, take this query. [image] If I include the table restraint on Part.ShortChar01, the query times out and will not run. If I exclude it, the query runs in under …

That’s likely because you have a bad cache on your Query Plan. Try adding OPTION (RECOMPILE) to your query. This isn’t trivial to do but if you go to [image] Then add Order By 1 OPTION (RECOMPILE) persist in query [image] That may fix it, give it a shot.

Well, shoot. I don’t know what that means, but changing that makes it not run at all, without any table constraint

Interesting I wonder if you have a faulty index… odd.

What’s weird is I do have the rebuild-reindex job running every other day or so, I’d assume that would repair if faulty. I’ll keep digging. Seems to be a BAQ related issue though, since the same queries in SQL run excellent!

Look at the query plan, you can download it from Actions

Should I try that with the constraint on the table or without?

Well, I did both and surprise surprise, I have no idea what I’m looking at here. In the execution plan where I applied the table constraint (and thus the query didn’t run), it looks slightly different than the execution plan where I removed the table constraint (and thus did run). Without knowing h…

What if you put your QuoteHed.Quoted=0, QuoteHed.QuoteClosed=0 and Part.ShortChar01=‘Fresh’ as table criteria instead of in the joins? Also, you might want to look at adding an index on the Part.ShortChar01 if you use it a lot.

They are actually not in the joins, but are in fact table criteria, it looks like the sql it writes does make it look different

If you add it as a table criteria, it will show up as a join condition. To have the criteria in the where clause, you need to use the SubqueryCriteria.

Strange, Poor BAQ Performance?

Epicor ERP 10

josecgomez (Jose C Gomez) February 9, 2021, 10:47pm 2

That’s likely because you have a bad cache on your Query Plan. Try adding OPTION (RECOMPILE) to your query.
This isn’t trivial to do but if you go to

Then add Order By 1 OPTION (RECOMPILE) persist in query

That may fix it, give it a shot.

BAQ Performance suddenly horrible

Complex UBAQ gracelessly fails after 10.2.700 >> 2022.1 Upgrade

Topic		Replies	Views
BAQ TEST Performance Epicor ERP 10 baq	12	2738	March 24, 2022
Improving BAQ Performance Epicor ERP 10 e10 , baq	53	16613	November 27, 2024
BAQ Timing out but in SQL runs in less than 1 second Epicor ERP 10 baq	6	1928	August 24, 2020
BAQ Performance suddenly horrible Off Topic	21	3517	September 27, 2023
Tracking Down a Speed Issue Kinetic ERP e10 , customization , baq , dashboard	42	4217	August 7, 2023

Strange, Poor BAQ Performance?

Related topics