my code works slow. need some other logic

[sudhakaranr]

Hi all,

I have given my code here and it's updating the database slowly.
Can you give me some other logic.

comparing talble has 3million rows. i shoud update 1million rows.
it's taking 4 seconds to load one row..it's very slow.

please help me


[PHP]<?php
mysql_connect("localhost", "root", "sudr");
mysql_select_db("adsd");
$query_1 = "select ID from al_ao";
$result_1 = mysql_query($query_1);
while($row = mysql_fetch_array($result_1)) {
$zinc_id = $row['ZincID'];
$query_2 = "select Mwt, Xlogp, apol, pol, donors, acceptor, psa, netcharge, rb, smiles from tproperty where zincId = '$zinc_id'";
$result_2 = mysql_query($query_2);
while($row1 = mysql_fetch_array($result_2)) {
$query_3 = "update al_ao set Mwt='$row1[Mwt]', Xlogp='$row1[Xlogp]', Apol='$row1[apol]', Pol='$row1[pol]', Donor='$row1[donors]', Acceptor='$row1[acceptor]', Psa='$row1[psa]', Netcharge='$row1[netcharge]', Rb='$row1[rb]', Smiles='$row1[smiles]' where ZincID = '$zinc_id'";
mysql_query($query_3) or die("couldn't insert values");
}
}
?>[/PHP]

[ronverdonk]

First that springs up is that you select field ID but fetches the data from field zincID. Must be a typo.

Secondly, you did not specify if there are any indexes used and, if so, on which columns.

Thirdly, have you ever investigated the use of the JOIN to get it together?

Ronald :cool:

[Motoma]

Hi all,

I have given my code here and it's updating the database slowly.
Can you give me some other logic.

comparing talble has 3million rows. i shoud update 1million rows.
it's taking 4 seconds to load one row..it's very slow.

please help me


[PHP]<?php
mysql_connect("localhost", "root", "sudr");
mysql_select_db("adsd");
$query_1 = "select ID from al_ao";
$result_1 = mysql_query($query_1);
while($row = mysql_fetch_array($result_1)) {
$zinc_id = $row['ZincID'];
$query_2 = "select Mwt, Xlogp, apol, pol, donors, acceptor, psa, netcharge, rb, smiles from tproperty where zincId = '$zinc_id'";
$result_2 = mysql_query($query_2);
while($row1 = mysql_fetch_array($result_2)) {
$query_3 = "update al_ao set Mwt='$row1[Mwt]', Xlogp='$row1[Xlogp]', Apol='$row1[apol]', Pol='$row1[pol]', Donor='$row1[donors]', Acceptor='$row1[acceptor]', Psa='$row1[psa]', Netcharge='$row1[netcharge]', Rb='$row1[rb]', Smiles='$row1[smiles]' where ZincID = '$zinc_id'";
mysql_query($query_3) or die("couldn't insert values");
}
}
?>[/PHP]

I think a more advanced SQL update is necessary:

UPDATE tproperty s, al_ao u
SET 
  u.Mwt = s.Mwt,
  u.Xlogp = s.Xlogp,
  u.Apol = s.Apol,
  u.Pol = s.Pol,
  u.Donor = s.Donor,
  u.Acceptor = s.Acceptor,
  u.Psa = s.Psa,
  u.Netcharge = s.Netcharge,
  u.Rb = s.Rb,
  u.Smiles = s.Smiles
WHERE
  tproperty.zincID = <zincid>
  AND al_ao.zincID = tproperty.zincID
 

[ronverdonk]

motoma: Thank you for helping out.

Ronald :cool: