Archives

That's Quite a View - INMast

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 =123456AND   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?

Related posts:

9 comments to That’s Quite a View – INMast

  • Visual Sucks Pro should have been completely removed from use years ago.

    I certainly hope they’ve got at least some database changes planned for the new versions. An updated interface is all fine and good but that does nothing to change the fact that the database is the primary problem. I’ll take a solid database with a poor user interface over that any day.

    I’ll stand beside your suggestions on this one, Mr. Smarty Pants.

  • Oops. If any of you happened to notice that my first comment to this post (now deleted) seemed out of place, it’s because I published this article a day early by accident. That comment goes with tomorrow’s article.

    So much for being Mr. Smarty Pants.

  • Judy

    Hopefully someone at Consona will take your suggestions to heart and work on making substantial improvements to Version 7, while they still have the opportunity. Making SQL reporting services the default for M2M reports, would also be a much needed improvement to the product!
    Thanks again, Dave for your work on these issues. Great job!!

  • Mr. SmartyMartyPants

    It is with great anticipation I look forward to the removal of VFP from the M2M environment. Not only the reporting aspect but the Red Box break outs relative to “Fox Pro gone wild” episodes will move life closer to the “Walgreens commercial” ideal.

    Referential integrity is a must and I suspect M2M will cave under the pressure if we continue to bark away.

    I have just begun my SQL journey of report programming Obi-wan so please continue.

    PS: Yes the “Marty” is a promotion, don’t spend it all in one place.

    Thank you

  • Darren

    Hey, I have been reading your blog for a while now. It sure is nice to have someone talking about M2M that doesn’t work for them! It gives a different perspective. Thanks.

    I just upgraded to Version 6.0 this past weekend and I am having an issue with one of my reports. It pulls a lot of data from the Item Master and of course is running very slow. I remembered this post and thought I would take a look at it to see what I can do to speed it up.
    Here is the query I am running:

    SELECT INMAST.Fpartno,INMAST.Frev,INMAST.Fdescript,INMAST.Fonhand,;
    INMAST.Fbook,INMAST.Freordqty,INMAST.Fsafety,INMAST.Fac,;
    INVEND.Fpartno,INVEND.Fpartrev,INVEND.Fac,INVEND.Fvendno,;
    INVEND.Fpriority, inmast.fproqty;
    FROM M2MDATA!INMAST INMAST INNER JOIN M2MDATA!INVEND INVEND ON INMAST.FPARTNO = INVEND.FPARTNO;
    WHERE fsource=’S’ and fcpurchase’Y’ and (invend.fvendno is NULL ;
    or invend.fvendno=’000609′ or invend.fvendno=’000610′ or invend.fvendno=’000611′ ;
    or invend.fvendno=’000612′ or invend.fvendno=’000730′);
    ORDER BY INMAST.Fpartno;
    INTO CURSOR JOQSTK

    I then go on and filter the data further using this “filter text” on the selections tab on RPMAKE

    inmast.fbook-inmast.fonhand>0
    or inmast.fsafety-inmast.fonhand>0
    or inmast.fonhand-inmast.fbook<inmast.fsafety

    Essentially it pulls a make-shift Job Order Queue based on the Vendor (we have different departments and have not found a better way to be able to tell what items on the Queue are for each department.) I am sure there is a better way to do this and if anyone has any suggestions it would be appreciated.

    As this post suggests I tried changing my query to the following:
    SELECT ;
    inmastx.Fpartno,;
    inmastx.Frev,;
    inmastx.Fdescript,;
    dbo.GetItemOnHandQuantity(inmastx.fac, inmastx.fpartno, inmastx.frev) as on_hand,;
    dbo.GetItemCommittedQuantity(inmastx.fac, inmastx.fpartno, inmastx.frev) as committed,;
    inmastx.Freordqty,;
    inmastx.Fsafety,;
    inmastx.Fac,;
    INVEND.Fpartno,;
    INVEND.Fpartrev,;
    INVEND.Fac,;
    INVEND.Fvendno,;
    INVEND.Fpriority,;
    dbo.GetItemInProcessQuantity(inmastx.fac, inmastx.fpartno, inmastx.frev) as in_process;
    FROM m2mdata!inmastx inmastx ;
    inner JOIN m2mdata!invend INVEND ON ;
    inmastx.FPARTNO = INVEND.FPARTNO and inmastx.frev = invend.fpartrev and inmastx.fac = invend.fac;
    WHERE inmastx.fsource='S' and inmastx.fcpurchase’Y’ and (invend.fvendno is NULL ;
    or invend.fvendno=’000609′ or invend.fvendno=’000610′ or invend.fvendno=’000611′ ;
    or invend.fvendno=’000612′ or invend.fvendno=’000730′);
    ORDER BY inmastx.Fpartno;
    INTO CURSOR JOQSTK

    This query runs fine in SQL but I am missing something with the syntax to get FOXY to accept it. I get an “Object DBO is not found” error.

    Any guidance would be greatly appreciated.

    Darren

  • Daren,
    I tried your code in SSMS (SQL Server Manangement Studio, Query Analyzer in SQL 2000) and VFP. It ran fine in both cases. In SSMS I had to remove the “m2mdata!” words and the “into cursor joqstk” words from the query. The existance of those words in your query leads me to belive your runing the query through the m2m wrapper (M2MSELECT()). The first thing to do to speed up any report that uses the M2MSELECT() function is to modify the code to run the SQLEXEC() function insead of the m2m wrapper. If you need help doing this, I can certainly guide you in the right direction. But that doesn’t seem to be the issure here. I put your code in the m2m wrapper and it ran just fine.

    The issue may be the verson of VFP you are using. Which version of VFP are you using to compile the report’s .PRG? I’m running VFP 9.0.

  • Darren

    Thanks for the reply Glen.
    I am using VFP 9.0 SP2. It is just a Query (.QPR)not a .PRG with it set to SQL on the RPMAKE screen. And YES, it runs fine directly in SQL. I must be doing something wrong, I just can’t put my finger on it!
    I guess I will keep researching and trying.
    Thanks Again

    Darren

  • Thanks for checking that out Glenn. To be honest Darren, I have never created a M2M Report with a QPR file. If I’ve written the SQL I use another medium such as SQL Reporting Services, Crystal, or Excel.

  • Darren

    Hummm, Thanks for the Replay David. I guess I am going to have to learn how to use SQL reporting Services with M2M. Can anyone point me towards some documentation?

    Darren

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>