I have a somewhat complicated BAQ. When I run it wide open, it goes kind of slow. Maybe 20-30 seconds. It’s tying Invoice Lines to a subquery that calculates who should get commission on the lines based on several factors that I’ve setup in a UD table. All of that seems to work OK - just takes a little longer than I’d like. I could live with 20-30 seconds load time but the real issue is that when someone filters it to a specific date range, it bumps the time astronomically. So for a filtered query that might return <5% of the rows, it takes 5x longer to execute. Is there some fundamental SQL law that I’m violating? I just want to filter based on invoice date.
I’ve struggled with this a good bit lately. My toolbox:
*Run the query in SSMS. If its slow there it isn’t really an Epicor problem. You’ve just got to rethink the query.
*I’ve seen really weird, slow behavior when filtering on unindexed columns, especially coupled with results that include UD columns. Try to use indexed fields where possible.
*The last one of these I ran into, it was an epicor problem, not sql. I ended up putting the whole part table as a subquery, joined that. Made query 20x faster.
*You might look at adding indexes, though I’ve been able to avoid doing this.
Good luck
1 Like
try making a CTE subquery that retrieves all the invoices, and then have another sub-query that filters the CTE for the dates. (just a suggestion… not sure if that would work).
2 Likes
Can be a lot of things. Quickest way is to check the query plan in SSMS and see what’s taking so long.
If you have access to SQL, you can do a reindex of the tables involved in the query and see if that speeds up things. Looking at the InvcHead table, I can see an index with InvoiceDate but it also has the Company in it. So make sure you sort by Company first and then Invoice Date, just like the index. Also, removing stuff one by one (sorts, filters, etc.) and then testing it each time might help identify what’s wrong.
1 Like
I was very fortunate that by creating a CTE that contained just my invoice data filtered down, then joining that to the other tables/subqueries… it helped tremendously. Thank you for the suggestion.
1 Like
Seriously… like the difference between dial up and Fiber
I am very glad this worked for you. I think that we have not promoted the use of CTE queries enough. I always thought that the CTE queries were only for creating recursive queries, but that is only a minor reason to use them.
1 Like