I have two tables in one database that are emptied and replaced very frequently. Each table has well over 65,000 rows. I want to select two fields from the first table (LnsPriceUpdates), then take those values and compare and insert them into the second table (LnsCatalog) and want to do this for all 65,000 items in the database.
(Note: I want to do all this from a localhost)
Here's the current code I'm using:
public function testbananaAction()
{
$em = $this->getDoctrine()->getManager();
$query = $em
->createQuery(
'SELECT l.partnumber, l.level1Net FROM AppBundle:LnsPriceUpdates l WHERE l.level1Net IS NOT NULL'
)
->getResult();
foreach ($query as $row){
$partNo = $row['partnumber'];
$part = $em->getRepository('AppBundle:Lnscatalog')
->findOneBy(['partnoid' => $partNo]);
$dealerCost = $row['level1Net'];
if(isset($dealerCost) && isset($part)){
$partCheck = $part->getDealerCost();
if(!isset($partCheck)){
$part->setDealerCost($dealerCost);
$em->flush();
}
} else {
continue;
}
}
return new Response('yay');
}
Any ideas to accomplish this task without crashing would be helpful.
Can you achieve with a single Sql Update something like:
update Lnscatalog c
set c.dealerCost = (SELECT l.level1Net FROM AppBundle:LnsPriceUpdates l WHERE l.level1Net IS NOT NULL and l.partnumber=c.partnoid)
where c.dealerCost is null
and exists (SELECT l.level1Net FROM AppBundle:LnsPriceUpdates l WHERE l.level1Net IS NOT NULL and l.partnumber=c.partnoid)
You can speed up this query adding some index on the above table, as example:
ALTER TABLE LnsPriceUpdates
ADD INDEX LnsPriceUpdates_IDX_1 (level1Net, partnumber) ;
ALTER TABLE LnsPriceUpdates
ADD INDEX LnsPriceUpdates_IDX_2 (partnumber) ;
ALTER TABLE Lnscatalog
ADD INDEX Lnscatalog_IDX_2 (dealerCost) ;
Some reference about optimizing sql query can be found here, here and here
Hope this help