[v12] General Ledger Report is getting Timed out on Not so Large Database

ERPNext Accounting
1

Hi,
I am having issues with the GL Entry table and the General Ledger Report the problem seems to be that the General Ledger report takes too long to load and gets timed out. The data in the General Ledger report is not much to cause this kind of to time out I have checked the slow query log and this is what I get

# Time: 210621 10:03:41
# User@Host: db_name[db_name] @ localhost [::1]
# Thread_id: 358864  Schema: db_name  QC_hit: No
# Query_time: 127.747201  Lock_time: 0.000191  Rows_sent: 133  Rows_examined: 1379539
# Rows_affected: 0 
# Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 151  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    PRIMARY tabGL Entry     ref     posting_date,account,company    company 563     const   447825  230868.00       100.00  100.00  Using index condition; Using where; Using filesort
# explain: 1    PRIMARY tabAccount      eq_ref  PRIMARY,lft,rgt,lft_rgt_index   PRIMARY 1022    db_name.tabGL Entry.account    1       1.00    100.00  0.06    Using where
#
use db_name;
SET timestamp=1624250021;
select
                        name as gl_entry, posting_date, account, party_type, party,
                        voucher_type, voucher_no, cost_center, project,
                        against_voucher_type, against_voucher, account_currency,
                        remarks, against, is_opening , debit, credit, debit_in_account_currency,
                credit_in_account_currency
                from `tabGL Entry`
                where company='RIGPL' and account in (select name from tabAccount
                        where lft>=424 and rgt<=425 and docstatus<2) and (posting_date <='2021-06-21' or is_opening = 'Yes')
                order by posting_date, account, creation;

Now if I check the table with describe table I get the following output:

MariaDB [db_name]> describe `tabGL Entry`;
+----------------------------+---------------+------+-----+----------+-------+
| Field                      | Type          | Null | Key | Default  | Extra |
+----------------------------+---------------+------+-----+----------+-------+
| name                       | varchar(255)  | NO   | PRI | NULL     |       |
| creation                   | datetime(6)   | YES  |     | NULL     |       |
| modified                   | datetime(6)   | YES  |     | NULL     |       |
| modified_by                | varchar(255)  | YES  |     | NULL     |       |
| owner                      | varchar(255)  | YES  |     | NULL     |       |
| docstatus                  | int(1)        | NO   |     | 0        |       |
| parent                     | varchar(255)  | YES  |     | NULL     |       |
| parentfield                | varchar(255)  | YES  |     | NULL     |       |
| parenttype                 | varchar(255)  | YES  |     | NULL     |       |
| idx                        | int(8)        | YES  |     | NULL     |       |
| posting_date               | date          | YES  | MUL | NULL     |       |
| transaction_date           | date          | YES  |     | NULL     |       |
| account                    | varchar(140)  | YES  | MUL | NULL     |       |
| cost_center                | varchar(140)  | YES  |     | NULL     |       |
| debit                      | decimal(18,6) | NO   |     | 0.000000 |       |
| credit                     | decimal(18,6) | NO   |     | 0.000000 |       |
| against                    | text          | YES  |     | NULL     |       |
| against_voucher            | varchar(140)  | YES  | MUL | NULL     |       |
| against_voucher_type       | varchar(140)  | YES  | MUL | NULL     |       |
| voucher_type               | varchar(140)  | YES  | MUL | NULL     |       |
| voucher_no                 | varchar(140)  | YES  | MUL | NULL     |       |
| remarks                    | text          | YES  |     | NULL     |       |
| is_opening                 | varchar(140)  | YES  |     | NULL     |       |
| is_advance                 | varchar(140)  | YES  |     | NULL     |       |
| fiscal_year                | varchar(140)  | YES  |     | NULL     |       |
| company                    | varchar(140)  | YES  | MUL | NULL     |       |
| _user_tags                 | text          | YES  |     | NULL     |       |
| _comments                  | text          | YES  |     | NULL     |       |
| party                      | varchar(140)  | YES  | MUL | NULL     |       |
| party_type                 | varchar(140)  | YES  | MUL | NULL     |       |
| credit_in_account_currency | decimal(18,6) | NO   |     | 0.000000 |       |
| debit_in_account_currency  | decimal(18,6) | NO   |     | 0.000000 |       |
| account_currency           | varchar(140)  | YES  |     | NULL     |       |
| _liked_by                  | text          | YES  |     | NULL     |       |
| _assign                    | text          | YES  |     | NULL     |       |
| project                    | varchar(140)  | YES  |     | NULL     |       |
| voucher_detail_no          | varchar(140)  | YES  |     | NULL     |       |
| finance_book               | varchar(140)  | YES  |     | NULL     |       |
| to_rename                  | int(1)        | NO   |     | 1        |       |
| due_date                   | date          | YES  |     | NULL     |       |
+----------------------------+---------------+------+-----+----------+-------+

Now the Account tab is described below:

MariaDB [db_name]> describe `tabAccount`;
+-----------------------+---------------+------+-----+----------+-------+
| Field                 | Type          | Null | Key | Default  | Extra |
+-----------------------+---------------+------+-----+----------+-------+
| name                  | varchar(255)  | NO   | PRI | NULL     |       |
| creation              | datetime(6)   | YES  |     | NULL     |       |
| modified              | datetime(6)   | YES  |     | NULL     |       |
| modified_by           | varchar(255)  | YES  |     | NULL     |       |
| owner                 | varchar(255)  | YES  |     | NULL     |       |
| docstatus             | int(1)        | NO   |     | 0        |       |
| parent                | varchar(255)  | YES  |     | NULL     |       |
| parentfield           | varchar(255)  | YES  |     | NULL     |       |
| parenttype            | varchar(255)  | YES  |     | NULL     |       |
| idx                   | int(8)        | YES  |     | NULL     |       |
| account_name          | varchar(140)  | YES  |     | NULL     |       |
| parent_account        | varchar(140)  | YES  | MUL | NULL     |       |
| freeze_account        | varchar(140)  | YES  |     | NULL     |       |
| account_type          | varchar(140)  | YES  |     | NULL     |       |
| tax_rate              | decimal(18,6) | NO   |     | 0.000000 |       |
| company               | varchar(140)  | YES  |     | NULL     |       |
| old_parent            | varchar(140)  | YES  |     | NULL     |       |
| lft                   | int(11)       | NO   | MUL | 0        |       |
| rgt                   | int(11)       | NO   | MUL | 0        |       |
| _user_tags            | text          | YES  |     | NULL     |       |
| root_type             | varchar(140)  | YES  |     | NULL     |       |
| balance_must_be       | varchar(140)  | YES  |     | NULL     |       |
| report_type           | varchar(140)  | YES  |     | NULL     |       |
| _comments             | text          | YES  |     | NULL     |       |
| _assign               | text          | YES  |     | NULL     |       |
| is_group              | int(1)        | NO   |     | 0        |       |
| warehouse             | varchar(140)  | YES  |     | NULL     |       |
| account_currency      | varchar(140)  | YES  |     | NULL     |       |
| _liked_by             | text          | YES  |     | NULL     |       |
| account_number        | varchar(140)  | YES  |     | NULL     |       |
| inter_company_account | int(1)        | NO   |     | 0        |       |
| include_in_gross      | int(1)        | NO   |     | 0        |       |
| disabled              | int(1)        | NO   |     | 0        |       |
+-----------------------+---------------+------+-----+----------+-------+

If I check the number of rows in both GL Entry table and Account then the values are as below:

GL Entry Table = 895657 Total Rows
Account = 382 Total Rows

So if I check the slow query log its analysing around 1.3 million rows to return only 133 rows.
Is there a way I can figure out with the data why is the General Ledger report misbehaving in my production server and not on my test server.

This is causing a lot of issues at my production server end and any help would be greatly appreciated.

2

@adityaduggal Have you tried using inner join instead of the subquery in where condition account in (select name from tabAccount where lft>=424 and rgt<=425 and docstatus<2)?
Your query would look like this:

select
    t.name as gl_entry, t.posting_date, t.account, t.party_type, t.party, t.voucher_type, t.voucher_no, t.cost_center, t.project,
    t.against_voucher_type, t.against_voucher, t.account_currency, t.remarks, t.against, t.is_opening , t.debit, t.credit, t.debit_in_account_currency,
    t.credit_in_account_currency
from `tabGL Entry` t
inner join tabAccount ta on ta.name = t.account
where
    t.company = 'RIGPL' and (t.posting_date <='2021-06-21' or t.is_opening = 'Yes')
    and ta.lft >= 424 and ta.rgt <= 425 and ta.docstatus < 2
order by t.posting_date, t.account, t.creation;

I think it will help speeding up the query.
Sorry for not checking the date of the post before posting a reply :sweat_smile: