Laravel Eloquent Vs DB Query Builder [Performance and other statistics]

So, the question is - Laravel Eloquent Builder Vs DB Query Builder -> Which is better and why ?

Answer in one line would be -

It actually depend on your project size and perspective. I love Both of Them Based on My Project Type.

I'll show here some details comparison between Laravel Eloquent and Query Builder Performance. When I'll choose what ? 

Let's Start Our Testing with Real Data -

In database/factories/UserFactory.php, let's see it's written a dummy factory or just write it. I'm using Laravel Version - 8.x

<?php

namespace Database\Factories;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Support\Str;

class UserFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = User::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'name' => $this->faker->name,
            'email' => $this->faker->unique()->safeEmail,
            'email_verified_at' => now(),
            'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
            'remember_token' => Str::random(10),
        ];
    }
}

 It's a Basic Factory for User Table.

Now, make run that from seeder -

In database/seeders/DatabaseSeeder.php  -  Just Seed enough data so we can get some basic difference. Insert 10,000 user data.

<?php

namespace Database\Seeders;

use App\Models\User;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        User::factory(10000)->create();
    }
}

Install Laravel Debugbar to test data and performance -

https://github.com/barryvdh/laravel-debugbar

Commands to install Laravel Debugbar - 

composer require barryvdh/laravel-debugbar --dev

// Publish Debug Bar
php artisan vendor:publish --provider="Barryvdh\Debugbar\ServiceProvider"

Now restart the server and clear caches and all

composer dump-autoload

php artisan optimize

// Restart the server Again
php artisan serve

 Check if these lines in .env or not

APP_DEBUG=true
DEBUGBAR_ENABLED=true

I've Changed routes/web.php to this-

<?php

use App\Http\Controllers\HomepageController;
use Illuminate\Support\Facades\Route;


Route::get('/', [HomepageController::class, 'home'])->name('index');

In Controller, controllers/HomePageController.php

# Test 1 - Eloquent Query

<?php

namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Support\Facades\DB;

class HomepageController extends Controller
{
    public function home()
    {
        $users = User::all();
        // $users = DB::table('users')->get();
        return view('welcome', compact('users'));
    }
}

View File in resources/views/welcome.blade.php

We've fetched all data of User Model which is approximately 10,000. Let's Check in Debugbar now, Time & Memory

Now Test it with Laravel Eloquent

Now Test it with DB Query Builder

So, To Get 10,000 rows

Laravel DB Query Builder takes

1. 30MB RAM
2. 229 milli seconds

But Eloquent takes

1. 39MB RAM &
2. 322 milli seconds

 So, DB Query is Winner For Large Data Set here.

Now Check with Multiple Join & More.

 Create Post and Category Model and Tables

php artisan make:model Category -m

php artisan make:model Post

Category Migration

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categories');
    }
}

Post Migration

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('category_id');
            $table->unsignedBigInteger('user_id');
            $table->string('title');
            $table->text('description')->nullable();

            $table->foreign('category_id')->references('id')->on('categories');
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

Run Migration

php artisan migrate

 Now Create Again Some Dummy Data in those tables -

php artisan make:factory PostFactory

Post Factory Class

<?php

namespace Database\Factories;

use App\Models\Post;
use Illuminate\Database\Eloquent\Factories\Factory;

class PostFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Post::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'user_id' => mt_rand(1, 9999),
            'category_id' => mt_rand(1, 20),
            'title' => $this->faker->title,
            'description' => $this->faker->paragraph,
        ];
    }
}

Category Factory Class -

<?php

namespace Database\Factories;

use App\Models\Category;
use Illuminate\Database\Eloquent\Factories\Factory;

class CategoryFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Category::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'name' => $this->faker->title,
        ];
    }
}

Update DatabaseSeeder to run again the categories and posts - 

<?php

namespace Database\Seeders;

use App\Models\Category;
use App\Models\Post;
use App\Models\User;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        // User::factory(10000)->create();
        Category::factory(20)->create();
        Post::factory(10000)->create();
    }
}

Run Seeder Again after cleaning - 

composer dump-autoload
php artisan optimize
php artisan db:seed

So, now we've

1. 10,000 posts
2. 10,000 users
3. 20 categories

Now, make relationship also with those tables in model.

Category Eloquent Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

Post Model Eloquent Relationship

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

User Model Eloquent Relationship

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

Ok,

Now make a query with get 10000 posts with user eloquent data and category eloquent data

 web.php

<?php

use App\Http\Controllers\HomepageController;
use Illuminate\Support\Facades\Route;

Route::get('/', [HomepageController::class, 'home'])->name('index');

Route::get('/eloquent-posts', [HomepageController::class, 'eloquentPost']);
Route::get('/query-posts', [HomepageController::class, 'queryPost']);

HomePageController.php

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use App\Models\User;
use Illuminate\Support\Facades\DB;

class HomepageController extends Controller
{
    public function home()
    {
         $users = User::all();
        //$users = DB::table('users')->get();
        return view('welcome', compact('users'));
    }


    public function eloquentPost()
    {
        $posts = Post::all();
        return view('eloquent-posts', compact('posts'));
    }

    public function queryPost()
    {
        $posts = DB::table('posts')->get();
        return view('query-posts', compact('posts'));
    }
}

 View Files

Eloquent Query View File

DB Query View File

Performance Eloquent Query Vs DB Query Builder

 So, DB Query also winner is here for performance, memory consumption.

 Now, test with some joins and data fetch

 So, for performance and memory consumption, SB query is definitely better than Eloquent Query Builder.

But, there's another mind set also -

Look at those code - 

public function eloquentPostMore()
    {
        $posts = Post::where('user_id', 10)->with('category')->get();
        return view('eloquent-posts', compact('posts'));
    }

    public function queryPostMore()
    {
        $posts = DB::table('posts')
        ->select('posts.*', 'categories.name', 'categories.created_at', 'categories.updated_at')
        ->leftJoin('categories', 'categories.id', '=', 'posts.category_id')
        ->where('user_id', 10)
        ->get();
        return view('query-posts', compact('posts'));
    }

So, Look at the codes of eloquent query and DB quiery.

I love the eloquent query here for coding quality and readability. It's readable and nice. DB query is complecated here.

So, My conclusion About Laravel Eloquent Vs DB Query Builder

So, have you any idea about this, just share with me in  the comment.

Find my answer on StackOverflow also for this topic - 

https://stackoverflow.com/questions/38391710/laravel-eloquent-vs-query-builder-why-use-eloquent-to-decrease-performance/46820024#46820024