BAQ with PartPlant issue


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,


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

Also see: BAQ Speed Improvement TIP

1 Like

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…

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?

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. :slight_smile:


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.