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.
I had a different query that timed out if I put date parameters on the InvoiceDate field. Running the query WIDE open in a dashboard to return 30K+ rows takes a couple seconds. But if I add a start and end date parameter, it refuses to run.
Any ideas? Both of the same queries run almost instantly in SQL as written.
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!
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 how to read this, it looks like everything in the “table constrained” query get hung up on something called a Hash Match? I don’t know what I’m looking at though.
Constrained (doesn’t run):
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.
Part.ShortChar01 is not a real Part field, it is UD field and when you add criteria on it, BAQ secretly adds join on Part_UD. Looks like search in Join for that UD is not effective. Try to move it out, to Where clause for example.
In my example the criteria is added on the table criteria (where clause, id assume?) but the syntax in the baq makes it look like it’s a join criteria. Since the baq presents views as table objects to interact, it shouldn’t matter I’d think?
@Aaron_Moreng I would try what @Olga suggested and move those specific criteria out of the BAQ and into the Query Execution Settings. It should definitely improve the performance.
I’ll give it a shot. I’ve never needed to use an alternate method before like this for criteria on any table (including non custom fiends, invoice date like I mentioned was failing for the same reason). But there must be something to it
It is another way around - you think it is in Where, but it is actually in Join. This was done for compatibility with E9 when we moved to E10.
Subquery Where should be used if I remember correctly to have a real Where clause. Or even in query execution settings, as suggested by @John_Mitchell, then filtering will be done at the very end. Try both.
@Aaron_Moreng If @Olga is right that the execution settings “where” only applies at the very end then you will need to use subquery’s to limit the rows that are hitting the hash match. Another option that I thought about might be to use parameters for your where statements. I think the BAQ’s execute parameters first, but I could be wrong there too.
@Aaron_Moreng the other fix I ran into recently was an out of date table statistics you could try this too
UPDATE STATISTICS Erp.Part WITH FULLSCAN
UPDATE STATISTICS Erp.Part_UD WITH FULLSCAN
Run those 2 commands in SQL and then try re-running your query, I had this similar bad performance a few weeks ago and the OPTION (RECOMPILE) didn’t help either but updating the individual table stats made the query go super fast instantly.
This command is safe and runs pretty quick for individual tables, always consult with your doctor before taking advice from the internet
Execution settings are applied after main query is executed. This runs main query as subquery and then its result is filtered. Normally, we don’t use it ordinary BAQ execution, but more in additional filtering, required for example in dashboards.
