wow. Did you add index in SQL ?
I too have noticed difference between using parameters “CurComp”, “CurrentCompany” and a hard coded value for CompanyID. I found that if you hard code ON EVERY TABLE, and not JOIN that it actually works faster in some cases.
Not a sustainable development method in my opinion. 
Yup
The two items that make standard BAQs slower than external BAQs are normally the two table valued variables that are created for security. If you look at the query execution plan from the BAQ designer you can see the first thing it does is create a fill two variables. The company list is for company security and is often ignored by SQL by setting Company = CurrentCompany on your first table. The TerritoryLst table is brought in for order and customer security. It can be removed by unchecking “Territory Security on Sales Orders” in Company Config.
The other piece that can make the standard BAQ slower is I think it will always use the dbo view if there are custom fields.
I always try to make the standard BAQ work before jumping to an external. They don’t work as well in dashboards without a bunch of extra work and they are confusing if you aren’t expecting them.
As an example this:
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[Customer].[Name] as [Customer_Name]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.BTCustNum = Customer.CustNum
turns into this:
Expand the summary to see the mess…
Summary
(@_compList [Ice].[QueryParamTableType] READONLY,@CurrentUserID nvarchar(max) ,@CurrentCompany nvarchar(max) )
declare @AvailCompLst table(Company nchar(8) primary key);
insert into @AvailCompLst values(N'')
;
insert into @AvailCompLst(Company) select ParamValue from @_compList
;
declare @TerritoryLst table(Company nchar(8), TerritoryID nchar(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
;
--queryid: test, query companyid: EPIC06, userid: epicor, user companyid: EPIC06
--baqd39da1ed6-f162-43e9-a92c-6c8f4059fded;
SET DATEFORMAT mdy;
select [OrderHed].[OrderNum] [OrderHed_OrderNum],[Customer].[Name] [Customer_Name]
from ( [Erp].[OrderHed] inner join @AvailCompLst [AvailCLst_OrderHed] on [OrderHed].[Company] is null Or [OrderHed].[Company] = [AvailCLst_OrderHed].[Company])
inner join ( [Erp].[Customer] inner join @AvailCompLst [AvailCLst_Customer] on [Customer].[Company] is null Or [Customer].[Company] = [AvailCLst_Customer].[Company]) on ([OrderHed].[Company] = [Customer].[Company] And [OrderHed].[BTCustNum] = [Customer].[CustNum]) and ((([Customer].[TerritoryID] is null or [Customer].[TerritoryID] = N'') or exists (select 1
from @TerritoryLst [__TerritoryLst]
where [__TerritoryLst].[Company] = [Customer].[Company] and [__TerritoryLst].[TerritoryID] = [Customer].[TerritoryID]
) or exists (select 1 [c1]
from [Erp].[ShipTo] [ShipTo]
inner join @TerritoryLst [__TerritoryLst] on [ShipTo].[Company] = [__TerritoryLst].[Company] and [ShipTo].[TerritoryID] = [__TerritoryLst].[TerritoryID] Or [ShipTo].[TerritoryID] is null Or [ShipTo].[TerritoryID] = N''
where [ShipTo].[Company] = [Customer].[Company] and [ShipTo].[CustNum] = [Customer].[CustNum]
)))
1 Like
Thanks @Banderson. I was hesitant to add the index. Since you have you already done, I can confidently add it.
The BAQ performance inconsistencies with adding or not adding the “Company = CurrComp” criteria, have been resolved with work done in 10.2.700. Prior to that work, the BAQ engine would automatically use a Temp Table it populated with valid Company values for the query and the TT would be “joined” to the Company column. Depending on what other criteria was in play, that join could result in sub-optimal performance.
The BAQ has to account for a number of different scenarios including Cross Company BAQs and BAQs against tables where the Company column can be empty (generally Ice Tables where an empty Company value is an indication the record is available to all companies) so syntax and constructs that may at first blush appear simple, are not necessarily so…
11 Likes
I have had some pretty complex queries - one with eight sub-queries that were slowing down.
When I switched the joins between tables to left joins, the queries sped up.
Didn’t have to make sense, but I am now left joining more tables and getting good results.
1 Like
Doing a left join bring back all records for the “left” table. You must then check for null values to reduce the list.
This is all very interesting. Before now I’ve never questioned the Epicor dogma that says, “always join table1.Company = table2.Company”.
We are single-company, and I understand that something ought to be done with the Company field in many BAQs in order to future-proof the query results’ data structure (in case we ever go multi-company). But from a performance perspective, I had always assumed the Company join was only encouraged because the field is part of SQL indexes, as @josecgomez was discussing upstream in the comments.
But as @Rich points out, the situation seems to be more complicated…
I hesitate to ask what is “Best Practice” (since my response to those types of questions is usually “test and find out!”), but I’m wondering if the Epicor folks are willing to provide general guidance on when to join on Company vs use table criteria, etc.? (especially if the changes made for 10.2.700 affect that advice)
Again, I appreciate/understand that the best answer is going to be “test and measure in your environment”, but if @timshuwy 's experiences of better performance w/ table criteria over joins is that widespread, does it make sense that we start doing that by default on new BAQs?
For the integrity of data isolation by Company, the BAQ execution engine will always apply Company criteria. Adding your own Company criteria may change the SQL query plan potentially resulting in performance differences - both positive and negative - but the Epicor injected criteria will always be present.
For overall ease of maintenance, BAQ flexibility (Cross Company, ICE Tables, etc.), and being able to take advantage of software updates (Epicor and SQL), my suggestion would be to not add your own Company criteria. That said, it is hard to argue with positive gains achieved by adding your own criteria. If you do decide to add your own Company criteria, you should revisit that decision when you take new ERP releases or upgrades to SQL.
5 Likes
@knash - when I was talking about a left join, it was if you were joining customer to Orderhed or Orderdtl to Orderhed. It actually runs faster when you use a left join.
If you are attempting to filter the “Left” table with the “right” table, then the inner join is the correct tool.
Is that “built-in criteria” based on the companies the user has access to? Or is there some other magic happening that knows when a BAQ is multi-company, and what these criteria should be?
For example, if the DB has companies A, B, C, and D, and I am a user of co’s A and B, wouldn’t a BAQ of the part table (with no criteria applied), show me just part records for co’s A and B?
Now if I join the parttran table to part, and don’t include the companies in the join relationship, wouldn’t the results include parttrans for Co B for parts in Co A (and vice versa)?
If you have just the one company, or no users have access to more than one(highly unlikely), then there’d be no need to join or specify the companies in the BAQs. But as someone mention future proofing, wouldn’t adding another company, or giving rights to multiple companies, make all your BAQs that lack company joins or table criteria, now return mixed data from different companies?
I’ll take a stab at this:
Yes.
Only if the BAQ was set up as Cross-Company. If not, user only sees the records for the company currently logged into.
This depends on the Cross Company flag on the BAQ. If not set, it acts like like a single company query adding the currently logged in company to the query.
Again, only if you enable the Cross-Company flag on the BAQ. This is true whether or not you include the company link.
So if the BAQ is not cross-company, company relationships are not only unnecessary, but can cause performance issues.
So why does the Query builder automatically add the table1.company = table2.company relationship when you add a linkable table (like if OrderHed is in the BAQ, and you add OrderDtl)?
1 Like
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
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.