BAQ with PartPlant issue

[danielc0877 (Daniel Cardoso)]

Hi,

I am building an updateable BAQ with the PartPlant table. My database is fairly small, only about 8,000 records in that table but the BAQ would take 30 sec + to run. If I take the SQL statement and run it directly on the SQL server, it takes about 1 second… It took some time to realize that Epicor is actually checking what plants the user has access to while running the query. And it uses a couple of SQL functions which are performance hogs. The issue is that we have 60 plants…

Is there a way to “bypass” the “plant access check” and thus returning the rows regardless of whether the user has access to the plant or not.

Thank you,

Daniel

[Asz0ka (Adam Szoka)]

I just saw a post about this, maybe an outer join would help.

Learned this today - BAQs and sites

For the multi-site people out there. I made a BAQ to show transfer orders. I was hopping mad when site A could not see site B stuff. I learned that if you use the PartPlant, PartRev or Warehse tables in your BAQ (and inner join), the BAQ is restricted to only the sites that the user has access to. This is documented in a table called zDataTable - note the RestrictedBySite column: [image] (I recall others hitting this with territories. Notice the column to the left of the site column.) Cre…

[timshuwy (Tim Shoemaker)]

Also see: BAQ Speed Improvement TIP

[danielc0877 (Daniel Cardoso)]

I must have a wrong setting. If I do a simple BAQ with just PartPlant and retrieving 6 fields, the BAQ takes about 30 seconds to retrieve about 8,000 records. The same SQL statement on the server itself take less than a second for the same 8,000 records.

And when I trace the BAQ with SQL Server Profiler, I see it calling the SQL funstion Ice.lookup() quite a few times which is doing quite a few reads…

[danielc0877 (Daniel Cardoso)]

So by digging deeper with SQL Profiler, it seems that a records in the [Ice].[ZDataTable] is governing whether or not Epicor is checking the plant accesses of the user when running the BAQ. By running this SQL statement: UPDATE [Ice].[ZDataTable] SET [RestrictedByPlant] = 0 WHERE [DataTableID] IN (‘PartPlant’), the query is now only taking a few ms to run.

So I fixed the query performance but the question is: am I breaking anything else?

[jdtrent (Joe D Trent)]

I’m not a SQL guy, so some of my theories might not hold up. But one of the things I try to do in BAQs is add table criteria where possible, in addition to the join.

Say you’re inner joining something like PartTran to PartPlant, where you’re already filtering PartTran for CurrentPlant. But he PartPlant table still has to look up all the PartPlant records even it only returns the current plant on the join. My theory is that if I also put a table criteria on PartPlant for CurrentPlant it won’t have to do that unnecessary work.

In this case, maybe it also doesn’t have to do all that plant permission checking.

Comments welcome.

Joe

[timshuwy (Tim Shoemaker)]

you are breaking security by Site (aka Plant). If you are single plant, then this is not a problem. otherwise, users could edit data in a site that is not in their allowed list in the user table.