CODEWITHSUNDEEP
laravelperformanceeloquent
user5067291
user5067291

Reputation: 449

Speed up Eloquent query

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));

Upvotes: 4

Views: 8114

Answers (1)

Royal_MGH
Royal_MGH

Reputation: 804

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 :)

Upvotes: 9

Related Questions