I am finding whenever the procedure of updating my database is pretty complex, and requires multiple tables, that doing it all in Laravel or in PHP takes 10-100 times longer to execute than just coming up with the very complicated storedProcedure to keep on the mysql side and run whenever I need it.
Is this a "no-duh" type thing to experienced developers? That doing the full query on the mysql side will always win in terms of speed in execution?
The reason I ask it this way is because my gut tells me that Laravel has all these amazing built in things that make coding the solution fairly easy at times, but then when I take the time to write a stored procedure it has typically blown the laravel execution time away by a large factor.
Assuming what we need at the end of the procedure can actually be provided by either a stored procedure manipulating the data results, and can also be achieve by manipulating the results inside laravel after a series of logic and ->gets and what not. (Meaning the end result is purely "apples-to-apples" and we end up with the exact same structured associative data array/record set in both cases.
Is it always going to be "faster" to stay in mysql for the query as long in the data manipulation process as possible, and what we are trying to weigh is "how much faster" type thing? I am just so new to it all I find myself doing all the function building in Query Builder and then not being happy with the speed of the results and then just redoing it all as a simple:
DB::select("CALL myStoredProcedure());
Here is the latest migration of the most recent stored procedure I wrote. This sp excecutes in an average of around 30-90 milliseconds, and the series of Laravel queries necessary to make it return the exact same dataset was taking 10-12 full seconds consistently.
Here is what I am doing exactly.
I have 3 tables that each pull data independently from 3 different API external sources.
I store the results in JSON type columns raw and simple. I do not parse the incoming json into table columns.
A 4th table is a combination of all three of these tables and it does have independent columns to represent some of the property values of the fetched JSON data from the other three tables.
Worth noting: I am stuck in mysql 5.7 and MariaDB 10.4 which is why I am not using more robust JSON functions in the MYSQL.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
DB::unprepared(<<<SQL
DROP PROCEDURE IF EXISTS updateMasterDataTable
SQL
);
DB::unprepared(<<<SQL
CREATE PROCEDURE `updateMasterDataTable`()
BEGIN
INSERT INTO players (
pid, eid, sid, yid, ifa, full_name, first_name, last_name, short_name,
last_game_played, ind_num, team_id, created_at, updated_at
)
SELECT
COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.id')),
JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.pid')),
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.esx_id'))
) AS pid,
COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.id')),
JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.pid')),
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.esx_id'))
) AS eid,
COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.sid')),
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.sid'))
) AS sid,
COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.yid')),
JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.yid'))
) AS yid,
CASE
WHEN COALESCE(
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.is_free_agent')),
''
) NOT IN ('True', '1', 'null')
THEN 0
ELSE 1
END AS ifa,
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.full_name')), 'null'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.fullName')), 'null')
) AS full_name,
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.firstName')), 'null'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.first_name')), 'null'),
SUBSTRING_INDEX(
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.full_name')), 'null'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.displayName')), 'null')
), " ", 1
)
) AS first_name,
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.lastName')), 'null'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.last_name')), 'null'),
TRIM(
SUBSTRING_INDEX(
COALESCE(
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(slx.data, '$.full_name')), 'null'),
NULLIF(JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.displayName')), 'null')
), " ", -1
)
)
) AS last_name,
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.cbs_short_name')) AS short_name,
JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.last_game_played')) AS last_game_played,
JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.id_num')) AS id_num,
JSON_UNQUOTE(JSON_EXTRACT(esx.data, '$.team_id')) AS team_id,
NOW() AS created_at,
NOW() AS updated_at
FROM raw_players_esxs AS esx
INNER JOIN raw_players_tank01s AS tank
ON tank.pid = esx.pid
INNER JOIN raw_players_slxs AS slx
ON slx.sid = JSON_UNQUOTE(JSON_EXTRACT(tank.data, '$.sid'))
WHERE esx.pid IN (
SELECT DISTINCT
JSON_UNQUOTE(JSON_EXTRACT(t.game_pids, CONCAT('$[', n.n, ']'))) AS pid
FROM live_game_pids t
JOIN (
SELECT n1.n + n2.n * 10 + n3.n * 100 + n4.n * 1000 AS n
FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) n1
CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) n2
CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) n3
CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9) n4
WHERE n1.n + n2.n * 10 + n3.n * 100 + n4.n * 1000 < (SELECT MAX(JSON_LENGTH(game_pids)) FROM live_game_pids)
) n
ON JSON_UNQUOTE(JSON_EXTRACT(t.game_pids, CONCAT('$[', n.n, ']'))) IS NOT NULL
)
ON DUPLICATE KEY UPDATE
esx_id = VALUES(esx_id),
sid = VALUES(sid),
yid = VALUES(yid),
is_free_agent = VALUES(is_free_agent),
full_name = VALUES(full_name),
team = VALUES(team),
first_name = VALUES(first_name),
last_name = VALUES(last_name),
short_name = VALUES(cbs_short_name),
last_game_played = VALUES(last_game_played),
id_num = VALUES(jersey_num),
team_id = VALUES(team_id),
updated_at = NOW();
END;
SQL
);
}
/**
* Reverse the migrations.
*/
public function down(): void
{
DB::unprepared(<<<SQL
DROP PROCEDURE IF EXISTS updateMasterDataTable
SQL
);
}
};
Is it fair to assume for something with this many moving parts that will require multiple Models, Multiple queries, multiple ->each loops and ->filter and ->map and ->keyBy and ->toArray() that it will just always be a ton faster to take the time to create the storedProcedure?
OR is it more accurate to say that my particular USE CASE just happens to be that way?
