For a particular page on a website, I was tasked to create a 'tables view' so that the site admins could see/add/edit/delete data from our tables. Since our database is riddled with foreign keys and I'm somewhat new at this (php + laravel), my implementation was as follows:
- Check which foreign keys exist for this table.
- For each of those keys:
- Load all the table (for that key) into an array
mapping
(key/value pair) - Go through all of the data to-be-transformed, and transform it using
mapping
- Load all the table (for that key) into an array
- return
I did this to avoid multiple calls to the db, especially since the other tables were relatively small (so there were many repeated values).
Here's the problem I faced: In some instances, over long ranges, we were getting a memory exception (ie all memory consumed). After debugging, I found that my 12 MB of data is using 1.2GB of memory as a peak! I went through my function to figure out what's causing it, and I found the culprit:
// apply mapping to all entries
if($changed_value == true) {
// for $i in count(data_column). $col is the foreign key (id) in the $raw_data.
try {
$final_data[$i]->{$new_col_name} = $mapping[$raw_data[$i]->{$col}];
} catch (\Exception $e) {
$final_data[$i]->{$new_col_name} = $raw_data[$i]->{$col};
}
}
I didn't want the load times to be long, so I didn't check if the id exists in the mapping, opting to instead wrap it in a try-catch and just put the id as is if it fails (ie without mapping the id to the value). The memory footprint before this loop was only 24MB, and it increased to around 240MB after it, but only when the catch happened often. I fixed this problem by simply checking if the data was null before applying the mapping, and the max memory used went down from > 1GB to < 40MB.
I have obviously solved the problem, but I don't understand why it happened especially since the extra memory was removed after for the next foreign key (best bet: on exception, either the stack trace is stored for reasons, or the program is stored as an entry point).
So as for the question:
- Why does this happen? Where are other cases where it could happen so I can be aware of it in advance?