Ayuda con optimizacion de consulta

[davicomeh]

Tengo una tabla de usuarios, donde cada usuario esta relacionado a un usuario de la misma tabla (campo llamado idpadre)

De modo que se puede dar la siguiente Jerarquia:

-Administrador
-Supervisor
-Gerente
-Coordinador
-Asesor

Cada usuario tiene su propios registros capturados (en mi sistema son prospectos)

Tengo una consulta para sacar todos los prospectos de un usuario, y de todos sus subordinados. Es decir, yo Gerente veo todos mis registros, junto con los de mis Coordinadores asignados, junto con los de los Asesores asignados a mis Cordinadores....

La manera en que la tengo funciona, y sin problemas, solo que digamos tengo poco en sql y la manera en que la tengo se que no es la mas optima, ya que utilizo muchos select (como 11....) y tengo entendido que eso no es bueno....

Mi consulta es mas o menos la siguiente:

Código SQL:

ALTER PROCEDURE [dbo].[spObtenerProspectos]
@IDUSUARIO          NUMERIC(18,0)
AS
 
SELECT IDPROSPECTO
 FROM prospectosnew 
 
 WHERE PROSPECTOSNEW.IDUSUARIO IN (SELECT IDUSUARIO FROM USUARIOSNEW WHERE USUARIOSNEW.idpadre=@IDUSUARIO )
                        OR PROSPECTOSNEW.IDUSUARIO IN (SELECT idUsuario FROM USUARIOSNEW
                        WHERE IDPADRE IN ( SELECT idUsuario FROM usuariosnew WHERE idpadre=@IDUSUARIO))
                        OR PROSPECTOSNEW.IDUSUARIO IN (SELECT idUsuario FROM USUARIOSNEW
                        WHERE IDPADRE IN ( SELECT idUsuario FROM usuariosnew WHERE idpadre IN (SELECT IDUSUARIO FROM USUARIOSNEW WHERE USUARIOSNEW.idpadre=@IDUSUARIO)))
                        OR PROSPECTOSNEW.IDUSUARIO IN (SELECT idUsuario FROM USUARIOSNEW
                        WHERE IDPADRE IN ( SELECT idUsuario FROM usuariosnew WHERE idpadre IN (SELECT IDUSUARIO FROM USUARIOSNEW WHERE USUARIOSNEW.idpadre IN (SELECT IDUSUARIO FROM USUARIOSNEW WHERE USUARIOSNEW.idpadre=@IDUSUARIO))))
                        OR PROSPECTOSNEW.IDUSUARIO=@IDUSUARIO
 

Mi tabla de usuarios no tiene mas de 100 registros

y la tabla de prospectos estamos hablando que pueden ser 10mil, 20mil, 30mil registros...

alguna manera en que pueda evitarme el uso de tantos select y optimizar mi consulta????

[flaviovich]

En primer lugar, tu procedimiento debe llamarse ObtenerProspectos segun las buenas practicas de SQL.
Segundo, explica que debe hacer tu PA porque esta raro.

[-rommel_]

yo me imagino que es un Reporte parecido a la tabla de "Maestro de Cuentas Contables", cada cuenta con su Cuenta Padre, y su cuenta hijo... algo asi necesitas? davicomeh?

[davicomeh]

Primero que nada muchas gracias por responder.
Tomare en cuenta lo de la nomenclatura de los SP

Quizas poniendo las 2 tablas ayudaria a explicarme mejor

USUARIOS
| idUsuario | idPadre |

PROSPECTOS
| idProspecto | idUsuario |

Las relaciones seria idPadre a idUsuario de la misma tabla de Usuarios
y idusuario de Prospectos con idUsuario de Usuarios

De modo que cada prospecto tiene un idUsuario que corresponde al usuario que lo agrego

En la consulta que tengo (mal hecha y por lo visto inentendible) lo que hago es lo siguiente:

Sacar todos los usuarios que este debajo de 1 usuario espesifico, es decir, los hijos, los hijos de los hijos, los hijos de los hijos de los hijos, etc, etc. Para poder ver todos los prospectos de ese usuario y los prospectos de sus subordinados.

Es decir, yo Gerente, puedo ver mis prospectos, mas ademas los prospectos de mis Coordinadores, mas aparte los prospectos de los Asesores de mis Cordinadores. Y se aplicaria misma logica para administrador, supervisor, etc.

Espero haberme dado a entender ahora si =S

[flaviovich]

Prueba asi:

Código SQL:

ALTER PROCEDURE [dbo].[ObtenerProspectos]
@IDUSUARIO          NUMERIC(18,0)
AS
 
SELECT IDPROSPECTO
FROM prospectosnew P
LEFT JOIN USUARIOSNEW H1 ON P.idUsuario = H1.idUsuario
LEFT JOIN USUARIOSNEW H2 ON H1.idPadre = H2.idUsuario
LEFT JOIN USUARIOSNEW H3 ON H2.idPadre = H3.idUsuario
LEFT JOIN USUARIOSNEW H4 ON H3.idPadre = H4.idUsuario
LEFT JOIN USUARIOSNEW H5 ON H4.idPadre = H5.idUsuario
WHERE H1.idUsuario = @IDUSUARIO
OR H2.idUsuario = @IDUSUARIO
OR H3.idUsuario = @IDUSUARIO
OR H4.idUsuario = @IDUSUARIO
OR H5.idUsuario = @IDUSUARIO
 

[davicomeh]

MUCHISIMAS GRACIAS flaviovich, la verdad es que asi queria hacerlo, pero no sabia como hacer joins a una misma tabla, tardé en responder por que estaba probando la consulta, haciendo comparaciones entre resultados con la anterior query, y funciono perfecto.

Dejo la consulta completa (que basicamente seria la misma que me pusiste), por si alguien quiere aplacar su curiosidad de para que fin quiero la query hehe

Código SQL:

ALTER PROCEDURE [dbo].[spObtenerProspectos]
@IDUSUARIO          NUMERIC(18,0)
AS
 
SELECT P.FECHAALTA, P.IDPROSPECTO,(P.NOMBRES + ' ' + P.PATERNO + ' ' + P.MATERNO) AS PROSPECTO,
    CAMPUS.CAMPUS,(PERSONALNEW.NOMBRES  + ' ' +  PERSONALNEW.PATERNO + ' ' + PERSONALNEW.MATERNO) AS ASESOR,
    P.IDUSUARIO, P.IDCAMPUS, P.IDESCUELA, P.IDNEGOCIO, P.IDCOLONIA, P.TELEFONO1
FROM prospectosnew P
INNER JOIN CAMPUS ON P.IDCAMPUS=CAMPUS.IDCAMPUS
LEFT JOIN USUARIOSNEW H1 ON P.idUsuario = H1.idusuario
LEFT JOIN USUARIOSNEW H2 ON H1.idPadre = H2.idUsuario
LEFT JOIN USUARIOSNEW H3 ON H2.idPadre = H3.idUsuario
LEFT JOIN USUARIOSNEW H4 ON H3.idPadre = H4.idUsuario
LEFT JOIN USUARIOSNEW H5 ON H4.idPadre = H5.idUsuario
INNER JOIN PERSONALNEW ON H1.IDPERSONAL=PERSONALNEW.IDPERSONAL
WHERE H1.idUsuario = @IDUSUARIO
OR H2.idUsuario = @IDUSUARIO
OR H3.idUsuario = @IDUSUARIO
OR H4.idUsuario = @IDUSUARIO
OR H5.idUsuario = @IDUSUARIO
ORDER BY P.FECHAALTA DESC
 

saludos y muchas gracias de nuevo!
muchas dudas que he tenido, he visto que tu las aclaras en los diferentes temas que hay en el foro

[flaviovich]

Ese es el objetivo: ayudar y aprender.