So have a query that is checking the differences on PartMaterial between two companies. I two have subqueries that give a list for one company and one for the other that are join in the top level to give me the differences.
As usual, running the phrase in SSMS is fast. I know Epicor doesn’t actually execute that query but I do somewhat expect that the difference in time to return should be minimal. I’m doing a Top 500 and SSMS executes in .01 where as the BAQ takes over 80 seconds.
Digging into why and using the SQL Profiler, I get the actual SQL executed. Every time a PartRev table is used in the query, Epicor appends a clause “Ice.lookup(PartRev.Plant, SysUserComp.PlantList, ‘~’) > 0” and that seems to be the culprit. Removing those where clauses and rerunning it makes the query run as expected.
Could you create a SQL view and use an External BAQ to hit it? I have found this a useful option when getting timeouts on queries or excessively long processing time through the BAQ designer. The views/external queries have a drawback of being yet another concern to maintain when upgrading but when it has been truly needed, I think it is worth it.
I try to avoid the External when it can be query via the Internal BAQ and I try to keep it self contained so we don’t have to maintain it when we do restores. I try to leave those for data sources that don’t exist in Epicor. But it may end up there if I can’t find another resolution.
I’ll after doing a quick test using the same where clause, just querying the PartRev table in SSMS, it took 4 seconds to read 67k rows. That’s terrible!!
Select * From
Erp.PartRev
WHERE ( [PartRev].[Plant] is null or Exists (select 1 [c1]
from [Ice].[SysUserComp] [SysUserComp]
where [SysUserComp].[Company] = [PartRev].[Company] and [SysUserComp].[UserID] = N'myusername'
and Ice.lookup(PartRev.Plant, SysUserComp.PlantList, '~') > 0
))
Like @josecgomez said, this restricts the user to the plants they have access to. Epicor can also add more filters for tenancy (for mutli-tenant users), and sales territories for those using the CRM module.
I have found that by strategically using subqueries (like you would in SSMS) and use links like cover the indices for that particular join target, I get pretty good performance from the BAQ Designer.
In your two subqueries… (not sure if you currently using this way,… ) could you try to force your company by adding Company = “CompanyA” for one query and Company = “CompanyB” for the other as an added table restriction on your first object? and test it…
We found that by adding Company = BAQ CurrentCompany it improved a lot the speed of our queries… due to the table indexes…I assume.
I am just curious if it would do the same for you…
I did move the PartRev queries to subqueries. I got a little better performance but still light years apart from running it without the security in SSMS. Like @josecgomez said, there is nothing I can do in the BAQ designer itself so I’ll have to weigh my options with @Nancy_Hoyt suggestion.
And I think I’ll be posting a Feature Request to have a “View All Data” permissions on the user that would bypass the security when running the BAQ. This is for internal integration query that is never and will never be exposed to our other users. I would like to see a little more flexiblity on when we want the security to be applied. Some may cringe at that but it already can be bypassed by using an External BAQ.
