Customer Activities > Processed Sales tab takes a long time to load

Owned by Julie Hanlon
Aug 24, 2022
2 min read

Problem

The Processed Sales tab in Customer Activiities on Debtor Maintenance takes a long time to return results, even when the "Limit to only sales in the last" x days is ticked and set to a small number of days.

It's not the stored proc behind this grid that is slow, it's the digesting of the XML schema returned by the stored proc.  We are phasing this out to use the newer technique of returning two record sets from the stored proc - the first being the schema and the second being the data. This is much faster, and still offers the flexibility of using a custom stored proc to return non-standard shaped data.

We can't change the existing tab in 7.2.1 but, in the meantime, we can introduce a new tab with the same name and data that is much faster.

Solution

Run the attached SQL Script to create a new stored proc, usp_Jiwa_Debtor_OnProcessedSales.  It adds an entry in the custom forms tab for debtor maintenance so a new tab appears on the Customer Activities tab "Processed Sales (2)" - it doesn't have the ability to limit by number of days, but in our testing even with 20,000 rows of data it only takes a few seconds to populate.

Steps

  • Run the attached script (edit it to grant permissions to your custom SQL User).

  • Instruct each user to turn on Use Custom tabs, then hide/rename the old tab as desired.