Sage 100

Have a client on Sage 100 Advanced 2017 with 83k items and we have determined 48k of those are no longer needed so we're looking at merging them into a single "DELETED ITEM" since they have history on some of these. They also have 10 years of history which is about 2.25m records in AR, IM, and SO history.

I'm looking for ways to minimize the time needed to merge all of these items, using a copy of their data set on one of their old servers, a specific item took 4.5 minutes to process, i then tried the same data set on a spare workstation of mine with an SSD and got the time down to 2.3 minutes for the same item. I then purged history for all of the modules integrated with IM so only transactions after 12/31/2016 were kept. This brought the total records in the various history tables down to 700k - 900k. Using the same specific item again to test the merge, it took 2.13 minutes to process.

Knowing that sage 100/providex is not multi-threaded, i'm thinking that ideally, i would need to run the merge on a workstation with as few physical cores as possible but i don't have any desktops with less than 4 physical cores but i'm not positive that the bottleneck is with the number of physical/logical cores.

Their production server has 4 virtual cores but has a top speed rating of 1.7 GHz. The specific item took 14.5 minutes to process on here.
Their old server has 4 virtual cores with a top speed rating of 3 GHz.
My workstation i tested on has 4 physical cores and with hyperthreading enabled (enables an additional logical core per physical core), i only see a difference by about 10 seconds for the specific item.

Am i overlooking anything else that i can do to speed up this process?


------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Anything preventing them from  moving to Premium and then write your own merge code in SQL?

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------

Original Message:
Sent: 8/10/2020 10:52:00 AM
From: Phil McIntosh
Subject: RE: Looking for ways to speed up item merge

Anything preventing them from  moving to Premium and then write your own merge code in SQL?

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------

They also have legacy Work Order. There is an upgrade planned but they have put it off this year.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 08-10-2020 10:51
From: Phil McIntosh
Subject: Looking for ways to speed up item merge

Anything preventing them from  moving to Premium and then write your own merge code in SQL?

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------

I've noticed that the speed per item gets slower as a batch progresses, so I don't do more than about 50 at a time.  I don't know any shortcuts... just process in batches over multiple evenings.
You could try scripting the huge table renumbering to see if that helps but I am wary of such a thing because writing the new record has to be paired with deleting the old (when ItemCode is part of the Primary Key).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

The item I was testing with actually doesn't even have a record in IM_ItemTransactionHistory after the purge so it looks like the utility may not be using the most efficient way to read the files and appears to be reading every single record after all.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 08-10-2020 10:58
From: Kevin Moyes
Subject: Looking for ways to speed up item merge

I've noticed that the speed per item gets slower as a batch progresses, so I don't do more than about 50 at a time.  I don't know any shortcuts... just process in batches over multiple evenings.
You could try scripting the huge table renumbering to see if that helps but I am wary of such a thing because writing the new record has to be paired with deleting the old (when ItemCode is part of the Primary Key).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Would the read/write ProvideX ODBC driver let you write your own SQL queries to do the update more efficiently?

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------

Original Message:
Sent: 08-10-2020 11:02
From: David Speck II
Subject: Looking for ways to speed up item merge

The item I was testing with actually doesn't even have a record in IM_ItemTransactionHistory after the purge so it looks like the utility may not be using the most efficient way to read the files and appears to be reading every single record after all.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 08-10-2020 10:58
From: Kevin Moyes
Subject: Looking for ways to speed up item merge

I've noticed that the speed per item gets slower as a batch progresses, so I don't do more than about 50 at a time.  I don't know any shortcuts... just process in batches over multiple evenings.
You could try scripting the huge table renumbering to see if that helps but I am wary of such a thing because writing the new record has to be paired with deleting the old (when ItemCode is part of the Primary Key).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Original Message:
Sent: 8/10/2020 11:11:00 AM
From: Phil McIntosh
Subject: RE: Looking for ways to speed up item merge

Would the read/write ProvideX ODBC driver let you write your own SQL queries to do the update more efficiently?

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------


Original Message:
Sent: 08-10-2020 11:02
From: David Speck II
Subject: Looking for ways to speed up item merge

The item I was testing with actually doesn't even have a record in IM_ItemTransactionHistory after the purge so it looks like the utility may not be using the most efficient way to read the files and appears to be reading every single record after all.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

If the bottleneck is the number of cores, have IT create a VM and then allocate one core to the VM.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

I did try creating a VM on my desktop and assigned it 1 core, now i don't know if the version of VMWare i was using doesn't allow a VM to utilize more of the host's cores than the VM was assigned but what i saw is that even though the Task Manager inside the VM report 100% CPU usage, on the host, i was only seeing 13% with hyperthreading enabled and 25% with hyperthreading disabled.
I don't know how other VM software handles the relation between host core and VM core.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 08-10-2020 11:10
From: Doug Higgs
Subject: Looking for ways to speed up item merge

If the bottleneck is the number of cores, have IT create a VM and then allocate one core to the VM.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary
------------------------------

We have a client with almost the exact same scenario.  At first @David Speck II I thought you were referring to them :).  For any MD's out there, has there been anything developed to "Whack" obsolete items?  I know at one time @Randy Marion created routines to blow away old PO's.  ​​Could it be done with items?  An unconditional deletion based on a list of items?  Seems like there has to be a better way than doing batches at night over several nights and heaven forbid it crashes in a batch!

------------------------------
alan niergarth
Velosio LLC
------------------------------

Original Message:
Sent: 08-12-2020 14:29
From: David Speck II
Subject: Looking for ways to speed up item merge

I did try creating a VM on my desktop and assigned it 1 core, now i don't know if the version of VMWare i was using doesn't allow a VM to utilize more of the host's cores than the VM was assigned but what i saw is that even though the Task Manager inside the VM report 100% CPU usage, on the host, i was only seeing 13% with hyperthreading enabled and 25% with hyperthreading disabled.
I don't know how other VM software handles the relation between host core and VM core.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 08-10-2020 11:10
From: Doug Higgs
Subject: Looking for ways to speed up item merge

If the bottleneck is the number of cores, have IT create a VM and then allocate one core to the VM.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

https://www5.v1ideas.com/TheSageGroupplc/Sage100ERP/Idea/Detail/11724

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 08-13-2020 14:18
From: alan niergarth
Subject: Looking for ways to speed up item merge

We have a client with almost the exact same scenario.  At first @David Speck II I thought you were referring to them :).  For any MD's out there, has there been anything developed to "Whack" obsolete items?  I know at one time @Randy Marion created routines to blow away old PO's.  ​​Could it be done with items?  An unconditional deletion based on a list of items?  Seems like there has to be a better way than doing batches at night over several nights and heaven forbid it crashes in a batch!

------------------------------
alan niergarth
Velosio LLC
------------------------------


Original Message:
Sent: 08-12-2020 14:29
From: David Speck II
Subject: Looking for ways to speed up item merge

I did try creating a VM on my desktop and assigned it 1 core, now i don't know if the version of VMWare i was using doesn't allow a VM to utilize more of the host's cores than the VM was assigned but what i saw is that even though the Task Manager inside the VM report 100% CPU usage, on the host, i was only seeing 13% with hyperthreading enabled and 25% with hyperthreading disabled.
I don't know how other VM software handles the relation between host core and VM core.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 08-10-2020 11:10
From: Doug Higgs
Subject: Looking for ways to speed up item merge

If the bottleneck is the number of cores, have IT create a VM and then allocate one core to the VM.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Assistant to the Traveling Secretary

Original Message:
Sent: 8/13/2020 2:18:00 PM
From: alan niergarth
Subject: RE: Looking for ways to speed up item merge

We have a client with almost the exact same scenario.  At first @David Speck II I thought you were referring to them :).  For any MD's out there, has there been anything developed to "Whack" obsolete items?  I know at one time @Randy Marion created routines to blow away old PO's.  ​​Could it be done with items?  An unconditional deletion based on a list of items?  Seems like there has to be a better way than doing batches at night over several nights and heaven forbid it crashes in a batch!

------------------------------
alan niergarth
Velosio LLC
------------------------------


Original Message:
Sent: 08-12-2020 14:29
From: David Speck II
Subject: Looking for ways to speed up item merge

I did try creating a VM on my desktop and assigned it 1 core, now i don't know if the version of VMWare i was using doesn't allow a VM to utilize more of the host's cores than the VM was assigned but what i saw is that even though the Task Manager inside the VM report 100% CPU usage, on the host, i was only seeing 13% with hyperthreading enabled and 25% with hyperthreading disabled.
I don't know how other VM software handles the relation between host core and VM core.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------