Getting this error:
BAQ execution returned errors:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I can still access the server and it seems to still be running fine. Any ideas?
There is a BAQ option for the query timeout I have had to set. It is on the BAQ screen, under Actions -> Excecution Settings. You add a new parameter, and choose queryTimeout and one other one I usually have to set in there (I am not at my desk, so cant give specifics)
2 Likes
There is a timeout set on the Application Server by default it is only around 30 seconds (generally not enough for a complex BPM)
1 Like
I tried al of those and got nothing. However I did dig some and noticed that this report will work for any date range EXCEPT the month of June. Tried all different length of date ranges in June and got the exact same issue. However, anything outside those dates are good. I find that very odd.
I noticed also that I hadn’t set the limit to as high as what you recommended. When I did, it took 5 minutes, but it worked.
Will,
If this is a report from a BAQ, you might want to review the BAQ and make sure the BAQ is optimized. I have seen some create joins when none are defaulted and sometimes the join is not on primary key’s that are indexed and while it may test ok, once you get a lot of data, performance becomes an issue. An example of this is joins or criteria on CustID instead of CustNum. Just last week I fixed a BAQ that was producing the same type of error took it from timing out to less than 5 sec. While adjusting the timeout can get the report to run, I would review and look for ways to optimize the BAQ, this would be better for the end user and your DB.
1 Like
What do you think on this:
[Customer].[Zip] as [Customer_Zip],
[Customer].[Country] as [Customer_Country],
[Customer].[PhoneNum] as [Customer_PhoneNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[InvoiceType] as [InvcHead_InvoiceType],
[InvcHead].[FiscalYear] as [InvcHead_FiscalYear],
[InvcHead].[FiscalPeriod] as [InvcHead_FiscalPeriod],
[InvcDtl].[ExtPrice] as [InvcDtl_ExtPrice],
[InvcDtl].[DocTotalMiscChrg] as [InvcDtl_DocTotalMiscChrg],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[LineDesc] as [InvcDtl_LineDesc],
[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty],
[InvcDtl].[SalesUM] as [InvcDtl_SalesUM],
[TranGLC].[SegValue2] as [TranGLC_SegValue2],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum]
from Erp.Customer as Customer
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
And
Customer.CustNum = InvcHead.CustNum
and ( InvcHead.InvoiceType <> ‘DEP’ and InvcHead.InvoiceType <> ‘ADV’ and InvcHead.Posted = TRUE )
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
And
InvcHead.InvoiceNum = InvcDtl.InvoiceNum
and ( InvcDtl.ExtPrice <> 0 )
inner join Erp.TranGLC as TranGLC on
InvcDtl.Company = TranGLC.Company
And
InvcDtl.InvoiceNum = TranGLC.Key1
And
InvcDtl.InvoiceLine = TranGLC.Key2
and ( TranGLC.RelatedToFile = ‘InvcDtl’ and TranGLC.SegValue2 >= ‘10’ and (TranGLC.SegValue1 = ‘40000’ or TranGLC.SegValue1 = ‘40300’ ) )
inner join Erp.ProdGrup as ProdGrup on
InvcDtl.Company = ProdGrup.Company
And
InvcDtl.ProdCode = ProdGrup.ProdCode
and ( ProdGrup.ProdCode = ‘226’ or ProdGrup.ProdCode = ‘226B’ or ProdGrup.ProdCode = ‘226C’ )
Will,
Your joins are good, a few changes I would try to see if it helps, make a copy first:
- Use an IN operator on the ProdGrup.ProdCode instead of OR’s
- For the InvcHead.InvoiceType you can use NOT and the IN Operator instead of the two AND’s
- You are hitting the TranGLC table, one of the larger tables in the DB, that will be expensive no matter what, but the joins look ok.
- See if it is reasonable to limit the results to a shorter time period, sometimes that can help especially if they only need YTD or last 12 months or something, especially as time progresses. Eventually the network transport cost becomes higher than the query cost.
You could play around with the order of the joins, while the query optimizer is supposed to construct the best execution plan, sometimes you can get better performance by moving the most limiting criteria towards the top, however this can also sometimes yield an unintended data set. If you are including a table just for referenced data (like customer) you can usually move it down.
Will -
Just to eliminate a couple more variables have you attempted to run the SQL code, you posted above, for the June date range and run it using SQL management studio? If that is slow you can kind of narrow your focus more to the back-end of the system (Indexing, data file growth, Tempdb). Typically, when I encounter something that is focused just on a particular slice of data it turns out to be a fragmented index or possibly some bad data (which is harder to pinpoint).
1 Like
What do you think of mines? How could i speed this up?
select
[Vendor].[VendorNum] as [Vendor_VendorNum],
[Vendor].[Name] as [Vendor_Name],
[POHeader].[PONum] as [POHeader_PONum],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
[RcvDtl].[VendorQty] as [RcvDtl_VendorQty],
[RcvDtl].[VendorUnitCost] as [RcvDtl_VendorUnitCost],
[Part].[RefCategory] as [Part_RefCategory],
[RefCategory].[Description] as [RefCategory_Description],
[RcvDtl].[PartNum] as [RcvDtl_PartNum],
[RcvDtl].[PartDescription] as [RcvDtl_PartDescription]
from Erp.POHeader as POHeader
inner join Erp.RcvHead as RcvHead on
POHeader.Company = RcvHead.Company
and POHeader.PONum = RcvHead.PONum
and ( RcvHead.Plant = ‘MfgSYs’ )
inner join Erp.RcvDtl as RcvDtl on
RcvHead.Company = RcvDtl.Company
and RcvHead.VendorNum = RcvDtl.VendorNum
and RcvHead.PurPoint = RcvDtl.PurPoint
and RcvHead.PackSlip = RcvDtl.PackSlip
and ( RcvDtl.ReceiptDate >= ‘03/01/2020’ and RcvDtl.ReceiptDate <= ‘03/31/2020’ )
inner join Erp.Vendor as Vendor on
RcvDtl.Company = Vendor.Company
and RcvDtl.VendorNum = Vendor.VendorNum
inner join Erp.Part as Part on
RcvDtl.Company = Part.Company
and RcvDtl.PartNum = Part.PartNum
inner join Erp.PartCost as PartCost on
Part.PartNum = PartCost.PartNum
inner join Erp.RefCategory as RefCategory on
RcvDtl.Company = RefCategory.Company
inner join Erp.RefCategory as RefCategory
and
Part.Company = RefCategory.Company
and Part.RefCategory = RefCategory.RefCategory
I don’t like the three way join between RcvDtl, Part, and RefCategory…
Also, your join to the partcost table doesnt have a Company field join, which it should have. Also, the primary key to PartCost also needs a CostID. On many systems, the only cost ID is “1”, but Epicor supports multiple cost ID’s, so if you do as well, you also need to look in the Plant table to find out which cost ID you are using…
Assuming a cost ID of 1, you need to add a table criteria of CostID = 1
3 Likes
We noticed the same when we went from 10.0 to 10.2, having a few of our dashboards not showing data. All due to the timeout expired error.
By adding the timeout option to augment to more than 30 sec. did the trick to a few BAQ’s but what really resolved it was to make sure the joins were done using a valid index, in the order specified preferably… I was not making sure of that previously…but since then… queries are run much faster!
My coleague created an sql query that easily show the indexes of a particuliar table “ObjectID”
SELECT indexes.name, indexes.type_desc, indexes.is_unique, index_columns.key_ordinal, columns.name, CASE WHEN key_ordinal = 1 THEN '-------------' ELSE '' END
FROM sys.indexes
INNER JOIN sys.index_columns ON indexes.object_id = index_columns.object_id AND indexes.index_id = index_columns.index_id
INNER JOIN sys.columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id
WHERE indexes.object_id = OBJECT_ID('Erp.ShipHead')
ORDER BY indexes.name, key_ordinal;
Pierre
1 Like
Or one can use The Data Dictionary Viewer which displays the same information.
2 Likes
Thank you guys!
ohhh, thanks Mark, I did not notice the indexes were shown there !
Good to know!