A Made2Manage customer called me with a serious performance problem with one of his custom reports. After I looked at it, I realized that the inventory changes in M2M Version 6.0 were the culprit. To give you some idea of his problem, the query in question ran took a few seconds before the migration and then jumped to a whopping 2.5 minutes afterward.
Since M2M Support won’t support custom reports, who you gonna call? Ghostbust….. Well no, actually me.
What was the problem with M2M 6.0?
I remembered from Consona Connect 2008 that the inventory master table(INMAST) was essentially replaced with a sub table INMASTX and converted into a view. However, at that time, nobody could/would tell me why. Well, take a look at the create statement for the view:
/****** Object: View [dbo].[inmast] ******/ USE [M2MDATA01]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- Create new view and instead of trigger based on the renamed table. CREATE VIEW dbo.inmast AS SELECT * , dbo.GetItemOnHandQuantity(fac, fpartno, frev) AS fonhand , dbo.GetItemInspectionQuantity(fac, fpartno, frev) AS fqtyinspec , dbo.GetItemNonNetQuantity(fac, fpartno, frev) AS fnonnetqty , dbo.GetItemInProcessQuantity(fac, fpartno, frev) AS fproqty , dbo.GetItemOnOrderQuantity(fac, fpartno, frev) AS fonorder , dbo.GetItemCommittedQuantity(fac, fpartno, frev) AS fbook , dbo.GetItemLastIssueDate(fac, fpartno, frev) AS flastiss , dbo.GetItemLastReceiptDate(fac, fpartno, frev) AS flastrcpt , dbo.GetItemMTDIssues(fac , fpartno , frev , GETDATE()) AS fmtdiss , dbo.GetItemYTDIssues(fac , fpartno , frev , GETDATE()) AS fytdiss , dbo.GetItemMTDReceipts(fac , fpartno , frev , GETDATE()) AS fmtdrcpt , dbo.GetItemYTDReceipts(fac , fpartno , frev , GETDATE()) AS fytdrcpt FROM dbo.inmastx GO
I’ll briefly describe what is happening here. The view loads every field from the base table IMASTX and then completes 12 separate User Defined Functions. I described how to use UDF’s here.
Why did Consona make this change?
I’d say it’s a good bet that they did so because they’ve always had problems keeping inventory numbers synced with other tables, especially with the Inventory On Hand table (INONHD). That’s why there are so many utilities to recalculate these manually. It’s important to note that M2M violates the rules of database normalization by keeping duplicates of information all over its database, and this is just one more example of it.
Great, what is the problem?
The problem, as I eluded to before, is that this kills performance, or at least the querying performance. Each one of those UDFs makes a separate call to the database, converting one select statement into many for each row.
Consider the following profile traces. When I execute the following query against the underlying table:
SELECT * FROM inmastx WHERE fpartno = '123456' AND frev = '001'
Profiler returns 13 rows with a total run time of 29 Milliseconds or .029 seconds.
The next trace involves the same query against the INMAST view:
SELECT * FROM inmast WHERE fpartno = ‘123456’ AND frev = '001'
Profiler returns 49 Rows with a total runtime of 198 Milliseconds or .198 seconds.
What’s the big hairy deal? The second query is 1/5th of a second anyway.
Ah, very true, but how many parts do you have in your item master? I ran the following queries against a database with approximately 7000 part numbers. Keep in mind that you should never “Select *” from anything, but I am doing it for simplicity.
Select * from inmastx requires 1 minute 22 seconds. Select * from inmast requires 3 minutes 4 seconds.
This performance hit above is bad, but it actually gets worse. What happens if you are only retrieving fields that aren’t calculated? For example, what if you only want the part number, revision, and product class? This is common because some modules, such as shipping, do not include the product class so you must link back to the item master to retrieve it. Consider the following:
select FPARTNO, FREV, FPRODCL from m2mdata01..inmast
Because INMAST is a view, all of those columns are calculated anyway and the query churns 6 times as long regardless.
So, what do I do about it?
Well, you’re going to have to edit your custom reports that deal with inventory in order to migrate, or consult with someone like me to do it for you. Essentially, you need to query the underlying table INMASTX for your data and then if you need On Hand Quantity, calculate it yourself with a sub-query. Here’s an example from above.
SELECT * , dbo.GetItemOnHandQuantity(fac, fpartno, frev) [OnHand] FROM inmastx WHERE fpartno = '123456' AND frev = '001'
This isn’t a perfect solution, but in this way you can call any of the UDF’s used in that view without calling all of them.
Well smarty pants, if this is so bad, what would you do?
First, it’s Mr. Smarty Pants to you. Seriously though, I have reservations about the upgrade path with M2M. To the best of my knowledge, Consona is not making substantive database changes in Version 7. In my opinion, they should take the opportunity to normalize the database (or at least significant portions of it) and enforce referential integrity while migrating to the .Net platform. I think they should drop VFP entirely, train users to use SQL Reporting Services for reports, and start fresh in version 7.
What do you think?