soporta - ¿Puede MySql anidado SP ser un cuello de botella?
optimizar consultas mysql explain (2)
Si es posible. No tenemos medidas de cuánto tiempo lleva esto, pero uno puede esperar que una tabla temporal cause cierta sobrecarga a medida que la crea, escribe datos en ella, la consulta y luego la deja caer. ¿Con qué frecuencia se llama esto?
Además, se sabe que los procedimientos almacenados de MySQL son bastante ineficientes. No retienen la forma compilada del procedimiento, como lo hacen en Oracle y otras marcas RDBMS. En otras palabras, cada sesión vuelve a compilar cada procedimiento que utiliza la primera vez que se llama.
Primero, sugeriría eliminar la tabla temporal. Simplemente diseñe el procedimiento anidado para construir la consulta SQL correcta como una cadena, y devuelva esa cadena. Entonces el procedimiento externo ejecuta la consulta es su resultado. Debería poder omitir la creación / caída de la tabla temporal y la inserción en la tabla temporal.
Segundo, si estuviera diseñando esta aplicación, no veo ninguna necesidad de ningún procedimiento almacenado. Recomiendo escribir código para construir la consulta SQL correcta en su aplicación, y luego simplemente ejecutar esa consulta desde la aplicación.
Tenemos este MySQL SP, que llama un SP anidado. Parece que NO funciona bien bajo carga.
Es posible que este SP se vuelva lento bajo carga porque llama a un SP anidado y usa tablas temporales para pasar los datos al SP principal.
DELIMITER $$
drop procedure if exists `GeoAreaFlattened_Select`;
create procedure `GeoAreaFlattened_Select`(
_areas MEDIUMTEXT,
_comparisonGroup varchar(21844),
_parentArea varchar(21844),
_areaType varchar(21844)
)
begin
drop temporary table if exists areas;
-- areas
call CreateAreas(_areas, _comparisonGroup, _parentArea, _areaType);
SELECT
areas.ID,
areas.Code,
areas.Name,
areas.LevelId,
GeoAreaLevel.Name AS AreaTypeLabel,
GeoAreaLevel.Identifier AS AreaTypeIdentifier
FROM
areas
INNER JOIN
GeoAreaLevel
ON
areas.levelid = GeoAreaLevel.id
ORDER BY areas.name ASC;
drop temporary table areas;
end
El SP anidado:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
drop procedure if exists `CreateAreas`;
DELIMITER $$
CREATE PROCEDURE `CreateAreas`(
_areas varchar(21844),
_comparisonGroup varchar(21844),
_parentArea varchar(21844),
_areaType varchar(21844)
)
BEGIN
-- create temporary table "areas"
-- fill with area ids
create temporary table areas (
id int not null,
code varchar(30),
name varchar(100),
shortName varchar(100),
levelid int not null,
sortOrder int not null,
key (id)
);
-- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group
if (_areas is not null) then
set @sql = concat(''insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in ('''''', replace(_areas, '','', '''''',''''''), '''''')'');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
elseif (_comparisonGroup is not null) then
-- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
insert into areas (id, code, name, shortName, levelid, sortOrder)
select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
from ComparisonGroupGeoAreaLink
INNER JOIN
GeoArea
ON GeoArea.ID = GeoAreaID
where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
and IsMember = 1;
elseif (_parentArea is not null and _areaType is not null) then
-- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
insert into areas (id, code, name, shortName, levelid, sortOrder)
select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
from (select id from GeoArea where Code = _parentArea) as t
INNER JOIN
GeoAreaLinkCache c
ON
c.ParentAreaID = t.id
inner join GeoArea a
on c.ChildAreaID = a.ID
INNER JOIN
(select id from GeoAreaLevel where Identifier = _areaType) as l
ON
a.LevelID = l.id;
elseif (_areaType is not null) then
-- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
set @sql = concat(''insert into areas (id, code, name, shortName, levelid, sortOrder)
select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
from
(select id from GeoAreaLevel where Identifier in ('''''', replace(_areaType, '','', '''''',''''''), '''''')) l
INNER JOIN
GeoArea a
ON
a.LevelID = l.id'');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
end if;
END
Parece que los SP anidados NO fueron el principal cuello de botella.
Cambié las tablas temporales al motor MEMORY y realmente funcionó y creó una diferencia INCREÍBLE. La solución fue sugerida en
create temporary table areas (
id int not null,
code varchar(30),
name varchar(100),
shortName varchar(100),
levelid int not null,
sortOrder int not null,
key (id)
) ENGINE=MEMORY;