Блог

У меня есть рабочий запрос на обновление с использованием mysql, но в настоящее время страница загружается очень медленно. Есть ли какой-нибудь способ ускорить обновление моего запроса ?.

Вот мой код

 <?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>";}
    ?>
  

Вы можете сделать это как один запрос:

 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;
  

Это, по крайней мере, избавит от update цикла, позволяя базе данных выполнять работу, а не приложению. Если производительность является проблемой, то, вероятно union all , это . Если это так, вы должны исследовать каждый подзапрос.