A user came to me yesterday and told me that a BAQ they were trying to create was timing out. She was wanting to make an updatable BAQ to change some customer information, so the query was pulling from just one table. Figuring she was doing something wrong, I made a new BAQ myself and it times out too. Here is the query phrase:
select
[Customer].[Name] as [Customer_Name],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Zip] as [Customer_Zip],
[Customer].[Country] as [Customer_Country]
from Erp.Customer as Customer
I have seen some other posts here about BAQ timeouts and I tried some of the workarounds but nothing is working. I really don’t want to change the timeout, since this is something that should not take longer than 30 seconds to execute. I am at a loss here.
Not sure if that’s the full query or not, but there’s no “where clause” so it’s attempting to return every customer in the database - is that the intention?
Try removing the Country column and run again. That is the only linked field in the query and maybe there is something going on there. After that remove columns one at a time test, repeat until it runs. I have over 8000 rows return in 800ms
@ckrusen - Yeah, I noticed that actually. I re-ran the query with no changes and it was quick. I figured that was caching though. The BAQ that was made is going to be a uBAQ, but she didn’t have that checked yet because she was just working on building it first. I turned on tracing and I don’t notice anything that looks wonky, though admittedly I have never looked at an Epicor trace until now.
The way Epicor translates a BAQ to an actual SQL query is hard to determine… but yes, unless you want to query multiple companies at the same time, filtering on the actual company is a GREAT help.