As I am a great fan and working on laravel framework for a long time, I want to explore how to build up our web application with better performance in laravel. According to various surveys conducted, The laravel has elected many times as one of the top frameworks in php. It has combined the best features from other frameworks like ASP.NET MVC, Ruby on Rails and Sinatra… Laravel provides an abundance of great features that ease the development such as authentication, routing, database managing, sessions, and caching. Laravel is a full-stack framework because it manages all essential tasks from database management to web serving.
Out of all the features there existed, we are focusing on performance analysis of database management techniques in laravel through this blog. Trying to explore which techniques could be used to get optimum performance in different use cases. I am planning to focus on the performance of Eloquent Vs Raw Sql through this blog. We would keep investigating Eloquent Vs Query Builder through the next blog. Laravel Interacts with the database in three different ways.
Eloquent ORM: – The Eloquent ORM(Object-relational mapper) communicates with the database using Active Record implementation. The Active Record pattern is a way of approach to accessing data in the database. It allows relational databases to be represented in the object-based code.
Query Builder:- Query builder provides an interface to writing queries by chaining sequence of builder functions. It uses PDO parameter binding as its backend support.
Raw SQL: – Programing language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System.
Work Flow of Eloquent
Eloquent maps a relational database table into the object-oriented way of approach. It creates a model class to represent each table in the database. The instance of this model class represents each row of the associated relational table. The CRUD operations that perform on model instance affects directly upon the respective rows in the database table. Eloquent adds an extra layer of abstraction which will cause to consume more memory and increase CPU usage. Always takes place an extra query translation process behind the scene of laravel that appears to be an overhead in contrast with other database management techniques. In some cases, a translated query looks very weird as it confines numerous additional queries to perform a simple join query. For example in the case of using eloquent relations. Relations allow us to access related models from another model. But if we’re not careful, it’s easy to introduce an (n + 1) problem which essentially means that we execute additional queries every time we access the relationship that was not eager loaded. We could manage this issue some extent using eager loading that could be used to reduce comparatively the amount of queries executing at the backend. Besides some performance issues, Eloquent has many advantages as well.
1)Database Independent – There is no need to stress over the particular to the database. The database can be changed with minimal code change anytime.
2)Relationships – Related models can be fetched through one model instance by defining the relations.
3)Caching – Eloquent has support for cache management, the objects can be cached in memory which reduces the load on the database.
4)Concurrency support – The same data can be accessed and updated by multiple users simultaneously.
Work Flow of Query Builder
Query Builder provides an easy interface to perform most of the database query operations such as CRUD, complex join queries, etc. Since the query builder works with PDO, it provides high security against sql injection vulnerabilities. The data are sanitizing and passing as parameterized before feeding into the query builder. The DB facade helps to build the queries directly unlike the eloquent ORM. The query builder seems comparatively more flexible when building complex queries that difficult to be managed with Eloquent. Another benefit of using the query builder is it abstracts you away from the language used by your storage of choice, i.e. MySQL, Oracle, SQLite, etc. If you want to switch the database at some point, you may have the possibility to get stuck with a lot of query refactoring. When you are using a query builder, you do not need to change the queries after migrating to a new database.
Work Flow of Raw Queries
Laravel allows running raw queries as well. We can run raw queries using laravel DB facade. The functions such as DB::raw(), DB::statement and DB::unprepared, etc allows us to select whatever we want and basically write raw SQL statements. But this approach sometimes discouraged due to the possibility of lack of security, maintainability, and portability for our application. But in some cases, raw queries are the preferable option to be performed complex querying in the database.
Performance Analysis
As per the performance examinations took place for all three techniques, there are experienced significant performance variation among these laravel querying techniques. Few studies show different results even in the case of basic CRUD operations. To compare performance between Eloquent ORM and Raw SQL, it is necessary that the tests should be performed with the same software and hardware platforms and the same data for all techniques in order to make a fair comparison between all techniques. Once we have performed the performance tests, it will give us response time in milliseconds which we are going to gather and analyze. We will compare the response time of each database CRUD operation for these two techniques.
There have used Laravel Debugbar for one of the performance test analysis to test the CRUD operations. debug bar was displayed at the base of the page when the performance test was executed and it showed us the executed SQL queries and the total response time of the queries that were executed. There have a lot of performance tests have been conducted to find the performance of CRUD operation between Eloquent and Raw SQL.let’s go through one of these.
In case of checking the performance of Insert operation between Raw Sql and Eloquent
In one of the performance testing, there have run insert tests with various quantities of articles ranging from 1000 to 10000 for both techniques. We ran 1st iteration by inserting 1000 articles and then we expanded by 1000 articles in the following iterations. Both techniques have been tested up to 10000 articles. We could see the Raw Sql performed far better than eloquent. See the chart below.
In case of checking the performance of update operation between Raw Sql and Eloquent
There have run update tests with various quantities of articles ranging from 1000 to 10000 for both techniques. Executed the updation 10 times by 1000 each time. Update operation performed better in raw sql rather than eloquent.
In case of checking the performance of select operation between Raw Sql and Eloquent
There has run select operation test in iterations to fetch articles content from various tables in both techniques. Ran each iteration 3 times and calculated average response time and standard deviation.
The result of Raw sql shown below.
The result of Eloquent shown below.
The tables show above displaying the average response time for each join in Eloquent ORM and Raw SQL. The average response time was calculated during the execution of the select operation in all iterations for both techniques. The tables show the increment on average response time for both techniques while increasing the number of joins in every test run. If we compare average response time in both tables we can clearly see that Raw SQL performed better than Eloquent ORM in each test.
Conclusion
The Test result of CRUD operations shows that Raw sql was performed better than Eloquent ORM. So it proves the Raw Sql seems to be more preferable in the contrast of performance for the large applications with a huge amount of data and where many requests to the database at a time is involved as it created ORM object for each request and consumed more memory and increased the CPU usage rather than executing the queries. We can conclude that Eloquent ORM is good for building small to medium-sized applications were required a relatively small amount of data. Typically for tasks like inserting a single row into a database or retrieving few rows from the database. Because for such simple queries, there is probably not much response time difference between Eloquent ORM and Raw SQL and the support for cache management that caches the Eloquent ORM object that it has seen before can boost the performance as well. Raw SQL is preferable for the applications that are dealing with a huge amount of data, bulk data loads and complex queries which our test results have proved. Eloquent ORM provides benefits and advantages that one simply cannot miss, It can enhance maintainability, portability, and productivity of the application. So in many cases, both techniques can be used in different parts of the web application where it makes sense for the web application and the performance.
