• From: Indonesia, Jakarta
  • Registered: 05/08/2013
  • Posts: 28

Topic: Slow query in Invoice Against Sales Delivery menu

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.

2 Reply by apmuthu (edited by apmuthu 11/21/2013 04:59:15 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Slow query in Invoice Against Sales Delivery menu

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;

apmuthu's Website

  • From: Indonesia, Jakarta
  • Registered: 05/08/2013
  • Posts: 28

Re: Slow query in Invoice Against Sales Delivery menu

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.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Slow query in Invoice Against Sales Delivery menu

Updated my personal GitHub Repo.

Patch attached here.

Post's attachments

SlowQueryPatch.zip 908 b, 10 downloads since 2013-11-22 

You don't have the permssions to download the attachments of this post.

apmuthu's Website