title: Laravel 4.2 - storing in database is very slow for large amount of data
body: <p>I'm building an application that handles several thousand apartments per month. 
  This means several inserts, updates and selects in different tables per apartment per month. </p>
  
  <p>I've used in-built laravel/eloquent syntax, nothing in raw sql, as Im not familiar with it very well.</p>
  
  <p>I have a button that generates the info for a month for all apartments. I wasnt very pleased of having to put all that in one action but it was required of me. </p>
  
  <p>Now, every time I click the button and generate the info it gets harder and harder to process as the database fills each month. I assume its normal considering the situation. Could I get an advice on how to optimize this? I have to wait like 30 seconds - 1 minute for it to finish.... which is not ok...</p>
  
  <p>This is how my controller basically runs (this is the main storing function, which calls several other functions):</p>
  
  <pre><code>// define month
  $month = '01-04-2017';
  
  // identify all payment categories (like water, electricity, maintenance etc)
  // all these must be treated separately as they are calculated in very different ways
  $payment_categories = PaymentCategories::get();
  $payments_array = [];
  foreach($payment_categories as $payment_category){
      $total = Transactions::where('month', $month)-&gt;where('payment_category_id', $payment_category-&gt;id)-&gt;sum('amount');
      $payments_array[$payment_category-&gt;id] = $total;
  }
  
  $apartments = Apartments::get();
  foreach($apartaments as $apartment){
  
      $this-&gt;storeMainData($apartment);
  
      // each of these categories must be treates separately as they are calculated in very different ways
      foreach($payment_categories as $payment_category){
          $total_amount = $payments_array[$payment_category-&gt;id];
          if($payment_category-&gt;id == '1'){
              $this-&gt;storePaymentCategory1($apartment, $payment_category, $month, $total_amount);
          }
  
          // there are aprox. 10 categories of payments which dont have a common pattern so they must be treated separately
          // these functions below work similarly to storePaymentCategory1()
          if($payment_category-&gt;id == '2'){
              $this-&gt;storePaymentCategory2($apartment, $payment_category, $month, $total_amount);
          }
          if($payment_category-&gt;id == '3'){
              $this-&gt;storePaymentCategory3($apartment, $payment_category, $month, $total_amount);
          }
          if($payment_category-&gt;id == '4'){
              $this-&gt;storePaymentCategory4($apartment, $payment_category, $month, $total_amount);
          }
          // ...
  
          // NEXT I HAVE A FUNCTION FOR GENERATING THE PDF RECEIPT
          // NEXT I HAVE ANOTHER FUNCTION TO GENERATE THE INVOICE, IF THE APARTMENT REQUIRES ONE
      }
  }
  </code></pre>
  
  <p>Next I have the storeMainData() function:</p>
  
  <pre><code>$validator = Validator::make(Input::all(), MainData::$rules, MainData::$messages);
  if($validator-&gt;passes()){
      $check = MainData::where('month', $month)-&gt;where('apartament_id', $apartament-&gt;id)-&gt;first();
      if(count($check)&gt;0){
          $store_main_data = $check;
      }
      else{
          $store_main_data = new MainData;
      }
  
      //main data stuff (like apartment receipt data etc...)
  
      $store_main_data-&gt;save();
  }
  else{
      return Redirect::back()
          -&gt;withErrors($validator);
  }
  </code></pre>
  
  <p>Next I have the storePaymentCategory1() function (which is an example of how other payment categories work as well):</p>
  
  <pre><code>// NOW WE MUST APPLY: total amount stored in the payment category / apartment percentage = due payment for the apartment;
  
  // identify the percentage 
  $percentage_type = $payment_category-&gt;percentage_id;
  $percentage = Percentages::where('percentage_type_id', $percentage_type)-&gt;where('apartment_id', $apartment-&gt;id)-&gt;first();
  
  // verify if the percentage type is applied to this apartment
  $calculated_apartments = Percentages::where('percentage_type_id', $percentage_type)-&gt;get();
  $calculated_apartments_array = [];
  foreach($calculated_apartments as $calculated_apartment){
      array_push($calculated_apartments_array, $calculated_apartment-&gt;apartment_id);
  }
  
  if(in_array($apartment-&gt;id, $calculated_apartments_array)){
      $validator = Validator::make(Input::all(), DuePayments::$rules, DuePayments::$messages);
      if($validator-&gt;passes()){
          $check = DuePayments::where('month', $month)-&gt;where('apartment_id', $apartment-&gt;id)-&gt;where('payment_category_id', $payment_category-&gt;id)-&gt;first();
          if(count($check)&gt;0){
              $store_due_payment = $check;
          }
          else{
              $store_due_payment = new DuePayments;
          }
  
          // store due payment stuff
  
          $store_due_payment-&gt;save();
      }
      else{
          return Redirect::back()
              -&gt;withErrors($validator);
      }
  }
  </code></pre>