So we’re back at this… sorry this may be long
First, some assurance; for any researchers, exploring rolling ERPNext to production; reading this post, be rest assured we have used ERPNext extensively as have others in the community (and still/will do) and it has cost the users so little for the value it delivers… there’s nothing quite like it in the ERP ecosystem, period. These are all improvement phases, as an open source system, we keep putting minds together. If you want to know if you should, 100% go for it, on the forums you will see information to help you cross many bridges. These experiences are shared so more people get to give opinions. If you’re planning for large scale, indeed such posts can help you begin to draft a maintenance and continual improvement plan to integrate into your IT or your client’s.
So, this relates to online POS…
Over the week, I got another call from one more user saying invoices are taking over 40 seconds to submit, a little longer as items per invoice increases. If any one has read any of my posts we have been battling with these for a while, the number of complaints keep increasing as the months pass and data expands. I must say; the primary challenge is the database and I want to make a proposal based on repeated performance gains we have seen practically by handling the amount of data. Call it a brutal, axe down method, but that’s all that really works
May I state that, POS is one key module where many customers interface with the software, other modules are either used internally by the organization or in a more slow paced manner, for instance sales order, invoices mailed or issued to corporate customers.
We did our simple routine performance recovery for 2 users today with same results in both cases; with some testing at certain steps to see this time which actually has the greatest performance impact, GL or Sales Invoice tables(which is way less than ideal but the customers understand the details - no option) :
- Take a backup of the system
- Cloned > empty GL table - test showed marginal/little improvement, almost doubtful of any, just one terminal was used to post, no customers in-store, just a single sale.
- Cloned > empty sales invoice related tables - test - instant submission, shop owners are happy today, checkout is fast and painless.
We have tried a lot of recommendations over the months; and the only thing that works optimally is always this; clearing the Sales invoice tables; it goes to show that the main issue is just the growth of data, and the queries; so there should be a way to make it lighter and still keep the data. So…
Here’s what I’m proposing, a feature that allows a high level operator to archive sales, example, unrefined, design workflow:
- A doctype with a field: ’ Archive Sales up to’, user selects the date and clicks Archive
- System Creates an empty copy of the sales tables like `tabSales Inoice _098746
- System Copies sales data after the selected ‘archive up to date’ into the new table
- Subsequent archive operations will just be a matter of moving the data, table cloning is only on the first archive operation.
To conceive how the GL may handle this; the system keeps track of archived sales, this may be complex but doable, a new field ‘archived’ may be added and those archived invoices marked 1 as archived, so if an archived sale is opened from the GL or other link, the system pulls from the archive, with a tag, ‘Archived’.
With this; there would be a new set of reports that access the archives. so system presents and organization knows archive dates and will use the archive reports to view any old data they need to view, when to view this data to prevent performance impact on report loading remains an internal policy and control issue but the core effect is to speed up this very crucial customer interface, POS. This would be beneficial for many, especially non-technical. One can use queries and other analytic tools to work with the data in these ‘foreign tables’ but having the archives integrated will be great, so this will benefit the community…
Let me add that, offline POS is available but may not be acceptable by some organizations for obvious reasons. But there is the option in Chrome for instance; of using Group Policies to lock down the browser and create non-administrative users who cannot change settings, again this is getting advanced, secondly I doubt we have people practicing this to secure offline transactions.
Example from small POS only solution, in this case we have the option to archive it… :
Versions impacted in our use: v9 - v11