PartAlloc strange results in baq execution plan

Kinetic ERP
1

I have been trying to chase down some performance issues and came across something really odd. I was able to reproduce it by making a new baq, and adding only PartAlloc to it, then generating the execution plan. Why does the execution plan show this when I have no table or subquery criteria in my baq? Its extremely inefficient. Even if I add a criteria for company = current company it just appends that on, it doesn’t replace this. Has anyone seen this before or have any idea how to make it stop? Cloud 2025.1.12

;from [Erp].[PartAlloc]
where ( [PartAlloc].[Company] is null or [PartAlloc].[Company] = N'' or [PartAlloc].[Company] = @CurrentCompany )"
2 Likes
2

Looks like company security. What happens if you turn on MultiCompany for the new BAQ?

1 Like
3

Do you mean all companies? or cross company?

1 Like
4

Oooh, try All Companies first.

1 Like
5

If I check cross company, it drops off the =current company portion but keeps the rest.

2 Likes
6

No change with All Companies.

2 Likes
7

All companies just means you can run the baq from any company. It still only ever gives you results for the current company context so those results don’t surprise me.

2 Likes
8

Hmm. I would expect similar additions for site security and territory security.

But why test for blank or null company? Might this be a holdover from older, upgraded systems from the V6 days? I don’t see any PartAlloc records missing these fields but I haven’t checked all tables either…

1 Like
9

This is a new implementation

1 Like
10

Right. I mean was this code added back in V8 to handle cases where there wasn’t a company ID filled in on every record?

2 Likes
11

Something similar / quasi related to this I would imagine.

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

2 Likes
12

Blank company can be in some tables, especially old ones, meaning all companies data.
Company is null may happen in left outer joins, when second table does not have such row.

4 Likes
13

Why not remove records with blank company values during conversion? There is no valid reason for a record in PartAlloc to not have a company value? Vs. hindering performance of every partalloc baq forever?

2 Likes
14

Company security always adds it, it does not know about hundreds of tables it can be used with

1 Like
15

That antipattern is not added everywhere though. I am only seeing it on partalloc. Its a severe performance problem for me right now.

2 Likes
16

Try to use Alternate company security, it changes how the second table is joined, maybe it will help in your case.

1 Like
17

I don’t understand what this means? To be clear, there is no join in my sample query. I can reproduce the issue just by querying partalloc by itself.

1 Like
18

ok, then it does not matter.
Anyway, this clause is always added, unless you create external query, that you cannot do in the cloud.

19

Why. Its redundant with company = current company and its hindering the performance of the baq.

I feel I am caught in a catch 22 here. I complain about cloud performance. Cloud ops blames my baq. When I run the execution plan, the items with the biggest impact are:

  1. Missing indexes (can’t do anything about this in cloud)
  2. Outdated statistics (can’t do anything about this in cloud)
  3. Antipattern in criteria (apparently can’t do anything about this in cloud either)

So . . . cloud ops won’t address performance impact of baqs even though the tools are not provided to improve the performance of the baq in the cloud.

Now what?

2 Likes
20

Company is always indexed, in most cases it is part of clustered index as well. Also when you move on with additional tables and filtering then maybe SQL server will be able to find even better order to filter data.

But if your goal to get only the data from this one table, then there is what you can have in BAQ.

If cloud team complains about this query exactly, then maybe you return too many row and you should not do it?

1 Like