Can you force a query execution plan for BAQ

[Randy (Randy Stulce)]

@Banderson

Side note, does anyone know why in the classic BAQ editor the option to pull the execution plan would be grayed out?

Never mind, it used to work that way.

[JasonMcD (Jason McDermott)]

Same indexes for me; different plan. Not surprised there.

[JasonMcD (Jason McDermott)]

Thank you @Mark_Wonsil and @Banderson for putting up with me. This was helpful.

@Banderson

Generally this happens naturally as things that make the record unique are the same things the index is built on. But if there are ever things that are “optional” in that, you’ll get a unique record without a specific part of a join, but it doesn’t change your records returned to put it in, if that shows up in an index that can be helpful, then adding that piece to the join can make the query more efficient

OK, makes sense. That confirms what was kind of in my mind. Best practice (and necessity) tends to lead you toward good indexes. But you never know what you don’t know about, so it’s good to check the indexes, especially when you are hitting a wall with efficiency.

[Mark_Wonsil]

And you’re right. There is one exception, which is a topic you were involved with:

Missing Joins for BAQ usage - an IDEA for you to vote on and comment on

If you have examples of the system generating invalid joins, we would like to know about that. I do know that sometimes when you join the Order to the Customer table, it will join the BTCustomer instead of the Customer number. But there is also a combo box asking you which of the two joins you want to use. Many times, we are finding that people create inefficient joins. for example they leave out the join to the COMPANY field, which makes SQL work harder to get to the data you want. Our joins …

[JasonMcD (Jason McDermott)]

Yes, that one came to mind a few times.

Now to tackle an efficient join of PartTran to TranGLC.

[dr_dan (Dan Ramirez)]

Efficient? lol… I don’t know how efficient it is but if you have some criteria to lead you in the right direction, it works OK. I’ve found it to be extremely helpful for reconciling some financial things that had to be reversed, etc. But in those cases, we always knew which account to start from and/or a fairly small date range… or a specific invoice(s)…

[JasonMcD (Jason McDermott)]

Yeah, same here.