php - How to handle/optimize thousands of different to executed SELECT queries? -

php - How to handle/optimize thousands of different to executed SELECT queries? -

i need synchronize specific info between 2 databases (one mysql, other remote hosted mssql db) thousands of rows. when execute php file stucks/timeouts after several minutes guess, wonder how can prepare issue , maybe optimize way of "synchronizing" it.

what code needs do:

basically want for every row (=one account) in database gets updated - 2 specific info (=2 select queries) database (mssql db). hence utilize foreach loop creates 2 sql queries each row , afterwards update info 2 fields of row. talk ~10k rows needs run thru foreach loop.

my thought may help ??

i have heared things pdo transactions should collect queries , sending them afterwards in bundle of select queris, have no thought whether utilize them correctly or whether help in such cases.

this current code, timing out after few minutes:

// dbh => mssql db | db => mysql db $dbh->begintransaction(); // referral ids needs updated: $listaccounts = "select * gifting refscompleted <= 100 order idgifting asc"; $ps_listaccounts = $db->prepare($listaccounts); $ps_listaccounts->execute(); foreach($ps_listaccounts $row) { $refid=$row['refid']; // refsinserted $refsinserted = "select count(username) done accounts referral='$refid'"; $ps_refsinserted = $dbh->prepare($refsinserted); $ps_refsinserted->execute(); $row = $ps_refsinserted->fetch(); $refsinserted = $row['done']; // refscompleted $refscompleted = "select count(username) done accounts referral='$refid' , finished=1"; $ps_refscompleted = $dbh->prepare($refscompleted); $ps_refscompleted->execute(); $row2 = $ps_refscompleted->fetch(); $refscompleted = $row2['done']; // update fields local order db $updategifting = "update gifting set refsinserted = :refsinserted, refscompleted = :refscompleted refid = :refid"; $ps_updategifting = $db->prepare($updategifting); $ps_updategifting->bindparam(':refsinserted', $refsinserted); $ps_updategifting->bindparam(':refscompleted', $refscompleted); $ps_updategifting->bindparam(':refid', $refid); $ps_updategifting->execute(); echo "$refid: $refsinserted refs inserted / $refscompleted refs completed<br>"; } $dbh->commit();

you can of in 1 query correlated sub-query:

update gifting set refsinserted=(select count(username) accounts referral=gifting.refid), refscompleted=(select count(username) accounts referral=gifting.refid , finished=1)

a correlated sub-query using sub-query (query within query) references parent query. notice in each of sub-queries referencing gifting.refid column in clause of each sub-query. while isn't best performance because each of sub-queries still has run independent of other queries, perform much improve (and going get) have there.

edit:

and reference. don't know if transaction help here @ all. typically used when have several queries depend on each other , give way rollback if 1 fails. example, banking transactions. don't want balance deduct amount until purchase has been inserted. , if purchase fails inserting reason, want rollback alter balance. when inserting purchase, start transaction, run update balance query , insert purchase query , if both go in correctly , have been validated commit save.

edit2:

if doing this, without doing export/import do. makes few assumptions though. first using mssql 2008 or newer , sec referral id number. i'm using temp table insert numbers because can insert multiple rows single query , run single update query update gifting table. temp table follows construction create table temptable (refid int, done int, total int).

//get list of referral accounts //if using 1 column, query 1 column $listaccounts = "select distinct refid gifting refscompleted <= 100 order idgifting asc"; $ps_listaccounts = $db->prepare($listaccounts); $ps_listaccounts->execute(); //loop on , list of refids above. $refids = array(); foreach($ps_listaccounts $row){ $refids[] = $row['refid']; } if(count($refids) > 0){ //implode string utilize in query below $refids = implode(',',$refids); //select out total count $totalcount = "select referral, count(username) cnt accounts referral in ($refids) grouping referral"; $ps_totalcounts = $dbh->prepare($totalcount); $ps_totalcounts->execute(); //add array of counts $counts = array(); //loop on total counts foreach($ps_totalcounts $row){ //if referral id not found, add together if(!isset($counts[$row['referral']])){ $counts[$row['referral']] = array('total'=>0,'done'=>0); } //add count $counts[$row['referral']]['total'] += $row['cnt']; } $donecount = "select referral, count(username) cnt accounts finished=1 , referral in ($refids) grouping referral"; $ps_donecounts = $dbh->prepare($donecount); $ps_donecounts->execute(); //loop on total counts foreach($ps_totalcounts $row){ //if referral id not found, add together if(!isset($counts[$row['referral']])){ $counts[$row['referral']] = array('total'=>0,'done'=>0); } //add count $counts[$row['referral']]['done'] += $row['cnt']; } //now loop on counts , generate insert queries temp table. //i suggest using temp table because can insert multiple rows //in 1 query , update 1 query. $sqlinsertlist = array(); foreach($count $refid=>$count){ $sqlinsertlist[] = "({$refid}, {$count['done']}, {$count['total']})"; } //clear out temp table first inserting new rows $truncsql = "truncate table temptable"; $ps_trunc = $db->prepare($truncsql); $ps_trunc->execute(); //make insert sql multiple insert rows $insertsql = "insert temptable (refid, done, total) values ".implode(',',$sqlinsertlist); //prepare sql insert mssql $ps_insert = $db->prepare($insertsql); $ps_insert->execute(); //sql update existing rows $updatesql = "update gifting set refsinserted=(select total temptable refid=gifting.refid), refscompleted=(select done temptable refid=gifting.refid) refid in (select refid temptable) , refscompleted <= 100"; $ps_update = $db->prepare($updatesql); $ps_update->execute(); } else { echo "there no reference ids found \$dbh"; }

php mysql sql pdo