Strange, Poor BAQ Performance?

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.

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 5 seconds.

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.

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.

1 Like

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 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):

Non-Constrained (runs great):

Other than that, it seems to always want a new non clustered index, but that’s probably expected

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.

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.

1 Like

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.

Interesting! I’ve written hundreds of baqs and never run into this before, but I’ll give it a shot! Thank you :slight_smile:

@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


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 :stuck_out_tongue:

1 Like

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.