Laravel Eloquent vs SelectRaw. How does it effect

Question

How do Laravel process the query when we use Eloquent vs SelectRaw for an scenario like:

Will this fetch data faster:

$data = Records::where('active', 1)->get();

or will this fetch my data faster:

$data = DB::select( DB::raw("SELECT * from records WHERE active ='1'") ); 

Does using SelectRaw effect on query processing speed when we are dealing with large data around 5,00,000 records?

Answer 1

There will always be some overhead from using any ORM. However, this is nearly always a non-issue.

For example, while there might be some slight performance increase from writing raw SQL, it's generally dwarfed by the cost of making the query in the first place. You can get a far, far bigger improvement in performance by caching the responses than by rewriting ORM queries in raw SQL.

An ORM does make certain types of slow inefficient queries more likely, but that's something that can be resolved by using a profiler like Clockwork to identify the slow or unnecessary queries and refactoring them away. Most ORM's have tools to handle things like the N+1 problem - for instance, Eloquent has the with() method to eager-load related tables, which is generally a lot more convenient than explicitly writing a query to do the eager-loading for you.

Using an ORM also comes with significant benefits to developers:

• It's generally easier to express relations between tables
• It helps avoid the mental context switch between PHP and SQL
• It does a lot of the work of sanitizing data for you
• It helps make your application portable between different databases (eg so you can test using SQLite but use MySQL in production)
• Where you have logic that can't be expressed using the ORM, it's generally easy to drop down to writing raw SQL for that part

If you have a slow query in a web application, then rewriting it as a raw query is probably the very last thing you should consider doing, after:

• Refactoring the query or queries to be more efficient/remove unnecessary queries
• Making sure the appropriate indices are set on your database
• Caching the responses

Writing all your queries as raw queries is a micro-optimization - it's a lot of work for not that much payback, and considering developer time is much more expensive than server time, it's hardly ever worth the bother. Even if you have a single, utterly horrendous query or set of queries that has a huge overhead, there are better ways to deal with it - under those circumstances I'd be inclined to create a stored procedure in a migration and call that rather than making the query directly.