sql - Save huge array to database -

first introduction, in case there's better approach: have product table *product_id* , stock, stock can big 5000 or 10000, need create list (in table) have row each item, is, if *propduct_id* has stock 1000 i'll have 1000 rows *product_id*, , plus, list needs random.

i chose php (symfony2) solution, found how random single product_id based on stock or how random order product list, didn't find how "multiply" rows stock.

now, main problem: so, in php it's no difficult, product_id list, "multiply" stock , shuffle, problem comes when want save:

1. if use $em->flush every 100 records or more memory overflow after while
2. if use $em->flush in every record takes ages save

this code save maybe can improve:

foreach ($huge_random_list $indice => $id_product) {     $preasignacion  = new listapreasignacion();     $preasignacion->setproductid($id_product);     $preasignacion->setorden($indice+1);     $em->persist($preasignacion);      if ($indice % 100 == 0) $em->flush(); } $em->flush(); 

edit final solution based on @pazi suggestion:

$conn = $em->getconnection(); foreach ($huge_random_list $indice => $id_product) {    $conn->executeupdate("insert product_list(product_id, order) "                             ." values({$id_product}, {$indice})"); } 

i suggest abstain doctrine orm , use dbal connection pure sql queries purpose. in applications, have store data in short time. doctrine adds overhead objects, checks , dehydrating. can retrieve dbal connection via di container. example in contoller:

conn = $this->get('database_connection'); 

read more dbal