3

I'm trying to query a large amount of data (40K records), and planning to query much larger datasets in the future. Eloquent seems to take a very long time to load this data. I wondered if there is a faster way to process this data. I'm attempting to look at the validity of my data, and hence checking to see if all fields are null.

I've used regular Eloquent calls. I don't think chunking the data is appropriate as I'm not planning on modifying the data in any way. I debated whether running a job every so often and calling the results of this job might be a better approach.

$journal = Journal::where('issn', $this->issn)->first();
$collection = $journal->outputs;
$collectionUnique = $collection->unique('doi');
$collectionDupes = $collection->diff($collectionUnique);

dd('Total Articles '.$this->getTotal(), 'Total Articles '.count($collection));
Karl Hill
  • 12,937
  • 5
  • 58
  • 95
user5067291
  • 440
  • 1
  • 6
  • 16

1 Answers1

9

Just use Query Builders !

Why we should use Query Builders for lots of records instead of Eloquent ?!

Here is the reason :

Query Builder is so faster than Eloquent :

Comparison (Eloquent vs Query Builder ) :

To insert 1000 rows for a simple table Eloquent takes 1.2 seconds and in that case DB facades take only 800 mili seconds(ms).

Another comparison :

Eloquent ORM average response time

Joins | Average (ms) 
------+-------------
1     | 162,2 
3     | 1002,7 
4     | 1540,0 

Result of select operation average response time for Eloquent ORM

Raw SQL average response time

 Joins | Average (ms) 
------+-------------
1     | 116,4 
3     | 130,6 
4     | 155,2 

Result of select operation average response time for Raw SQL

For more information : Laravel Eloquent vs Query Builder


Edited :

Your code should be : 

$journal = DB::table('journals')->where('issn', $this->issn)->first();


And Then For using Collection ( Simple way ) : 

$journal = Collection::make($journal); //For use Collection Methods
$collection = $journal->get("outputs");//Changed
$collectionUnique = $collection->unique('doi');
$collectionDupes = $collection->diff($collectionUnique);

dd('Total Articles '.$this->getTotal(), 'Total Articles '.count($collection));

Best Performance :

Use queries and Query Builder instead of collections . Because operations in SQL often is faster .

Please compare time for your last code and this code and please let me know in comments :)

Royal_MGH
  • 766
  • 4
  • 8
  • I cant run $journal->outputs with this (it throws an error). If I try to run it by searching the journal_id on the output table as Raw SQL queries it takes a long time. Dumping out the collection is fast, I believe its the unique / diff methods on the collection which is slowing it down. – user5067291 May 26 '19 at 18:52
  • Yes , Sorry I missed that , Eloquent just has Eloquent Collection , I will Edit this now ;) – Royal_MGH May 26 '19 at 19:26