The application that I've been developing went live on Monday. Overall things are working out pretty well. However, I am having an issue with one API call in particular. It was taking a very long time to respond, and when certain parameters were passed, PHP was running out of memory. I fired up profiler to see what I could do about the situation. I simplified the query, but PHP is still gobbling up tons of memory whenever I query the database.
I'm attempting to select data across three tables. When I use the ORM like this:
$result = Ticket::query()
->where('client_id', $this->current_client->id)
->related(array('location', 'customer'));
->get();
The matching data set is a little less than 10,000 records. This query takes over 20 seconds and the application hogs up nearly 200 MB of memory. I replaced "get()" with "get_query()->compile()" to find the exact SQL query that is being run. I ran the same query directly on the database using MySQLi:
$query = "SELECT `t0`.`id` AS `t0_c0`, `t0`.`client_id` AS `t0_c1`, `t0`.`user_id` AS `t0_c2`, `t0`.`category_id` AS `t0_c3`, `t0`.`original_rep_id` AS `t0_c4`, `t0`.`current_rep_id` AS `t0_c5`, `t0`.`status_id` AS `t0_c6`, `t0`.`appointment_date` AS `t0_c7`, `t0`.`sale_amount` AS `t0_c8`, `t0`.`sold_date` AS `t0_c9`, `t0`.`interest_level` AS `t0_c10`, `t0`.`campaign` AS `t0_c11`, `t0`.`install_date` AS `t0_c12`, `t0`.`created_at` AS `t0_c13`, `t0`.`updated_at` AS `t0_c14`, `t0`.`customer_id` AS `t0_c15`, `t0`.`deleted_at` AS `t0_c16`, `t0`.`cf0` AS `t0_c17`, `t0`.`cf1` AS `t0_c18`, `t0`.`cf2` AS `t0_c19`, `t0`.`cf3` AS `t0_c20`, `t0`.`cf4` AS `t0_c21`, `t0`.`cf5` AS `t0_c22`, `t0`.`cf6` AS `t0_c23`, `t0`.`cf7` AS `t0_c24`, `t0`.`cf8` AS `t0_c25`, `t0`.`cf9` AS `t0_c26`, `t0`.`location_id` AS `t0_c27`, `t0`.`comments` AS `t0_c28`, `t1`.`id` AS `t1_c0`, `t1`.`address` AS `t1_c1`, `t1`.`city` AS `t1_c2`, `t1`.`state` AS `t1_c3`, `t1`.`zip` AS `t1_c4`, `t1`.`lat` AS `t1_c5`, `t1`.`lng` AS `t1_c6`, `t1`.`created_at` AS `t1_c7`, `t1`.`updated_at` AS `t1_c8`, `t2`.`id` AS `t2_c0`, `t2`.`client_id` AS `t2_c1`, `t2`.`category_id` AS `t2_c2`, `t2`.`name` AS `t2_c3`, `t2`.`email` AS `t2_c4`, `t2`.`phone` AS `t2_c5`, `t2`.`alt_phone` AS `t2_c6`, `t2`.`created_at` AS `t2_c7`, `t2`.`updated_at` AS `t2_c8`, `t2`.`location_id` AS `t2_c9` FROM `tickets` AS `t0` LEFT JOIN `locations` AS `t1` ON (`t0`.`location_id` = `t1`.`id`) LEFT JOIN `customers` AS `t2` ON (`t0`.`customer_id` = `t2`.`id`) WHERE `t0`.`client_id` = '2' ORDER BY `t0`.`appointment_date` ASC";
$mysqli = new \mysqli($dbhost, $username, $password, $database);
$mysqli->query($query);
The memory usage drops from almost 200 MB to just under 9 MB, and execution time is less than three seconds. Am I attempting to use the ORM beyond it's purpose? I didn't think 10,000 records was a whole lot of data.
ORM hydrates the result and creates an object for every row in the resultset. And it caches all results, so even after destruction of $result these objects remain in memory.
This query means you now have 10.000 Model objects in memory. It's the hydration (converting the DB result to object data) that takes most time. Use DB queries instead.
Donate
