0023298: Physical Inventory / Create Inventory Count List: excessive runtimes with large datasets

Description

This process ran for about 25 hours before it finally completed successfully, writing 211k rows. Note that in this environment there were 1.3 million m_product rows.

Because the entire process commits at the end, it is very difficult to gauge the progress of the program when running in production. In this case, it was part of a live cutover to production, and the long runtime cause a lot of pain.

Steps To Reproduce

Execute the process with high volumes, like above.

Proposed Solution

Ideally the process would be re-designed to handle larger data volumes.

However, a cheaper solution that would prevent ugly surprises is to add a warning process like this:

- Add a Preference Attribute, "Number of Physical Inventory Rows to Generate Warning"--default it to 3000
- When the OK button is pressed on the "Create Inventory Count List" modal dialog, run a query to count the output rows that will be generated.
- If the count is greater than the "Number of Physical Inventory Rows to Generate Warning", then show a warning message: "You are about to generate &count number of Physical Inventory Count records, which is above the recommended maximum of &WarningCount. This process may run for a very long time. Please consider adding addition criteria to generate few inventory counts."
- Allow the user to proceed (or to re-select and generate the same logic again)

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0057237) johnfandl (reporter) 2013-03-14 00:41	I had a typo in the suggested error message. I meant to write this (fewer, instead of few): "You are about to generate &count number of Physical Inventory Count records, which is above the recommended maximum of &WarningCount. This process may run for a very long time. Please consider adding addition criteria to generate fewer inventory counts."

Issue History
Date Modified	Username	Field	Change
2013-03-14 00:38	johnfandl	New Issue
2013-03-14 00:38	johnfandl	Assigned To	=> dmiguelez
2013-03-14 00:38	johnfandl	Modules	=> Core
2013-03-14 00:38	johnfandl	Triggers an Emergency Pack	=> No
2013-03-14 00:41	johnfandl	Note Added: 0057237