codeSeven codeSeven - 1 month ago 13
MySQL Question

PHP/MYSQL -update inside of looping. slow to load page

I have a working update query using mysql but currently it is very slow to load the page. Is there any way to make more faster the update of my query?.

Here's my code

<?php

$sql = "select ite_desc,ecr_desc, pric_cash, t.itemcode as itemcode ,sum(t.qty) as qty
from (
select ite_desc,ecr_desc, pric_cash, itemcode,qty from barcode as bc inner JOIN allinvty3 as ait on bc.itemcode = ait.in_code
union all
select ite_desc,ecr_desc, pric_cash, itemcode,qty from branchtobranch_tb as bb inner JOIN allinvty3 as ait on bb.itemcode = ait.in_code
union all
select ite_desc,ecr_desc, pric_cash, itemcode,qty from adjustment_tb as adt inner JOIN allinvty3 as ait1 on adt.itemcode = ait1.in_code where adt.status='APPROVED'
union all
select ite_desc,ecr_desc, pric_cash, itemcode,qty from stockreturn_tb as sb inner JOIN allinvty3 as ait on sb.itemcode = ait.in_code
union all
select ite_desc,ecr_desc, pric_cash, itemcode,qty from notinclude_tb as nt inner JOIN allinvty3 as ait on nt.itemcode = ait.in_code where nt.status='COMPLETE'
union all
select ite_desc,ecr_desc, pric_cash, itemcode,qty from purchase_tb as pt inner JOIN allinvty3 as ait on pt.itemcode = ait.in_code
union all
select ite_desc,ecr_desc, pric_cash, itemcode,(qty * -1) from soldout_pd as slp inner JOIN allinvty3 as ait2 on slp.itemcode = ait2.in_code) as t
group by itemcode order by ecr_desc ASC ";
$result = $conn->query($sql);
if ($result->num_rows > 0) {

echo "

</tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
$total =$row['qty'];
$itemcode=$row['itemcode'];
$sql1="UPDATE allinvty3 set sa_onhand = '".$total."' where in_code ='".$itemcode."'" ;
$conn->query($sql1);
}echo " </table>";}
?>

Answer

You can do this as a single query:

update allinvty3 a join
       (select t.itemcode, sum(t.qty) as qty
        from ((select ite_desc,ecr_desc,  pric_cash, itemcode, qty
               from barcode bc inner join
                    allinvty3 ait
                    on bc.itemcode = ait.in_code
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, qty
               from branchtobranch_tb bb inner join
                    allinvty3 ait
                    on bb.itemcode = ait.in_code
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, qty
               from adjustment_tb adt inner join
                    allinvty3 ait1
                    on adt.itemcode = ait1.in_code
               where adt.status = 'APPROVED'
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, qty
               from stockreturn_tb sb inner join
                    allinvty3 ait
                    on sb.itemcode = ait.in_code
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, qty
               from notinclude_tb nt inner join
                    allinvty3 ait
                    on nt.itemcode = ait.in_code
               where nt.status='COMPLETE'
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, qty
               from purchase_tb pt inner join
                    allinvty3 ait
                    on pt.itemcode = ait.in_code 
              ) union all
              (select ite_desc, ecr_desc, pric_cash, itemcode, (qty * -1) 
               from soldout_pd slp inner join
                    allinvty3 ait2
                    on slp.itemcode = ait2.in_code
              )
             ) t
        group by itemcode
       ) i
       on a.in_code = i.itemcode
    set a.sa_onhand = i.qty;

This will at least get rid of the update loop, letting the database do the work rather than application. If performance is an issue, then it is probably the union all. If that is the case, you have to investigate each subquery.

Comments