I’m back with another question. I’m troubleshooting a slow BAQ. It takes around 25 seconds to execute on our database. I’ve attached the BAQ and execution plan. If I’m reading this correctly (and I might not be), a large portion of the execution time is spent looking up the TerritoryID for some reason. The BAQ makes no reference to the TerritoryID–it just counts customers by when they were added. I had to do some convoluted dateadd()'s and datediff()'s to get the YTD by month values. Any help speeding this query up would be appreciated.
Hi Mark. Thanks for the advice but the box is already cleared. Furthermore, all of our salespeople have “View all territories” enabled on their Work Force entries. I have uploaded a similar BAQ which pulls the values for MTD instead of YTD. I composed the MTD query first, then copied and modified it to create the YTD query. The MTD query executes in less than a second. I’m at a loss why one would get stuck on pulling TerritoryID while the other doesn’t, when the only differences are how I’m calculating the date range and how I’m joining the subquery. Is it because of the weird subquery join, where I’m joining based on an expression and a calculated field?
Thanks. I think I got it. I was able to speed it up drastically by creating a calculated field MonthMatch in the CTE subquery, defined as datepart(month, Customer1.CreatedDate_c) and using a matching datepart(month, Customer.CreatedDate_c) as the expression to join the CTE with the top-level subquery:
