Why is Laravel Query Builder Slower Than Knex.js and Raw Query Analyzer

[filbertumbawa15]

Laravel Version

8.0

PHP Version

7.4

Database Driver & Version

SQL Server

Description

Hi everyone,

I've encountered a noticeable performance issue when using Laravel's Query Builder with SQL Server. Running the same simple query gives very different execution times depending on the method used:

• Laravel Query Builder: ~800–1000ms

• Knex.js (Node.js): ~200–300ms

• Raw SQL via SQL Server Management Studio (SSMS) Query Analyzer: ~100–150ms

All tests are done using the same SQL Server instance, schema, and indexes.

My questions:

1. Why does Laravel's Query Builder perform significantly slower with SQL Server in comparison to Knex.js or SSMS?

2. Does Laravel introduce extra overhead (e.g., parameter binding, internal abstraction, or data casting) that affects performance on SQL Server?

3. Are there known performance pitfalls specific to Laravel and SQL Server (e.g., driver issues, odbc vs sqlsrv)?

4. Would using DB::select() with raw SQL be a better choice for high-performance scenarios in Laravel with SQL Server?

Any tips on optimizing Laravel for SQL Server would be greatly appreciated!

Thanks in advance!

Steps To Reproduce

public function index()
{
    $data = DB::table('parameter')->get();

    $count = DB::table('parameter')->count();

//    $data = Parameter::all();

//    $count = Parameter::count();

    return response()->json([
        'data' => $data,
        'total' => $count
    ]);
}

[macropay-solutions]

public function index()
{
    $data = DB::table('parameter')->get(); // fetches the data from db without populating active records (models)

    $count = DB::table('parameter')->count();

//    $data = Parameter::all(); // fetches the data from db by populating models and then calling on each of them toArray() which does many things.

//    $count = Parameter::count(); // count should have pretty much the same execution time.

    return response()->json([
        'data' => $data,
        'total' => $count
    ]);
}

So, not the query builder is slower even if when building the query it might do more steps vs raw query but the processing of the fetched data by eloquent makes the difference.

Update.
The steps to reproduce mislead us. This is not a question of query vs eloquent builders.

[macropay-solutions]

Long answer

$data = DB::table('parameter')->get();
DB facade:
@method static \Illuminate\Database\Query\Builder table(string $table, string $as = null)

The get is executed on the query builder.

$data = Parameter::all();

Model class:

    /**
     * Get all of the models from the database.
     *
     * @param  array|string  $columns
     * @return \Illuminate\Database\Eloquent\Collection<int, static>
     */
    public static function all($columns = ['*'])
    {
        return static::query()->get(
            is_array($columns) ? $columns : func_get_args()
        );
    }

query()- will return an Eloquent Builder with a Query Builder inside.

Then get:

    /**
     * Execute the query as a "select" statement.
     *
     * @param  array|string  $columns
     * @return \Illuminate\Database\Eloquent\Collection<int, TModel>
     */
    public function get($columns = ['*'])
    {
        $builder = $this->applyScopes();

        // If we actually found models we will also eager load any relationships that
        // have been specified as needing to be eager loaded, which will solve the
        // n+1 query issue for the developers to avoid running a lot of queries.
        if (count($models = $builder->getModels($columns)) > 0) {
            $models = $builder->eagerLoadRelations($models);
        }

        return $this->applyAfterQueryCallbacks(
            $builder->getModel()->newCollection($models)
        );
    }

will call getModels:

    /**
     * Get the hydrated models without eager loading.
     *
     * @param  array|string  $columns
     * @return array<int, TModel>
     */
    public function getModels($columns = ['*'])
    {
        return $this->model->hydrate(
            $this->query->get($columns)->all() // this is the query builder call
        )->all();
    }

$this->model->hydrate will pass through the hydrate function call to the model's eloquent builder's hydrate:

    /**
     * Create a collection of models from plain arrays.
     *
     * @param  array  $items
     * @return \Illuminate\Database\Eloquent\Collection<int, TModel>
     */
    public function hydrate(array $items)
    {
        $instance = $this->newModelInstance();

        return $instance->newCollection(array_map(function ($item) use ($items, $instance) {
            $model = $instance->newFromBuilder($item);

            if (count($items) > 1) {
                $model->preventsLazyLoading = Model::preventsLazyLoading();
            }

            return $model;
        }, $items));
    }

Article on the same idea https://medium.com/p/25e80c5eb135

[macropay-solutions]

@filbertumbawa15 thank you for this question. You inspired us to create a new feature. This feature is included in laravel-crud-wizard-free 4.2.0 and in the demo page for clients resource.

[robert-moore96]

there are a few potential explanations here and i expect in reality there is a bit of each at play

my first question is how exactly is the performance being measured in each case?

there are a few different stages that need to happen in order for that example andpoint to return data

• the application needs to boot up + parse the request
• query builder generates a query (takes whatever abstractions it has and compiles them all down into a SQL query)
• establish a TCP connection with the database
• send the query to the database
• DB then has to process said query
• results are sent back
• application then needs to convert that data sent back into whatever representation it uses
• application processes that data and returns the data to the user

basically every one of these stages has opportunities to be an issue

PHP, atleast when running in its traditional run mode has some characteristics which can impact this, in particular that the application has to be loaded from disk and parsed on every request, which means the speed of the underlying disk plays a large role, and this also has a knock on effect that the DB connection cant be reused from 1 request to the next it has to be reinitialized

there are a few projects which aim to solve that such as swoole/openswoole/frankenphp which recent versions of laravel support via laravel octane

generating the query tbh based on your example there is nothing to generate you are just fetching an entire table

establishing the DB connection can take a decent amount of time depending on networking / etc

waiting for the DB to process the query is where things start to get interesting, there are many factors which will affect how long it takes the DB to process a query, including some which will vary over time and are not consistent, typically RDBMS will have some level of caching of queries so repeating the same query multiple times would be expected to get faster in some cases significantly. the OS and underlying stroage device may also have their own caching, while i dont doubt that some of the other points i mention specific to php/laravel are more significant factors it is almost impossible to completely isolate the DB itself

the result data being sent back is going to depend a lot on the amount of data along with networking

then the app getting that data into whatever structure it uses is also going to depend on how much data there is along with how much overhead the structure has

and then processing the data into the format to respond to user is once again going to depend on how much data and how efficient the processing is

those last 3 (and really last 4) are heavily dependand on how much data is being returned from the query

that is one of the reasons that ORMs / Active Record implementations have a reputation for being slow, because by default they will load more data than is needed

while i expect a bit of all of these factors is at play,
my 1st guess at the most signicant one is the application boot up and establishing DB connection on every request,
my 2nd guess is that the table contains a huge amount of data and only the laravel test is actually loading/processing all of it

as for recommendations

• try out laravel octane and see if that helps at all
• try loading a smaller amount of data, in my experience it is extremely rare that dumping out the entire contents of a db table as json is a good solution for an api (not that i havent seen it done just that its always had major issues)

[macropay-solutions]

@robert-moore96 in octane the query builder vs eloquent builder will have the same difference in processing a select of 1000 rows for example.

[robert-moore96]

sure elequent has some overheads compared to the query builder, but unless there is something extreme going on in the models beyond just the regular overhead from standard eloquent itself. nobody would ever use eloquent if it had that much overhead

i have personally seen the boot up and initializing DB connection cost a second or 2 per request when running fpm on very low iops storage

that is why my the first thing i asked was how they were measuring the performance, because i expect the times are actually that the php example needs to load the app from disk during that request while nodejs doesnt

[macropay-solutions]

@robert-moore96 you are right. @filbertumbawa15 only mentioned the query builder but in the steps to reproduce both eloquent and query builder are referenced:

Steps To Reproduce

public function index()
{
    $data = DB::table('parameter')->get(); // query builder

    $count = DB::table('parameter')->count();

//    $data = Parameter::all(); // eloquent builder

//    $count = Parameter::count();

    return response()->json([
        'data' => $data,
        'total' => $count
    ]);
}

That lead us to believe he is referencing them as comparison.