Help diagnosing slow Eloquent query

// Laravel 9

I'm querying against a table that has a couple million records with a _raw field that is indexed for searching. I have a hook in my AppServiceProvider that logs sql queries so I can inspect the format and performance.

DB::listen(function ($query) {
    Log::channel('sql')->info('query_log', [
        'query' => Str::replaceArray(
            '?',
            array_map(fn ($binding) => "'$binding'", $query->bindings),
            $query->sql
        ),
        'time' => $query->time
    ]);
});

When I run two seemingly identical queries, one from DB::raw() and one from the Eloquent model, I get drastically different performance. I'm really not sure what the issue is or why the difference in performance would be so stark. Here are the two queries and their corresponding sql and timing info.

// Raw SQL

\DB::select(\DB::raw("select top 10 * from [syslogs] where [_raw] like '%loggedcmd:%' order by [id] desc"))

[2024-06-03 09:15:48] local.INFO: query_log {"query":"select top 10 * from [syslogs] where [_raw] like '%loggedcmd:%' order by [id] desc","time":91.75}

// Eloquent query

\App\Models\Syslog::where('_raw', 'like', '%cfglog_loggedcmd:%')->orderBy('id', 'desc')->take(10)->get()

[2024-06-03 09:17:58] local.INFO: query_log {"query":"select top 10 * from [syslogs] where [_raw] like '%loggedcmd:%' order by [id] desc","time":116455.35}

The data is being presented in a Filament Table and from what I understand I need to use the eloquent builder and can't flip over to the raw sql super easy. Is there something obvious that I'm missing somehow?

LaryAI

2 years ago

Level 58

The issue you're experiencing with the performance difference between the raw SQL query and the Eloquent query is likely due to how Eloquent handles the query execution and the subsequent data hydration process. Eloquent, being an ORM, does more than just execute the SQL query; it also instantiates model objects for each row returned, which can be significantly slower, especially with large datasets.

Here are a few steps you can take to diagnose and potentially improve the performance of your Eloquent query:

Check for N+1 Query Problem: Ensure that your Eloquent query is not causing an N+1 query problem. This can happen if you have relationships that are being eagerly loaded without proper optimization.
Use select to Limit Columns: If you don't need all columns from the syslogs table, you can limit the columns being selected. This reduces the amount of data being transferred and processed.
```
\App\Models\Syslog::select('id', '_raw') // Add only the columns you need
    ->where('_raw', 'like', '%loggedcmd:%')
    ->orderBy('id', 'desc')
    ->take(10)
    ->get();
```

Use Chunking: If you are processing a large number of records, consider using chunking to process records in smaller batches.

\App\Models\Syslog::where('_raw', 'like', '%loggedcmd:%')
    ->orderBy('id', 'desc')
    ->chunk(100, function ($syslogs) {
        foreach ($syslogs as $syslog) {
            // Process each syslog
        }
    });

Disable Timestamps: If your model has timestamps enabled and you don't need them, you can disable them to reduce overhead.
```
class Syslog extends Model
{
    public $timestamps = false;
}
```
Use toBase Method: If you don't need the Eloquent model instances and just need the raw data, you can use the toBase method to get a query builder instance that doesn't hydrate models.
```
\App\Models\Syslog::where('_raw', 'like', '%loggedcmd:%')
    ->orderBy('id', 'desc')
    ->take(10)
    ->get()
    ->toBase();
```

Profile the Query: Use Laravel's built-in query profiling to get more insights into what might be causing the slowdown.

DB::enableQueryLog();
$syslogs = \App\Models\Syslog::where('_raw', 'like', '%loggedcmd:%')
    ->orderBy('id', 'desc')
    ->take(10)
    ->get();
$queries = DB::getQueryLog();
Log::info($queries);

Database Indexes: Ensure that the _raw field is properly indexed. Although you mentioned it is indexed, double-checking the index type and its effectiveness can sometimes reveal issues.
Database Configuration: Check your database configuration and ensure that it is optimized for the type of queries you are running. Sometimes, tweaking database settings can lead to significant performance improvements.

By following these steps, you should be able to diagnose and potentially improve the performance of your Eloquent query. If the performance is still not satisfactory, you might need to consider using raw queries for specific cases where performance is critical.