FrontAccounting forum — Some changes in add_supp_invoice to reduce unnecessary query run time

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (kvvaradha) — Tue, 20 Aug 2019 14:53:51 +0000

Sure I will write my part of changes and bug fixes and ideas too.

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (apmuthu) — Mon, 19 Aug 2019 17:01:00 +0000

Forecasting from how many years it took to come in from FA 2.3 to FA 2.4 and the fact that the devs are much older now, take it to be atleast 5 years from when the initial 2.5 branch began. If it is any sooner, thank the advances in coding technologies and that we are now older and wiser

@kvvaradha: Whatever changes you have in mind to make a start, do let us know here so that it can atleast get into the codebase for those who wish to benefit from it in the meanwhile and form part of FA 2.5 if and when it appears.

Aug 17th 2019 - probably last commit to the 2.4(.7) branch.
Aug 19th 2019 - first commit to merge stable into unstable for 2.5 branch

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (kvvaradha) — Mon, 19 Aug 2019 09:31:52 +0000

ok.So when it will be released officially.

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (joe) — Sun, 18 Aug 2019 11:06:39 +0000

Hello again.

As I feared, Janusz is about a major rewrite in 2.5 just inside this too.

He also want to wait until 2.5 implementing this.

Joe

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (joe) — Sun, 18 Aug 2019 07:23:43 +0000

You may be right. I will just sync with Janusz regarding this.

Joe

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (apmuthu) — Sun, 18 Aug 2019 06:13:44 +0000

Since this does not affect the DB schema, can we not make a start here?

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (joe) — Sat, 17 Aug 2019 21:50:48 +0000

You are right guys. However this is quite a change in several files so I would like to do this in release 2.5. I will make a note about this.

/Joe

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (apmuthu) — Sat, 17 Aug 2019 13:33:29 +0000

Putting it in a variable and then using it everywhere will make sense.
@joe?

Re: Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (kvvaradha) — Fri, 16 Aug 2019 17:08:28 +0000

Even, we can pass the same currency to

 get_diff_in_home_currency($supp_trans->supplier_id, $old_date, $date_, $old_value,     $taxfree_line, $currency);

this will reduce a bit more

function get_diff_in_home_currency($supplier, $old_date, $date, $amount1, $amount2,$currency=null)
{
    $dec = user_price_dec();
    price_decimal_format($amount2, $dec);
if($currency == null)
    $currency = get_supplier_currency($supplier);
    $ex_rate = get_exchange_rate_to_home_currency($currency, $old_date);
    $amount1 = $amount1 / $ex_rate;
    $ex_rate = get_exchange_rate_to_home_currency($currency, $date);
    $amount2 = $amount2 / $ex_rate;
    $diff = $amount2 - $amount1;
    //return round2($diff, $dec);
    return $diff;
}

Some changes in add_supp_invoice to reduce unnecessary query run time

null@example.com (kvvaradha) — Fri, 16 Aug 2019 17:06:23 +0000

As i have seen some queries which unnecesaarily runs within loops or its called multiple times to pass it to another data. There is one recent one I am bring here to update the core makes simple.

/purchasing/includes/db/invoice_db.inc

From the add_supp_invoice function, there is items line foreach which has a function inside it

$currency = get_supplier_currency($supp_trans->supplier_id);

This actually no point to run within items loop, just imagine, if the user add 50 items in one invoice and tries to save. this query runs 49 times unnecessarily. and also one more thing with the same function.

From this function

function add_gl_trans_supplier($type, $type_no, $date_, $account, $dimension, $dimension2,  
    $amount, $supplier_id, $err_msg="", $rate=0, $memo="")
{
    if ($err_msg == "")
        $err_msg = "The supplier GL transaction could not be inserted";    
        
    return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, $memo, 
        $amount, get_supplier_currency($supplier_id), 
        PT_SUPPLIER, $supplier_id, $err_msg, $rate);
}

This is acutally calls more than once for a single item. during the same invoice process. here looks this code.

get_supplier_currency($supplier_id),

it runs more than 100 times as of my above example. So its better take out this and pass one more parameter in the

 add_gl_trans_supplier

like this

 add_gl_trans_supplier($type, $type_no, $date_, $account, $dimension, $dimension2,  
    $amount, $supplier_id, $err_msg="", $rate=0, $memo="", $currency=null){
      if ($err_msg == "")
        $err_msg = "The supplier GL transaction could not be inserted";    
    if($currency == null )
        $currrency = get_supplier_currency($supplier_id);
    return add_gl_trans($type, $type_no, $date_, $account, $dimension, $dimension2, $memo, 
        $amount, $currency, 
        PT_SUPPLIER, $supplier_id, $err_msg, $rate);
}

. This will simplify more than 150 query load to server when we submit 50 items invoice.

@Joe,And @Janusz, Hope you guys get the point.