Hola como están?

Necesito una ayuda con un ejercicio de SQL que tomaron en un final.

El ejercicio dice... "Se ha solicitado a un programador que desarrolle una función que dado un ID de producto retorne el grado de composición del mismo. Si el elemento no es compuesto deberá retornar 0, si el elemento está compuesto por otro/s no compuesto/s deberá retornar 1 y así sucesivamente respetando los niveles del árbol de composición. El programador desarrolló la siguiente función...:

 

 

create function dbo.Nivel_composicion(@idElem int)

returns int as

BEGIN

	declare @id_composicion int,

			@id_componente int

	declare	@grado int,

			@max_grado int

			

	declare composicion cursor

	for

		select c.comp_producto, c.comp_componente

		from Composicion c

		WHERE c.comp_producto = @idElem

	

	set @grado = 0

	open composicion

	fetch composicion into @id_composicion, @id_componente

	

	if @@FETCH_STATUS != 0

	BEGIN

		close composicion

		deallocate composicion

		return 0

	END

	

	set @max_grado = 1

	WHILE @@FETCH_STATUS = 0

	begin

		if exists (SELECT * FROM Composicion c WHERE c.comp_producto = @id_componente)

		BEGIN

			SET @grado = 1 + dbo.Nivel_composicion(@id_componente)

		END

		

		IF(@grado > @max_grado)

		BEGIN

			SET @max_grado = @grado

		END 

		

		FETCH composicion into @id_composicion, @id_componente

	END

	close composicion

	deallocate composicion

	return @max_grado

END

... Sabiendo que no hay errores de sintaxis, y conociendo el DER informado [es el mismo que en los ejercicios que toma en clase, del producto compuesto] defina si la función cumple con lo solicitado. En caso que su respuesta sea positiva, detalle como mejoraría la misma para que sea más performante. En caso que su respuesta sea negativa, detalle cual es la falla o potencial falla y cómo lo solucionaría".

Ahora bien.... Hice una prueba de escritorio y este script funciona BIEN, por lo cual debería analizar como hacer más performante esto....
Si hay alguna "potencial falla" la verdad no la veo.

¿Alguna idea de qué se podría responder?
¿Conocen algún ejercicio similar en otro final?

sobre lo primero, ojo con el "funciona" http://stackoverflow.com/questions/31345...sql-server

parecería que depende de la version

respecto al segundo, a mi me tomaron de parcial hacerlo (no mejorarlo) alla por el 2011 y la posible solucion que nos mandó el profe (De pedo la tenia en el mail) fue esta

 

create function gr_composicion

(@elem int)

returns int

as

	begin

	declare @gr int,

			@gr_mayor int,

			@componente numeric(15,0)

	declare cur_elementos cursor

	for (select id_elemento_componente from composicion where id_elemento_composicion = @elem)

	

	set @gr = 0

	set @gr_mayor = 0

	if not exists (select id_elemento_componente from composicion where id_elemento_composicion = @elem)

		begin

			return 0

		end

	open cur_elementos

	fetch cur_elementos into @componente

	while @@fetch_status = 0

	begin

			set @gr = (select dbo.gr_composicion(@componente))

			set @gr = @gr + 1

			if @gr > @gr_mayor

			begin

				set @gr_mayor = @gr

			end

			fetch cur_elementos into @componente

	end

	close cur_elementos

	deallocate cur_elementos 

	return @gr_mayor

end

salvo por el select * en el if exists, es igual creo


tengo la leve sensacion de que se puede hacer sin cursor igual, solo recursivamente, pero ahora no me está saliendo

A mi me da la sensación que ni siquiera es necesario usar una función recursiva, que usando un cursor con WHILE ya sería posible ir cambiando el cómo llamás al select.... Voy a basarme en tu solución a ver hasta donde llego.

Gracias!!!

la recursividad es necesaria

pensa la composicion como un arbol

tu producto es la raiz, y los hijos son los componentes
luego, si cada componente a su vez tiene sub-componentes, son los hijos de ese (un sub-arbol).

luego tu ejercicio no es mas que averiguar la cantidad de niveles para un arbol, solo que con otras palabras

como podes tener N niveles, si o si vas a necesitar de alguna manera ir repitiendo el proceso recursivamente nivel a nivel de chequear si "hay un nivel mas abajo" (osea si el nodo en el que estoy tiene hijos)

Off-topic:

una opcion en sql server (Creo que nose ve en la cursada) es usar common table expresions (CTE) http://stackoverflow.com/questions/19641...achy-level

si te fijas con las CTE hace recursion - basicamente definis una query a un identificador y en vez de hacer la query usas el identificador y listo. Ahora, en esa query está haciendo un join de la tabla con el identificador - que es el join de la tabla con el identificador !

para este problema no creo que le podes escapar a la recursion

coincido con gonnza, me parece q no se puede escapar de la recursividad. De todas formas, no se me ocurre como volar el cursor.
Yo lo que hice fue optimizar el caso en que el grado es 0,en vez de un if(exists) un select count(*)=0, pero no se si esto hace a la funcion mas "performante".

Les dejo como hice el 3b
Describa detalladamente al menos 2 formas en las que implementaria el siguiente control en el modelo dado "los valores comp_producto y comp_componente de una misma fila nunca pueden tener el mismo valor"

forma 1:
ALTER TABLE Composicion
ADD CONSTRAINT CK_Comp
CHECK (comp_componente!=comp_producto)

forma 2:
create trigger trig
on Composicion
instead of insert
as
begin
declare @c int
declare @p int
set @c = (select comp_componente from inserted)
set @p = (select comp_producto from inserted)
if(@c!=@p)
begin
insert into Composicion(comp_componente,comp_producto)
values(@c,@p)
end
end
go

no hay diferencia perceptible entre el exists y el count(*), al menos no creo que sea una diferencia de performance como podria incluir poner o no un cursor

(01-12-2014 14:53)gonnza escribió:  no hay diferencia perceptible entre el exists y el count(*), al menos no creo que sea una diferencia de performance como podria incluir poner o no un cursor

Claro, habria que ver si cualquiera de estos 2 cambios cuenta como mas performante contra el enunciado del final. Creo que lo unico que te ahorras es la reserva de memoria y la carga del cursor en caso que sea grado 0... asi que la verdad supongo que no cuenta.