BAQ Timeout for Simple Query


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:

	[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?

Yep, that’s the full query and that’s the intention. There are only 462 rows in the customer table.

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

It shouldn’t matter, but try adding field CustNum (might be CustomerNum) as that is one of the indexes


The primary key is Company, CustNum


And most keys that refers to Country, uses CountryNum

Adding CustNum and removing Country at least made the query not timeout, but it still took 28 seconds.

The 28 sec is was the results of using the BAQ’s Test button ?

Do you have access to the SQL Servere manager? Could run the same query there, to see if its in the Client/E10 App server.

And might tracing be enabled?

1 Like

Yes, that was using the BAQ test button.

Running the query directly in SSMS is basically instantaneous (00:00:00).

Tracing is not enabled.

The original user was making a uBaq. Was your tests from a regular BAQ (i.e. non-updatable)?

Also, any BPM’s that could be getting in the way? Turn on tracing to see.

Did you add company as well?

FWIW - the Query Execution time can be misleading.

If I change the Query (say just add the field TermsCode, and click test, I get

Query returned 1318 row(s).
Query has no more records to return.
Query execution total time: 117.9414 m

Clicking test again (with no changes) and I get

Query returned 1318 row(s).
Query has no more records to return.
Query execution total time: 27.5502 ms.

After removing the TermsCode field, I get

Query returned 1318 row(s).
Query has no more records to return.
Query execution total time: 30.6655 ms.

So only the first test after making changes additions should be used.

@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.

@Banderson - I also added Company as well, yes.

Add a filter on = BAQ SpecialConstant (CurComp). I am pretty confident it should be instant afterward.

1 Like

@Alexandre_Pothier - Oh wow. That did it. 271ms to run the query returning the entire table.

Edit: Any idea as to why that made such a huge difference?

Do you have more than one company in the DB?

Maybe other companies were setup, but no Customer records ever created…

@ckrusen - Nope, we are a single company.

1 Like

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.


I will definitely keep that in mind. Thanks so much for the help!