This one is odd. We did an upgrade from 10.2.400 to 10.2.700 and now this one user can’t run a BAQ that he was able to run before. Other people CAN run it (myself included). We tried looking at security, temporarily giving him security manager access so he could run the BAQ directly (normally it’s through a dashboard) and the BAQ timed out on him. When I run it, it comes back in 307 ms.
Any ideas?
Is that during an update or even when just doing the Get List?
During Get List
This is the code that shows on the front screen, it’s not a complicated BAQ.
/*
select
[OrderHed].[Company] as [OrderHed_Company],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[Customer1].[CustID] as [Customer1_CustID],
[Customer1].[Name] as [Customer1_Name],
[OrderHed].[UserChar2] as [OrderHed_UserChar2],
[UserFile].[EMailAddress] as [UserFile_EMailAddress]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.Customer as Customer1 on
OrderHed.Company = Customer1.Company
and OrderHed.BTCustNum = Customer1.CustNum
and ( Customer1.CreditHold = 1 )
left outer join Erp.UserFile as UserFile on
OrderHed.UserChar2 = UserFile.DcdUserID
where (OrderHed.OpenOrder = 1 and OrderHed.CreditOverride = 0)
order by OrderHed.UserChar2 Desc, OrderHed.Company, OrderHed.OrderNum
if you have CRM, then the person running the BAQ will have different results depending on their assigned workforce ID. This is because CRM adds additional filters to every query when the Customer (and/or OrderHed?) tables are on the query. This is all done in the name of SalesPerson Security.
SO, you should look at the person to see what sales region / territory they have assigned.
We do have some people with a CRM license and some that don’t have it. The only work force ID they have access to is a shared one with all territories.
Ok, we have narrowed it down further. The issue is that it’s cross company. When we uncheck it, it works for the current company. Yet the people running the report DO have access to the companies involved. I’m thinking I may have to open a case with Epicor support.
It’s not JUST that I don’t think, there has to be more. Our boss has the same issue with the report (which is how we figured it out, he was willing to test), the report times out when Cross Company is checked, but not when it’s un-checked. Updateable is fine. We had him compare with a user who CAN run it, and even setting all the same options didn’t make it work.
I hate things like this.
I just looked at your first screen shot. It’s a timeout issue. The security stuff that Epicor adds to the BAQ can be a drag on the BAQ. When you uncheck cross company, it effectively reduces the amount of data coming back. If you add that, but filter something else down, it could probably work.
The fix for this is a lot of times looking at indexes and doing some SQL tuning so that the query can run faster. Unfortunately this takes some skill and is hard to explain.
Thanks for the reply 
The problem is that when I run it, or other people in IT (besides our boss) run it, it comes back very quickly. 247ms this last time. So why is it timing out only for them?
Could be a cache thing. In the BAQ try going to the execution settings, add a setting, like shown and see if it fixes it.
If you remove the OrderBy’s, does the BAQ run for those who it times out for?
You could then apply the ordering in the RDL
@MLamkin If you add it to a dashboard it won’t time out. Won’t go any faster, but should finish. Curious why you have any users that are able to run BAQs especially updatable.
Actually, it IS in a dashboard and times out. I just had them test the BAQ directly to make sure that was the issue.
I was able to fix it finally, I had to bump the execution settings QueryTimeoutValue up considerably. We’ve had to do that before, but that was when the timeout was bad overall, this is the first time we’ve had to do it because of one group failing while others had super fast results.
I appreciate all the help though, it led us to trying more things until we figured it out
@MLamkin Glad you got it working. I wonder if @timshuwy nugget on OrderHed and Company from here applies to this baq.
It was an interesting read, thanks
I tend to not bother with company for the primary table in BAQs but use Company in all the joins. In this particular one, we need Company on the joins, but we want to see Orders regardless of which Company they might be in. (it’s purpose is for Debt to be able to see which Orders are on hold, and which ones have had a hold removal requested, which is what we are using UserChar2 for). It’s updatable so that the Debt person can release the hold. (the dashboard also has a button which will launch the credit manager screen for the appropriate company and customer.) That and a similar one for Quotes (which is also on the dashboard) are really the only Cross-Company BAQs we use, other than built in Epicor stuff. With the auto-refresh set for every minute, they can sit in the dashboard and manage the hold removal requests instead of having an email chain for each order.
I’m pretty sure that it’s just the join to customer where the company join slows things down. Since CustNum is unique to the whole database, you can usually get by without having company in the join to the customer table.
We have multiple Companies, and the key is Company, CustNum. Going into SQL directly, CustNum is NOT unique by itself. If it was, then yes, we could avoid using Company
Interesting! I thought it was. I stand corrected.
Kind of a side topic (mods feel free to split this off to a new topic)
Does SQL optimize a Query expression, or pretty much process it as written?
The expression posted (slightly reformatted for readability) is:
select
[OrderHed].[Company] as [OrderHed_Company],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[Customer1].[CustID] as [Customer1_CustID],
[Customer1].[Name] as [Customer1_Name],
[OrderHed].[UserChar2] as [OrderHed_UserChar2],
[UserFile].[EMailAddress] as [UserFile_EMailAddress]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer
on OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.Customer as Customer1
on OrderHed.Company = Customer1.Company
and OrderHed.BTCustNum = Customer1.CustNum
and ( Customer1.CreditHold = 1 )
left outer join Erp.UserFile as UserFile
on OrderHed.UserChar2 = UserFile.DcdUserID
where (OrderHed.OpenOrder = 1 and OrderHed.CreditOverride = 0)
order by OrderHed.UserChar2 Desc, OrderHed.Company, OrderHed.OrderNum
The WHERE clause applies to just one table (OrderHed), and the main tables are all joined with inner joins.
I’m no SQL expert, but that looks like a dataset is created based on the joins, then filtered by WHERE clause criteria, then sorted.
Would applying that criteria to OrderHed before all the joins reduce the number of matches required between OrderHed and Customer (as well as those between Orderhed and Customer1) speed things up?
Would the best way to do this be to make a subquery that first pares down the OrderHed table to just those records where (OrderHed.OpenOrder = 1 and OrderHed.CreditOverride = 0) Then use that subquery in place of the OrderHed table on the top level.
I’m assuming the expression above was generated for a design that used Table Criteria on OrderHed. Is that the case @MLamkin ?
Yes, the actual BAQ design is
If I were writing the raw SQL I’m sure it could be coded more efficiently, but I have found that in nearly all cases, SQL server itself is almost never where things are slow. I copied and pasted that code into a query window and tried it, the execution time displayed was 00:00:01, in otherwords, <= 1 second.
For this query, one could possibly speed it up by using the link from OrderHed to Customer1 (which is where the other credit hold factor we need is) and then linking to the other tables. And when we were testing, it’s the CreditHold field from Customer1 which is the big issue (there’s a warning in BAQ about security for it), removing a reference to it made the query work fast for everyone.
The combination of Cross Company and accessing that field
