Hello, we have just upgraded from 2022.1.9 to 2024.1.19. i have a BAQ that was working properly before but is now giving me a timeout error and not pulling all the info.
I would appreciate any advice on what to look for or any ideas why it might not be working,
thanks,
Sounds like an index issue on the SQL side. Rebuild all your indexes and statistics and see if that makes it better.
If you had any custom indexes in your database before, check to make sure they are still there. I remember rumors of Epicor removing custom indexes when upgrading (probably more like deleting all of them, and re-adding the stock ones, and it didn’t know what indexes you added).
Outside of that, you’re going to need to provide a LOT more information than what you provided in order for anyone to help you. We have no idea what your BAQ is doing or how it’s made, so it’s really hard for anyone to be able to tell you why it doesn’t work.
ok thanks for that, i don’t think we have any custom indexes. I started to rebuild the top level query because all the subqueries were working when i tested them. if it still doesn’t work for me. i will export and upload the BAQ on here
so here is the exported BAQ.
ShipDash_Mainupd.baq (152.5 KB)
it is a dashboard for all our shipments (TOs and SOs) with info about the number of lines, open jobs and an inventory check.
let me know what other info would be helpful to root cause this as i am a bit unsure what to do next other then completely restarting
thanks,
Did you look at the event logs on the server yet?
not yet but will do
check your Query Timeout settings on your app server. It reset mine to 0 when I upgraded to 2024. I think being set to 0 actually times out at 30 seconds.
Also, try making these UD Columns table criteria into calculated fields and in the case of an empty string, put something else there. It’s quicker for a computer to find something than nothing.
Like Brandon said, it could be a lot of things. You might need better keys on your UD09 so it’s joined by something other than just Company.
thanks for that. ours was set to 0 for the query timeout settings. what do you recommend setting it at?
I have updated it so it isn’t filtering on the empty
thanks for your help
whatever floats your boat. I set mine to 300. Don’t forget to recycle the app pool or else it won’t apply!
cool. i will talk to my system admin to try this out. I was going through my other queries and found another 5 that are giving me the issue.
I just finished wrestling with a similar issue. In my case, the underlying BAQ wouldn’t complete stating “Bad SQL query” and to check the server logs. This invariably leads me to an hour or two of trying to figure out why it won’t run using SQL Server but to no avail. When I checked the server logs, the errors stated
Ice.Common.EpicorServerException: Query Id: Forecast_ASP. The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Next I tried setting the SQL Timeout on the server to 3600 and regenerating the data model, but still no dice. In the end, my solution was to add “tableID.Company = @CurrentCompany” as criteria to every table, and the BAQ and dashboard immediately ran like a champ.
Cheers, Tony G.
Treat this as a +1 to @tonygardner reply in this thread. I experienced a similar issue, BAQs that were performing just fine on a previous version were timing out all of a sudden on 2024.1.
My approach was similar to Tony’s except for the fact that I have been ensuring that Company is included in all joins (if possible), and then adding the following Table Criteria to the “main” table in the query: Company = @CurComp.
Any BAQ that has started to randomly timeout after our upgrade has been getting this treatment, and we’ve seen huge performance gains doing this. To quantify this, I had a BAQ that on our previous version (E10) would typically run in ~60 seconds. Some time after upgrading to 2024.1, it was reported that the Dashboard that depended on this BAQ was not working. Turns out this BAQ was now taking 40 minutes to run. Applied previously mentioned @CurComp criteria, that same BAQ now runs in ~4 seconds.
That smells like a SQL indexing issue. Sometimes you just need to change the query enough to get it off of a bad plan.
One of the new features is to be able to add option(recompile) to your query. Using the execution settings. This doesn’t always fix the issue, but when it does, the results can be dramatic.
While we’re talking about indexes, If you haven’t yet, go give this idea a vote. I don’t know why Epicor hasn’t jumped on this low hanging fruit to improve their database performance, but it would help if they did a revamp of the indexes in the database.
I would also suggest using alternative company security in execution settings. It also may change the order of data retrieval by SQL server and affect the results
