mysql - tablas - select recursivo

Rendimiento de procedimientos almacenados recursivos en MYSQL para obtener datos jerárquicos

Question

Tengo un empleado de mesa como,
empleado (emp_id int clave primaria, emp_name varchar (50), mngr_id int)

y aquí mngr_id anularía o contendría emp_id válido. De esta forma forma la jerarquía de empleados en la organización.

Para atravesar toda la jerarquía tuve que escribir el procedimiento almacenado recursivo. (En Oracle es fácil usando CONNECT BY ... COMIENZA CON)

Entonces, la pregunta es cuál es el impacto en el rendimiento de dicho procedimiento almacenado dado que el nivel de jerarquía no iría más allá de 10 niveles.

¿Hay alguna otra forma de lograr lo mismo?

Answer 1

Tomalak: "... Haría la recursión en la capa de presentación de mi aplicación ..."

Esto significa que cada vez que se produce la recursión, se envía otra llamada al servidor de la base de datos desde la capa de presentación. Eso sería increíblemente lento.

Answer 2

una solución iterativa de adyacencia bastante simple db server side solution: http://pastie.org/1056977

delimiter ;

drop procedure if exists employee_hier;

delimiter #

create procedure employee_hier
(
in p_emp_id smallint unsigned
)
begin

declare p_done tinyint unsigned default(0);
declare p_depth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier values (null, p_emp_id, p_depth);

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while p_done <> 1 do

    if exists( select 1 from employee e inner join hier on e.boss_id = hier.emp_id and hier.depth = p_depth) then

        insert into hier select e.boss_id, e.emp_id, p_depth + 1 
            from employee e inner join emps on e.boss_id = emps.emp_id and emps.depth = p_depth;

        set p_depth = p_depth + 1;          

        truncate table emps;
        insert into emps select * from hier where depth = p_depth;

    else
        set p_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 b.emp_id as boss_emp_id,
 b.name as boss_name,
 hier.depth
from 
 hier
inner join employee e on hier.emp_id = e.emp_id
inner join employee b on hier.boss_id = b.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;


call employee_hier(1);
call employee_hier(3);

Answer 3

Con respecto a la última pregunta: Hay algunas opciones agradables en "¿Cuál es la forma más eficiente / elegante de analizar una tabla plana en un árbol?"

También debería considerar almacenar en caché el resultado de la recursión en una tabla intermedia. Si cambia eso solo al actualizar su tabla de jerarquía, el rendimiento de recursión será insignificante.

EDITAR: Personalmente, hago la recursión en la capa de presentación de mi aplicación, por ejemplo en el servidor web. Esto proporciona una mayor flexibilidad en comparación con lo que se puede lograr en SQL, y también puede usar el almacenamiento en caché de nivel de sesión o de aplicación. (Sin embargo, el uso de una tabla de DB preconstruida que se mantiene actualizada con un desencadenador nunca lo deja con un caché desactualizado).