The vdvCustomerPayment view has been around for many versions but in Sage 500 v7.6 they made a change to this View. The change was to add Misc Cash Receipts. This is a nice addition but not if you have to wait 15 minutes for the View to return results.
After doing some major analysis on this View what we have uncovered is:
- This was covered up by the fact that if you do not have large datasets it does not impact performance.
- The SELECT DISTINCT was masking some of the poor JOIN conditions.
- A JOIN requiring a more specific condition
So here is a revised version of the vdvCustomerPayment (click on the image to download the full script).
I hope you found this helpful. The result of this for the client who reported the issue to us, remember taking sometimes over 10 minutes or more to return the results, it is now seconds!