ActiveRecord vs DAO - Test memory consumption and query speed
In the process of growth and development of the project “ AutoOffice - Payment Acceptance and Full Automation of Sales ”, we were faced with the fact that when customers with more than 100,000+ contacts in their databases appear, one of the main functions responsible for creating and sending bulk email newsletters the database of customers of these online stores began to shamelessly slow down.
Rummaging through the code, it was revealed that when fetching data totaling 100,000+ records from MySQL tables using the ActiveRecord class, the memory consumption and script execution time increase significantly. It came to the point that one variable containing the result of a sample of 100,000 records consumed 644,750,972 bytes (614 MB) of RAM, and the script execution time exceeded 50 seconds.
Because 100,000 entries, to put it mildly, is not the ultimate dream, and the number of customers of the AutoOffice service has long been in the thousands, we could not leave this problem unattended and take measures to eliminate it. As a result, the function described above was completely rewritten into samples using the well-known DAO, which significantly reduced resource consumption and increased script execution speed.
During the transition, our programmers were not too lazy and made several tests that clearly demonstrate the comparative characteristics of using ActiveRecord and DAO when trying to fetch from MySQL 1, 5, 10, 50, 100, 500, 1,000, 5,000, 10,000, 50,000 and 100,000 entries. With the results of these tests, I suggest you familiarize yourself right now.
Immediately make a reservation that when conducting similar tests on your project, the numbers may differ slightly, because I don’t know what computer capacities you have, what data you choose from the database, and what means for measuring indicators you will use.
In my case, standard PHP functions were used to measure the memory consumption and calculate the script execution time:
The purpose of this article is to show the dynamics of changes in the amount of consumed resources and the time spent on executing scripts when using ActiveRecord and DAO.
And so for starters, a pivot table showing an increase in resource consumption and runtime of scripts using MySQL to access the database:
It is important to understand that in this table the runtime is 0 seconds. means that it was spent on receiving data and storing the result in a variable for less than 1 second.
Now let's look at the dynamics of growth in RAM consumption, depending on the increase in the number of returned rows with data from the MySQL database:
As you can see from the graph, significant changes when using both approaches begin only if more than 1,000 rows are included in the selection result. But, indicators of memory consumption when using DAO are much lower than when using ActiveRecord. And in peak performance, the DAO consumes 5.91 times less resources than ActiveRecord.
Of course, some “specialists” may say that given the continuous decrease in the cost of iron (physical and RAM), as well as the continuous increase in the maximum available server capacity, this problem can be solved by simply purchasing more iron. But, looking at the dynamics of growth in resource consumption, it can be noted that starting from 1,000 records, the growth in resource consumption becomes almost linear, i.e. if 100,000 records consume 614 MB. RAM, then 1,000,000 records, according to conservative estimates, will require 6,140 MB. (6 Gb.)
Now let's look at the dependence of the script execution time on the number of returned rows satisfying the database query:
Here everything is already much more interesting!
The number of returned values when using DAO has practically no effect on the execution time of the script. At all stages of testing, this indicator did not exceed 1 second.
But ActiveRecord behaved almost the same way as with the consumption of resources. It took him 52 seconds to get the result of 100,000 records from the MySQL database, which is completely unacceptable for a serious Internet project. No sensible user of your Internet resource will wait so long for the page to load with the information he needs (of course, provided that you are not a monopolist in your niche).
As a result, I want to say the following:
I really enjoy working with ActiveRecord. It is convenient and very well suited for solving a whole group of tasks that do not require obtaining large amounts of data. But, if you are working with large amounts of data, if your queries must return more than 1,000 records, then it is better to use something else right away to build queries and obtain sample results, for example, your favorite DAO or even cooler solutions.
If you have questions for me or you want to share your experience in making large selections from the MySQL database, then write in the comments, I will be extremely grateful for the valuable content.
Rummaging through the code, it was revealed that when fetching data totaling 100,000+ records from MySQL tables using the ActiveRecord class, the memory consumption and script execution time increase significantly. It came to the point that one variable containing the result of a sample of 100,000 records consumed 644,750,972 bytes (614 MB) of RAM, and the script execution time exceeded 50 seconds.
Because 100,000 entries, to put it mildly, is not the ultimate dream, and the number of customers of the AutoOffice service has long been in the thousands, we could not leave this problem unattended and take measures to eliminate it. As a result, the function described above was completely rewritten into samples using the well-known DAO, which significantly reduced resource consumption and increased script execution speed.
During the transition, our programmers were not too lazy and made several tests that clearly demonstrate the comparative characteristics of using ActiveRecord and DAO when trying to fetch from MySQL 1, 5, 10, 50, 100, 500, 1,000, 5,000, 10,000, 50,000 and 100,000 entries. With the results of these tests, I suggest you familiarize yourself right now.
Immediately make a reservation that when conducting similar tests on your project, the numbers may differ slightly, because I don’t know what computer capacities you have, what data you choose from the database, and what means for measuring indicators you will use.
In my case, standard PHP functions were used to measure the memory consumption and calculate the script execution time:
- time () - Returns the number of seconds elapsed from the beginning of the Unix Age (The Unix Epoch, January 1, 1970, 00:00:00 GMT) to the current time.
- memory_get_usage () - Returns the amount of memory in bytes that was allocated to the PHP script at the moment
The purpose of this article is to show the dynamics of changes in the amount of consumed resources and the time spent on executing scripts when using ActiveRecord and DAO.
And so for starters, a pivot table showing an increase in resource consumption and runtime of scripts using MySQL to access the database:
It is important to understand that in this table the runtime is 0 seconds. means that it was spent on receiving data and storing the result in a variable for less than 1 second.
Now let's look at the dynamics of growth in RAM consumption, depending on the increase in the number of returned rows with data from the MySQL database:
As you can see from the graph, significant changes when using both approaches begin only if more than 1,000 rows are included in the selection result. But, indicators of memory consumption when using DAO are much lower than when using ActiveRecord. And in peak performance, the DAO consumes 5.91 times less resources than ActiveRecord.
Of course, some “specialists” may say that given the continuous decrease in the cost of iron (physical and RAM), as well as the continuous increase in the maximum available server capacity, this problem can be solved by simply purchasing more iron. But, looking at the dynamics of growth in resource consumption, it can be noted that starting from 1,000 records, the growth in resource consumption becomes almost linear, i.e. if 100,000 records consume 614 MB. RAM, then 1,000,000 records, according to conservative estimates, will require 6,140 MB. (6 Gb.)
Now let's look at the dependence of the script execution time on the number of returned rows satisfying the database query:
Here everything is already much more interesting!
The number of returned values when using DAO has practically no effect on the execution time of the script. At all stages of testing, this indicator did not exceed 1 second.
But ActiveRecord behaved almost the same way as with the consumption of resources. It took him 52 seconds to get the result of 100,000 records from the MySQL database, which is completely unacceptable for a serious Internet project. No sensible user of your Internet resource will wait so long for the page to load with the information he needs (of course, provided that you are not a monopolist in your niche).
As a result, I want to say the following:
I really enjoy working with ActiveRecord. It is convenient and very well suited for solving a whole group of tasks that do not require obtaining large amounts of data. But, if you are working with large amounts of data, if your queries must return more than 1,000 records, then it is better to use something else right away to build queries and obtain sample results, for example, your favorite DAO or even cooler solutions.
If you have questions for me or you want to share your experience in making large selections from the MySQL database, then write in the comments, I will be extremely grateful for the valuable content.