Improving BAQ Performance

Epicor ERP 10
,
41

I think that’s what Rich was saying above.

For performance reasons, you want as much of the key (left to right) in your joins. I think those auto-joins are based on the primary index shown in the Data Dictionary Viewer and so they’re being helpful. This helpfulness has gone back to the Progress days IIRC. In the background, they were adding that join logic again but now it’s probably confusing SQL Server’s execution planner - that’s if I understand what Rich said above.

2 Likes
42

The addition of table1.company = table2.company can help with performance but it is essential for data integrity by Company. There is no guarantee that the natural key - OrderNum, JobNum, CustNum, Key1, Key2, etc. - are not duplicated across companies. By adding the company “match” criteria, the BAQ Engine is ensuring the data is correctly linked and data isolation by company is enforced.

43

I do something similar like ckrusen. IF it is not a Cross-Company BAQ my first table I dont set the Company = CUR-COMP I leave it alone, but joining from that table forward to other tables I always use Company = Company.

44

Rich - I tested today with a BAQ not adding Company = CompanyID in 10.2.600.6. When I run with BAQ Report just for two period it failed with Timeout error. Below are the results with and without company criteria: on first table. This is a All Companies BAQ.It looks like safer to add Company criteria .
Without Company criteria on first table:
Severity: Error, Table: , Field: , RowID: , Text: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out

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

With Company = CompanyID on first table:
Severity: Warning, Table: , Field: , RowID: , Text: Test results are forcibly limited to 10000 rows to prevent the application server memory overload. This limitation will not apply to the query execution outside of BAQ Designer. Please review the query design you test to make sure that this result is correct.
Query returned 10000 row(s).
Query has no more records to return.
Query execution total time: 4531.2815 ms.

1 Like
45

A problem with the way we were doing some of the sub-table joins was addressed in 10.2.600.8 under Jira Story ERPS-142960 to address the BAQ Timeout problem on larger data collections. Adding the explicit Company Criteria - as you did - helped with the Timeout issues but should not be necessary once you are running 600.8 or newer.

2 Likes
46

Sorry for the necro post ,
Interestingly doing a sql trace on a BAQ after I added the customer table. The following is added. to the SQL.

I thought this was interesting. This added significantly to the execution time.


exec sp_executesql N'declare @TerritoryLst table(Company nvarchar(8), TerritoryID nvarchar(8), primary key(Company, TerritoryID));

with [_TerrCTE_SalesRep]  as 
(
    select uc.Company as Company, sr.SalesRepCode, ISNull(sr.ViewAllTer,0) as ViewAllTer, (case when em.FeatureID is null then 0 else 1 end) as TrCrAllowed 
    from [Erp].[UserComp] uc
        left join [Erp].[SaleAuth] sa on sa.Company = uc.Company and sa.DcdUserID = uc.DcdUserID
        left join [Erp].[SalesRep] sr on sr.Company = uc.Company  and sr.SalesRepCode = sa.SalesRepCode
        left join [Ice].[SysCompany] sc on sc.Company = uc.Company
        left join [Ice].[EnabledModule] em on em.InstallationID = sc.InstallationID and em.FeatureID = ''00000003-9662-4B1D-AD67-8D90C3599092''
    where
        (uc.Company = N'''' or uc.Company = @CurrentCompany) and uc.DcdUserID = @CurrentUserID 
)
insert into @TerritoryLst

select st.Company, st.TerritoryID -- CRM disabled
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st on st.Company = [_TerrCTE_SalesRep].Company and st.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 0

union

select st1.Company, st1.TerritoryID -- CRM enabled and ViewAllTerr=1
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st1 on st1.Company = [_TerrCTE_SalesRep].Company and st1.Inactive = 0
where [_TerrCTE_SalesRep].ViewAllTer = 1 and [_TerrCTE_SalesRep].TrCrAllowed = 1

union

select st2.Company, st2.TerritoryID -- CRM enabled and no SaleAuth
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st2 on st2.Company = [_TerrCTE_SalesRep].Company and st2.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 1 and not exists(select top 1 1 from [Erp].[SaleAuth] sa2 where sa2.Company = [_TerrCTE_SalesRep].Company and sa2.DcdUserID = @CurrentUserID and sa2.SalesRepCode = [_TerrCTE_SalesRep].SalesRepCode)

union

select strp.Company, strp.TerritoryID -- CRM enabled and ViewAllTerr=0 and territories assigned
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTRP] strp on [_TerrCTE_SalesRep].ViewAllTer = 0 and [_TerrCTE_SalesRep].Company = strp.Company and [_TerrCTE_SalesRep].SalesRepCode = strp.SalesRepCode;
1 Like
47

Yes, there is an interesting post by @banderson here I believe which explains all about it.

https://www.epiusers.help/t/sql-for-running-queries-just-like-epicor/102127

48

That is interesting. No Varchar(max) in this. It gets added when you add the customer table to the BAQ…I understand, but, oh my word, it does slow things down somewhat.

49

I think they changed the MAX as well, so it doesn’t do that I believe. Olga said something about that.

2 Likes
50

Yes it appears so.