Laravel Eloquent vs query builder - Why use eloquent to decrease performance [closed]

Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses.

Active Record is a good solution for processing a single entity in CRUD manner - that is, create a new entity with filled properties and then save it to a database, load a record from a database, or delete.

You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alerts or update statistics counters when someone has created a new account), traits (timestamps, soft deletes, your custom traits) eager/lazy loading etc. You can also apply domain-driven pattern and implement some pieces of business logic in your Active Record entities, for example, validation, managing relations, calculations etc.

But, as you already know, Active Record comes with some performance price.

When you process a single record or a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain Laravel DB methods is a better approach.

For our Laravel based applications we are using both approaches as we see appropriate. We use Laravel's Eloquent for UI forms to process a single record and use DB methods (backed by SQL views with additional database engine specific performance tweaks) to retrieve data for UI tables, export tasks etc. It also works well with RESTful APIs - Eloquent for GET, PUT, POST, DELETE with a key and DB for GET without key but with filters and sorting and paging.

Yes, In some case you are right. When we've more data and almost in every site, data is not small really. Then it is better to use DB Query than the Eloquent Query.

In a performance issue of Eloquent VS DB I've heard that,

To insert 1000 rows for a simple table Eloquent takes 1.2 seconds andin that case DB facades take only 800 mili seconds(ms).

So Why then Eloquent ? Is't any necessary of that ?

Answer is - Eloquent is also necessary. Cause-

To create a better relationship and get the results in view with so much simple syntax, when there needs to join.

Eloquent is also for who have not much knowledge of SQL query.

An MVC framework follow the rules of Code Readability, Code Maintainability and which Eloquent is, you know that. A code comparison below. Obviously, Eloquent is better to read.

// In Eloquent$student = App\Student::find($id);// In DB facade$student = DB::table('student')->where('id', $id)->first();

Most important part is if we want to change other database, then raw query will be a lot much headache to us and in that case Laravel Eloquent will solve all the problems with one hand. It can handle different types Database.

So when we use Eloquent and When DB facades:

1. When we'll work on a simple and small records site with simple CRUD and there recordsare not fact, then use Eloquent there.
2. When we'll work on a lot's of records, it is better to use DB Query than Eloquent.

So, finally it is cleared that - when we'll use Database Query and When we'll use Eloquent Query.

Edit - Real Life Example

1. I'm making an University website. Which may contain maximum 5,000 teachers and 10,000 students and some notices and files. Then it is better to do this with simple Laravel Eloquent which is very much standard and readable.
2. Now I'm making a site like Stackoverflow. Which may contains more than 1,000,0000 (1 crore) posts and many more things. I will must choose the conventional DB facades there. It is more faster for searching the posts from so much records.

You can check your query performance using Laravel Debugbar (A popular package to check Eloquent/Database query performance/ execution times)

Now it's your choice. What you want to make...

You can check here a full code by code comparison with performance, memory consumption and code quality about these - https://devsenv.com/tutorials/laravel-eloquent-vs-db-query-builder-performance-and-other-statistics

Why Laravel Eloquent:

1. Executing query in a OOP manner.
2. Easy to use than raw query or query builder.
3. No binding with table schema. i.e. Even if you change your table name, no need to touch a single query(there may have 1000 query) to make it work. Just change the table name in eloquent model.
4. Relationship among tables can be maintained in an elegant way. Just mention the type of relationship, nothing else(JOIN, LEFT JOIN, RIGHT JOIN etc.) needed in query anymore to get data of related tables.
5. Queries are highly readable while written using Eloquent comparing with Query Builder.
6. You can use methods, scopes, accessors, modifier etc inside of a Model which is easy to maintain.