With the release of Sage 500 ERP (formerly Sage MAS 500 ERP) version 7.4 you have a new variation Stock Quantity BIE. In Sage 500 ERP v7.4 they had to take into account the new unit of measures and the additional decimal precision. Due to these enhancements and a new date function they have altered how the Stock Quantity Explorer works.
With the enhancements it has also created occasions for some performance issues with the Stock Status BIE. The good news is you have an option to enhance the performance especially if you typically apply a Filter such as an Item, etc before running the query.
If you look in the tsmDataView table you will notice they have added a new column called SQLCommandOverride. If you look in the Dtabase Schema you can see they have defined this column as “The name of the SQL stored procedure that will be used for this lookup. Must return same columns as corresponding SQL command”.
You can locate the record for vdvStockStatus view using the follow SELECT statement.
SELECT tsmDataView.DataViewID,
tsmDataView.SQLCommandName,
tsmDataView.SQLCommandOverride
FROM tsmDataView
WHERE tsmDataView.SQLCommandName = 'vdvStockStatus';
You will see that the tsmDataView.SQLCommandOverride column contains a value of ‘spdvStockStatus’. This means that by default when this view is called it will run the stored procedure to compile the results to use in the Stock Status BIE. If you would prefer not to have this logic run and simply use the results of the ‘vdvStockStatus’ you should consider updating tsmDataView.SQLCommandOverride to NULL for this record. This will cause the BIE to operate normally and use the view.
DISCLAIMER! You should make sure you have a backup of the database prior to running this. You should test this thoroughly in a test environment before running in production. You are running this code as is with no warranty or support provided, nor any guarantee of this making your system faster.
UPDATE tsmDataView
SET SQLCommandOverride = NULL
WHERE tsmDataView.SQLCommandName = 'vdvStockStatus';
GO
I have seen instances where making this change has shaved 20 minutes off of the results being returned to the BI window. If you look at the logic of the assigned SP for this view it is basically doing a cursor over every row several times to evaluate transaction dates as well as unit of measure conversions. You will have to decide if this is necessary in your environment to be worth the time delay.
I will be researching the SP performance further but that is beyond the scope of this blog.