Generic Inquiry Changes in Acumatica 5.3 explained
In Acumatica version 5.3 you may have noticed some mysterious warnings appearing on your generic inquiries. I could not find any documentation on why this started showing up. After a little digging this is what I discovered.
Problem
Let’s start with a simple test generic query.
Just use the SOOrder table
On the Results Grid add OrderNBR and CustomerID_Description and you will see the warning.
Generic Inquiry has automatically joined to the BAccount table to get the Account Name (customerID_Description – BAccount.AcctName) to be displayed.
Acumatica created the following SQL Statement to retrieve the SOOrder records. Not so easy to understand, lots of things going on.
SELECT TOP (33) [SOHeader].[OrderType] [SOHeader_OrderType], [SOHeader].[OrderNbr] [SOHeader_OrderNbr], [SOHeader].[CustomerID] [SOHeader_CustomerID], [SOHeader].[NoteID] [SOHeader_NoteID], (SELECT TOP (1) [NoteText] FROM [dbo].[Note] WHERE ([dbo].[Note].CompanyID IN (1, 7) AND 32 = SUBSTRING([dbo].[Note].CompanyMask, 2, 1) & 32) AND [Note].[NoteId] = [SOHeader].[NoteID]) [SOHeader_NoteText], (SELECT TOP (1) COUNT(*) FROM [dbo].[NoteDoc] WHERE ([dbo].[NoteDoc].CompanyID IN (1, 7) AND 32 = SUBSTRING([dbo].[NoteDoc].CompanyMask, 2, 1) & 32) AND [NoteDoc].[NoteId] = [SOHeader].[NoteID]) [SOHeader_NoteFiles], [SOHeader].[CuryID] [SOHeader_CuryID], [SOHeader].[CuryInfoID] [SOHeader_CuryInfoID] FROM SOOrder [SOHeader] WHERE ([SOHeader].CompanyID = 7) AND ([SOHeader].BranchID IS NULL OR [SOHeader].BranchID IN (5, 6, 10, 11, 12, 13)) ORDER BY [SOHeader].[OrderType] ASC, [SOHeader].[OrderNbr] ASC OPTION(OPTIMIZE FOR UNKNOWN) /* 00.00.00.00 */
To simplify, we can manually create a simplified SQL statement that will provide what we need to see to understand what is Generic Inquiry is doing.
First it returns all of the SOOrder header records.
Select OrderNbr, CustomerID From SOOrder Where CompanyID = 7 Order By SOOrder.OrderNbr
Next, it then loops through each of the customer ids and retrieves the AcctName. It caches the results so that for each name it only does the BAccount look up once.
Select AcctName From BAccount Where BAccountID = 2470 And CompanyID = 7
From BAccount Where BAccountID = 260 And CompanyID = 7
Select AcctName From BAccount Where BAccountID = 262 And CompanyID = 7
Example of this in SQL
With bigger record sets this can get very inefficient.
Solution
So how do we improve the performance and get rid of the warning. Easy!
We add the BAccount table to the Tables tab on the generic inquiry.
Create the relations between the Tables (join).
Replace the default customerID_Description with AcctName from the BAccount Table and warning disappears.
Now when Acumatica executes the query it runs the equivalent of:
Select SOOrder.OrderNbr, BAccount.AcctName
From SOOrder Inner Join BAccount On SOOrder.CompanyID = BAccount.CompanyID
And SOOrder.CustomerID = BAccount.BAccountID
Where SOOrder.CompanyID = 7
Order By SOOrder.OrderNbr
A much more efficient SQL query. Mystery solved.
