Inserting Hundreds of Thousands of Posts at Once

I have a project in which I create an array of 200k+ lines w/ 25 fields that run the wp_insert_post and update_post_meta. To start it processes pretty fast the slows down a lot after 10k records or so.

Is there a more efficient way to do this? Is there a MySQL stored procedure that exists so I can create these posts directly using SQL queries and skip the PHP?

echo "get list of posts to be created in wordpress \n";
//RETURNS ALL OFFER DATA OF POSTS THAT NEED TO BE ADDED
$sql = "SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z FROM offer WHERE modified = '1';";
$rst = $wpdb->get_results($sql,ARRAY_N); //run query
if(is_null($rst)){
  echo mysql_error() . "\n";
}else{
  echo "done \n";
}
echo "creating posts \n";;
foreach($rst as $r){
  //print_r($r);
  $my_post = array(
       'post_title' => $r[0],
       'post_status' => 'publish',
       'post_author' => 72,
       'post_category' => array(16)
    );

  // Insert the post into the database
  $newOffer = wp_insert_post( $my_post );

  if(!$newOffer){
    echo "problem creating the post " . $r[0];
  }

  array_unshift($r,$newOffer); // so that the data positions matches when calling updateMetaData 

  if($newOffer){
    updateMetaData($newOffer,$r);
  }
}

function updateMetaData($id,$data){
  global $wpdb;
  $category = $wpdb->get_results('SELECT categoryName FROM category WHERE categoryIdentifier ="'.mysql_escape_string($data[8]).'"');
  if($category){
    wp_set_object_terms( $id, array($category[0]->categoryName,16), 'category');
  }

  update_post_meta($id,'a','yes');
  update_post_meta($id,'b',$data[1]);
  $year = substr($data[2],0, -4);
  $mon = substr($data[2], -4, 2);
  $day = substr($data[2], -2); 
  update_post_meta($id,'c',$mon."https://wordpress.stackexchange.com/".$day."https://wordpress.stackexchange.com/".$year." 12:00:00 AM");
  $year = substr($data[3],0, -4);
  $mon = substr($data[3], -4, 2);
  $day = substr($data[3], -2); 
  update_post_meta($id,'d',$mon."https://wordpress.stackexchange.com/".$day."https://wordpress.stackexchange.com/".$year." 12:00:00 AM");
  update_post_meta($id,'e',$data[4]);//description
  update_post_meta($id,'f',$data[5]);
  update_post_meta($id,'g',$data[6]);
  update_post_meta($id,'h',$data[7]);
  update_post_meta($id,'i',$data[8]);
  update_post_meta($id,'j',$data[9]);
  update_post_meta($id,'k',$data[10]);
  update_post_meta($id,'l',$data[11]);
  update_post_meta($id,'m',$data[12]);
  update_post_meta($id,'n',$data[13]);
  update_post_meta($id,'o',$data[14]);
  update_post_meta($id,'p',$data[15]);
  update_post_meta($id,'q',$data[16]);
  update_post_meta($id,'r',$data[17]);
  update_post_meta($id,'s',$data[18]);
  update_post_meta($id,'t',$data[19]);
  update_post_meta($id,'u',$data[20]);
  update_post_meta($id,'v',$data[21]);
  update_post_meta($id,'w',$data[22]);
  update_post_meta($id,'x',$data[23]);
  update_post_meta($id,'y',$data[24]);
  update_post_meta($id,'z',$data[25]);
  $year = null;
  $mon = null;
  $day = null;
  //echo "wordpress post meta updated.\n";
}

There are two reasons I can think of that may cause negative impact

1. wp_insert_post retrieves the post from the DB after writing it to it. this is not fun but should have only a constant impact, so I’m guessing it is not your main problem

2. Each post is being stored in memory as part of the caching process, and in the end this makes your server choke when it runs out of physical memory and starts swapping. try calling wp_suspend_cache_addition which suspends caching in the beginning of your script.

wp_suspend_cache_addition(true);

echo "get list of posts to be created in wordpress \n";