One of our readers – Jeff – requested a follow up to the report performance post focused specifically on BAQs. I always appreciate reader feedback, so Jeff this one is for you (and of course anyone else with BAQ performance woes).
BAQs (Business Activity Queries if you’re unfamiliar with the acronym) are our eyes into the database. A BAQ is our way to ask the database a question and get an answer. One important thing to note is that our question might be… uh… terrible… and then we get equally terrible results. Who knew the BAQ would not intuitively know what I’m looking for?! That said, there are a few things we can do to make our database questions more precise and therefore get more precise answers without bringing our session to a grinding halt (I know we’ve all experienced the dreaded white-window time-out Ctrl-Alt-Del inducing BAQ-explosion at some point).
Criteria versus Filters
Criteria and filters are great for giving us more specific results; however, only criteria have an impact on performance. When you add criteria to a table in the phrase builder you’re restricting the full record set of the returned results. If you look at the full phrase in the general tab you’ll see the criteria as part of the total query phrase. Less records to return means a faster BAQ. Filters apply to the returned record result set and restrict the rows viewed. That’s the difference: criteria restrict rows returned, filters restrict rows viewed out of the returned rows. Therefore, filters will not help your BAQ performance. I still use filters for lots of things though. Since they operate on the rows returned I can filter null values, calculated field values, and create different views of my BAQ in a dashboard. If you have a BAQ that’s using lots of filters without criteria and it’s slooooow, think about which filters you can change to criteria to help performance. As a rule of thumb every BAQ should generally have at least one table criteria somewhere in the phrase builder.
The database is ordered in a very specific way and BAQs tend to perform best when that structure is maintained in your phrase structure. For instance, if I’m looking at Sales Order I’ll want to step from OrderHed to OrderDtl to OrderRel in a straight line. This mimics the Header-Line-Release one-to-many structure already followed by the database and maximizes the number of indexed fields used in table joins (more on this in a minute). If I branched from OrderHed to OrderDtl and then OrderHed to OrderRel I might still get useful data but my query is malformed and will not be as efficient.
Use the Indexed Fields
Certain fields in data tables are indexed to provide faster search results (check out this Wikipedia article for more general details on indexing). In ERP, the data dictionary viewer will show you a list of the indexed fields on any given table – most of them correspond to the foreign-key values e.g. OrderNum, PartNum, JobNum etc. Linking on indexed fields in your joins between tables will dramatically improve BAQ performance compared to non-indexed fields. The Company field should always be the first field relationship in any join between tables.
Inner Joins are Faster
In a BAQ there are both inner and outer joins. Inner joins functionally specify “with” while outer joins specify “with or without”. Just like criteria, inner joins restrict the total rows returned in the result set and therefore improve performance. There are definitely instances where outer joins are necessary, but if your BAQ can be restructured to use less outer joins it will improve the speed. As a best practice outer joins should be used as close to the terminal end of any given branch in your phrase builder as possible.
Restrict the Multi-Key Tables
If you’ve ever written a BAQ against TranGLC, PartTran, ComXRef or PatchFld you know exactly what I’m talking about. These tables (and some others) use a multi-key structure and “RelatedTo” field to create a large foreign key. The more restrictions you can put on the table the better. For instance, if I’m joining to TranGLC not only do I have my inner join defined with as many field relationships as possible, I’ve also put criteria on the table to limit the “RelatedTo” field to only the type of values I’m interested in. If you create an outer join off a giant table (such as PartTran) without criteria I can guarantee your BAQ will be slow.
Choose the Right Tool
BAQs are awesome, I use them all the time to find data and write reports. That said, they are not the only tool in the toolbox. If you’re trying to dump some large data set for summary manipulation you might be better off using an Executive Query – this does the number crunching on a set schedule rather than using lots of summary tables in the phrase builder. Likewise, some groupings are really difficult in a single BAQ – Crystal Reports offers a great alternative to visualize and reformat your data (via a BAQ report or standard report modification). SSRS is always an option, as is EPM. Dashboards are a great way to tie together lots of little BAQs rather than have one big BAQ monster. The point I’m trying to make is that there are lots of options; just because you know BAQs doesn’t mean everything should be a BAQ.
I hope this gives you some ideas for your own query writing. Remember, there is no such thing as a dumb question, but the database might try to answer and fail miserably if your question is malformed or imprecise. Precise questions will give precise (and generally fast!) answers.
Contact us if you have any questions or if you’d like to learn more.