Slow performance submitting invoices POS

ERPNext
1

Hi,

We have been experiencing a big issue when using POS in some stores is taking like 18 secs to submit or print an invoice, our current environment is the following:

ERPNext: v12.18.0 (version-12)
Frappe Framework: v12.15.0 (version-12)

Virtual (Droplet on Digital Ocean)
8 GB / 4 vCPUs

Current DB sizes (MB):

53%20AM
53%20AM692×478 78.7 KB

According to slow query we have the following queries with problems:

# Time: 210319  0:50:50
# User@Host: 81238610e9eb45f1[81238610e9eb45f1] @ localhost [127.0.0.1]
# Thread_id: 300  Schema: 81238610e9eb45f1  QC_hit: No
# Query_time: 3.432107  Lock_time: 0.000037  Rows_sent: 1  Rows_examined: 112266
# Rows_affected: 0
SET timestamp=1616129450;
SELECT sum(debit) - sum(credit)
			FROM `tabGL Entry` gle
			WHERE posting_date <= '2021-03-19' and gle.account = '1-1-30-01-08 - - Tienda - PB';
# Time: 210319  0:50:56
# User@Host: 81238610e9eb45f1[81238610e9eb45f1] @ localhost [127.0.0.1]
# Thread_id: 300  Schema: 81238610e9eb45f1  QC_hit: No
# Query_time: 5.745983  Lock_time: 0.000041  Rows_sent: 366947  Rows_examined: 733894
# Rows_affected: 0
SET timestamp=1616129456;
SELECT item_code, stock_value, name, warehouse
		FROM `tabStock Ledger Entry` sle
		WHERE posting_date <= '2021-03-19'  AND warehouse = 'Tienda - PB'
		ORDER BY timestamp(posting_date, posting_time) DESC, creation DESC;
# Time: 210319  0:51:03
# User@Host: 81238610e9eb45f1[81238610e9eb45f1] @ localhost [127.0.0.1]
# Thread_id: 300  Schema: 81238610e9eb45f1  QC_hit: No
# Query_time: 4.500637  Lock_time: 0.000045  Rows_sent: 1  Rows_examined: 265574
# Rows_affected: 0
SET timestamp=1616129463;
select sum(debit) - sum(credit)
		from `tabGL Entry` where party_type = 'Customer'
		and party = 'Client X' and company='Shop X';
# Time: 210319  1:00:07
# User@Host: 81238610e9eb45f1[81238610e9eb45f1] @ localhost [127.0.0.1]
# Thread_id: 381  Schema: 81238610e9eb45f1  QC_hit: No
# Query_time: 3.010399  Lock_time: 0.000096  Rows_sent: 24  Rows_examined: 1076232
# Rows_affected: 0
SET timestamp=1616130007;
select `tabGL Entry`.`name`
			from `tabGL Entry`
			where `tabGL Entry`.to_rename = 1.0
			
			 order by creation
			limit 50000 offset 0;

We have tried to increase memory on my.cnf and reds but no luck yet.

2

Are these queries actually executing during POS Invoice printing and posting? Because if that’s true, this SQL does not make sense for a POS invoice.

  1. This query would get the Net Total amount (since the beginning of time), for a single GL account. Why would that matter for POS invoice creation?
SELECT sum(debit) - sum(credit)
			FROM `tabGL Entry` gle
			WHERE posting_date <= '2021-03-19' and gle.account = '1-1-30-01-08 - - Tienda - PB';
  1. This query would return -every- Stock Entry, for 1 warehouse, since the beginning of time. There could be thousands. And it’s sorting them too! (even slower) Again, should not matter for POS invoices.
SELECT item_code, stock_value, name, warehouse
		FROM `tabStock Ledger Entry` sle
		WHERE posting_date <= '2021-03-19'  AND warehouse = 'Tienda - PB'
		ORDER BY timestamp(posting_date, posting_time) DESC, creation DESC;
  1. This query actually makes -some- sense. It should probably be limited to a particular GL account, though.
select sum(debit) - sum(credit)
		from `tabGL Entry` where party_type = 'Customer'
		and party = 'Client X' and company='Shop X';
  1. This query below seems unneccesary. Fetch -every- GL entry that must be renamed…why? Why not focus on just the GL entries for the POS invoice being posted?
select `tabGL Entry`.`name`
			from `tabGL Entry`
			where `tabGL Entry`.to_rename = 1.0
			
			 order by creation
			limit 50000 offset 0;

If these are executing when POS posts an invoice, there’s a big problem with either POS business logic, or the Print Format.