FrontAccounting forum — Slow query in Invoice Against Sales Delivery menu

Re: Slow query in Invoice Against Sales Delivery menu

null@example.com (apmuthu) — Fri, 22 Nov 2013 06:38:03 +0000

Updated my personal GitHub Repo.

Patch attached here.

Re: Slow query in Invoice Against Sales Delivery menu

null@example.com (mfaa98) — Fri, 22 Nov 2013 04:46:14 +0000

Thank you for your response. Now I feel confident to add the index into my production system.

I'll try to implement the query optimization too.

Re: Slow query in Invoice Against Sales Delivery menu

null@example.com (apmuthu) — Thu, 21 Nov 2013 16:37:49 +0000

Must we now add the following in:

ALTER TABLE `0_debtor_trans` ADD INDEX `Order` (`order_`);

We also need to optimise the query in the function at line 364 in sales/includes/db/cust_trans_db.inc to be:

function get_sql_for_sales_deliveries_view($selected_customer, $selected_stock_item=null)
{
    $sql = "SELECT trans.trans_no,
            debtor.name,
            branch.branch_code,
            branch.br_name,
            sorder.deliver_to,
            trans.reference,
            sorder.customer_ref,
            trans.tran_date,
            trans.due_date,
            (ov_amount+ov_gst+ov_freight+ov_freight_tax) AS DeliveryValue,
            debtor.curr_code,
            Sum(line.quantity-line.qty_done) AS Outstanding,
            Sum(line.qty_done) AS Done
        FROM "
         .TB_PREF."sales_orders as sorder LEFT JOIN "
         .TB_PREF."debtor_trans as trans 
            ON (sorder.order_no = trans.order_) LEFT JOIN "
         .TB_PREF."debtor_trans_details as line 
            ON (line.debtor_trans_no = trans.trans_no
                AND line.debtor_trans_type = trans.type) LEFT JOIN "
         .TB_PREF."debtors_master as debtor
            ON (trans.debtor_no = debtor.debtor_no) LEFT JOIN "
         .TB_PREF."cust_branch as branch 
            ON (trans.branch_code = branch.branch_code
                AND trans.debtor_no = branch.debtor_no)
        WHERE
            trans.type = ".ST_CUSTDELIVERY;

Slow query in Invoice Against Sales Delivery menu

null@example.com (mfaa98) — Wed, 13 Nov 2013 23:29:03 +0000

I have problem in Invoice Against Sales Delivery menu. It displays correctly, but the query is long (in my localhost experience, about 9 seconds).

After looking at the query and the database, there was small issues in the field order_ at table debtor_trans. The order_ field is not indexed. So after I add the index, the query now run normally (under half second)

Is it intended not to create index for field order_ or is it a performance bug?

Thank you.
Sorry for my bad English.