I have a simple booked orders baq with the salesrep table tossed in to get the bookings by salesperson on the order.
The image shows SubQuery1 as my innersubquery. Mainquery is just the SubQuery1 table with a calculated field to sum the bookings value field. All other display fields are the grouping. Query runs in 80 ms. When I add criteria to MainQuery:
I generally like my criteria higher in the table order. If you go to Table List and move BookDtl up to 1 and put your criteria there, see if that makes a difference.
I might link OrderDtl to OrderHed as well…but do one at a time.
I would like the OrderHed to the OrderDtl, and then the Customer to the ORderHed… that way you are not looking at every customer, but only those that meet the criteria you set in the OrderDtl record.
2 Likes
I used input from both Mark and Tim. I had no idea changing the order would have so much effect. I also eliminated BOOKORD since it was not providing any input. BOOKDTL has everything this BAQ needs. Long story short, it runs in 167 ms! Thanks
1 Like
Sounds like you’re missing an index. After you’ve run the query, click Actions > Get Query Execution Plan. Open that with SSMS, right click on the green “Missing Index” text (you may have to scroll down to see it), and click Missing Index Details. It should show you a sensible index to create, name it (we use something like [IDX_<Table Name>_<Underscore separated column names>], e.g. IDX_OrderHed_Company_SalesRepList), and run it in your test/pilot instance. Rerun the BAQ, hopefully it’ll run closer to 80 ms.
Don’t go overboard adding a bunch of indexes, it could slow down create, update, and delete operations. I have also seen a bad index gets chosen and slow down other queries, so use them sparingly.
Sometimes if you adjust your query, you can hit an existing index, but I don’t think the ERP/Kinetic ships with one including SalesRepList.
1 Like
Yes, the sequence does matter… in your case, since you had criteria on the OrderDtl record, that criteria means that some of the records are not going to be selected… if none of the records from an order are selected, then you dont need the order header either. If none of the orders from the customer are selected, then you dont need the customer.
But they way you had the original query, it was probably selecting all orders, and all customers, and then later filtering them back out again.
ALSO… with Sales Orders & customers, there is an additional filter applied to every BAQ for the CRM Security to be applied. Sometimes that extra security can take a bad toll. Again, in your case, it was probably applying that security to both the customer lookup and to the order lookup, so it was probably not efficient there either.