Foros Club Delphi - mostrar SOLO cliente de los que tengo un sólo registro

Foros Club Delphi (https://www.clubdelphi.com/foros/index.php)

- SQL (https://www.clubdelphi.com/foros/forumdisplay.php?f=6)

- - mostrar SOLO cliente de los que tengo un sólo registro (https://www.clubdelphi.com/foros/showthread.php?t=11140)

mostrar SOLO cliente de los que tengo un sólo registro

Hola,

Estoy trabajando con delphi6 e interbase 7.

Tengo que motar un informe (report), y para ello me he creado o lo estoy intentando, un procedimiento que me filtra los cliente por una serie de datos de modo que al final, me muestra todos los registros correspondientes a cada uno de los clientes que cumplen mi condición.

Hasta aquí todo bien. El tema es que ahora lo que necesito hacer es que de ese listado que he obtenido, si un cliente tiene más de un registro, entonces NO me muestre los registros de ese alumno y si tiene SÓLO un registro me lo muestre en el listado.

Alguna idea?????

Por favor, ayuda,

Muchas gracias de antemano, y por todo

Virginia

Hola Virginia

No se si te he entendido (podrías poner el SQL que lanzas), pero quizás con un simple Distinct lo puedas solucionar

Código SQL [-]select distinct campos
from tablas
where condiciones

Espero te sirva

Hola Cadetill,

por desgracia, no es tan fácil, pues efectivamente utilizo el distint, pero los registros no son totalmente iguales, tienen campos algo distintos. Esto es lo que tengo dentro del bloque del procedimiento:

Código SQL [-]FOR
select distinct alumnos.NUMALU, (alumnos.NOMALU || ' ' || alumnos.APELALU) AS APENOM,
 alumnos.FNACALU, alumnos.TELFALU, (alumnos.viaalu || ' ' || alumnos.domialu) as CALLE,
  alumnos.codpalu, alumnos.pobalu, alumnos.sexalu,
  matri.fecfin,
  grupos.alias,
 (clientes.nomcl || ' ' ||  clientes.apelcl) as nomcli, clientes.sexcl

 FROM (((alumnos INNER JOIN matri ON alumnos.numalu=matri.NUMALU)
       inner JOIN grupos ON matri.numgrup=grupos.numgrup)
       inner join clientes on alumnos.nic=clientes.nic)


 WHERE ((matri.caubaja=1) AND (grupos.PRODUCTO>'21' AND grupos.producto<'34'))
 ORDER BY alumnos.numalu, matri.fecfin desc

into
:VNUMALU, :vnombre, :vfnaci,:VTELFALU,  :VCALLE, :VCPALU,
:VPOBLALU, :VSEXALU,  :VFECFINCURSO, :valias, :VNOMCLI, :VSEXCL
DO BEGIN                                            
  NUMALU= :VNUMALU;
  NOMBRE=  :vnombre;
  FNACI= :vfnaci;
  TELFALU=  :VTELFALU;
  CALLE= :VCALLE;
  CPALU= :VCPALU;
  POBLALU= :VPOBLALU;
  SEXALU= :VSEXALU;
  FECFINCURSO= :VFECFINCURSO;
  NOMCLI= :VNOMCLI;
  SEXCL= :VSEXCL;
  alias= :valias;

  suspend;
END /*fin del for*/

Muchas gracias,

Virginia

Hola.

Esto te va a listar los registros no repetidos por el campo nombre.

select nombre
from Tabla EXT
where (select count(*) from Tabla INT where INT.nombre = EXT.nombre) = 1

Si esto no es lo que quieres, tendrás que explicarte mejor.

Saludos.

Cita:

Empezado por guillotmarc

Esto te va a listar los registros no repetidos por el campo nombre.

select nombre
from Tabla EXT
where (select count(*) from Tabla INT where INT.nombre = EXT.nombre) = 1

Si esto no es lo que quieres, tendrás que explicarte mejor.

Saludos.

Efectivamente lo que necesito es que se me listen los registros NO REPETIDOS. Lo que me propones me funciona, pero el procedimiento se me hace eterno, de hecho, creía que se me había colgado la aplicación al ejecutarlo directamente desde IB, por lo que parece implanteable su ejecución por medio de Delphi, ¿alguna idea de que puedo estar haciendo mal para que sea tan lento?

Muchas gracias,

Virginia

Hola.

Los problemas de velocidad en una consulta, practicamente siempre se solucionan creando un/varios índice/s adecuado/s.

Cuando tienes una subconsulta (como este caso), tienes que prestar especial atención a crear un índice para optimizar esa subconsulta (puesto que se va a lanzar la subconsulta, una vez para cada registro de la consulta principal).

Así para una consulta tipo, la comentada :

select nombre
from Tabla EXT
where (select count(*) from Tabla INT where INT.nombre = EXT.nombre) = 1

Solo creando un índice para el campo nombre, vas a aumentar espectacularmente su tiempo de ejecución.

NOTA : Este caso es muy simple, en muchos casos vas a necesitar un índice múltiple para optimizar adecuadamente una consulta. Es decir para optimizar una consulta del tipo select .... where Campo1 = X and Campo2 = Y, debes crear un índice compuesto, con los campos Campo1 y Campo2.

Saludos.

Cita:

Empezado por Giniromero

Lo que me propones me funciona, pero el procedimiento se me hace eterno, de hecho, creía que se me había colgado la aplicación al ejecutarlo directamente desde IB, por lo que parece implanteable su ejecución por medio de Delphi, ¿alguna idea de que puedo estar haciendo mal para que sea tan lento?

Por cierto, Delphi no tiene nada que ver con que la consulta/procedimiento sea lenta o no. Como has comentado también va lento directamente en IB. Es un problema de diseño de la consulta/Base de Datos, y no de Delphi.

Saludos.

Hola,

Cita:

Empezado por guillotmarc

Por cierto, Delphi no tiene nada que ver con que la consulta/procedimiento sea lenta o no. Como has comentado también va lento directamente en IB. Es un problema de diseño de la consulta/Base de Datos, y no de Delphi.

Saludos.

Si, ya sé que el problema lo tengo directamente en la propia consulta, pues como te dije, me tarda horrores directamente desde IB. Lo que quería decir es eso de "y de lanzarla desde delphi, ... ni hablamos..." pues si el problema está el la BD... en Delphi no va a ir bien ni de bromita.

En cualquier caso, tendré que revisar los indices que tengo, por que esto mismo me pasó con otra consulta, (creo recordar que tu mismo me dijiste como solucionarlo, esto es, que tuviese especial cuidado con los indices), y esta vez yo creía que los había puesto bien. :o

Muchas gracias por la ayuda,

Virginia

Hola a todos,

He estado probando lo que me comentó guillotmarc. El caso es que parece que funcina cuando lo utilizo en un procedimiento que tira de otro procedimiento y de unas tablas, pero me va muy, muy lento.

He estado revisando los indices para las tablas que intervienen en cada uno de ellos, parece que tengo todos los campos que se usan en algún momento en el where dentro de un indice directamente en su tabla correspondiente. Pero me sigue llendo lento, muy, muy lento.

Arta de no conseguir nada, decidí que tal vez, el hecho de que esté tirando de otro procedimiento, podía ser la causa de la tardanza, (aunque este procedimiento no tarda en ejecutarse), así que decidí montar el procedimiento con todo el código directamente dentro de este procedimiento, para que no dependa de ningún otro.

En el otro procedimiento utilizo inner join para ir juntando las tablas, y al juntar el código de los procedures, (el nuevo y del que dependía antes), encuentro que no me filtra bien, aunque eso si, va mucho más rápido.

El código que me funciona, aunque lento es:

Código SQL [-]CREATE PROCEDURE RESERVASPTES1
RETURNS (
    NUMALU SMALLINT,
    NOMBRE VARCHAR(95),
    TELFALU VARCHAR(30),
    NOMCLI VARCHAR(95),
    ALIASNEW VARCHAR(25))
AS
DECLARE VARIABLE VNUMALU SMALLINT;
DECLARE VARIABLE VNOMBRE VARCHAR(95);
DECLARE VARIABLE VTELFALU VARCHAR(30);
DECLARE VARIABLE VNOMCLI VARCHAR(95);
DECLARE VARIABLE VFECFINCURSO TIMESTAMP;
DECLARE VARIABLE VALIASNEW VARCHAR(25);
begin
  /* Procedure Text */
FOR

select distinct
renopla2.NUMALU, renopla2.nombre, renopla2.TELFALU, renopla2.nomcli,
 renopla2.fecfincurso, grupos.alias

from renopla2 EXTE, grupos , comen


where (( select count (*) from renopla2 inte
         Where inte.numalu=exte.numalu)=1 )
and ((renopla2.numalu=comen.numalu) and (comen.tipo=97) and (comen.alias=grupos.alias))
ORDER BY   renopla2.NUMALU, renopla2.fecfincurso desc

into
:VNUMALU, :vnombre, :VTELFALU, :VNOMCLI, :VFECFINCURSO, :VALIASNEW
DO BEGIN
/*si el año de finalización de curso para el registro es igual que este año
es un registro de un alumno que no se ha matriculado aun*/
 If  ((extract(year from :VFECFINCURSO))=(extract(year from current_date))) Then Begin
  NUMALU= :VNUMALU;
  NOMBRE=  :vnombre;
  NOMCLI= :VNOMCLI;
  TELFALU=  :VTELFALU;
  ALIASNEW= :VALIASNEW;
    suspend;
 End /*fin del if*/
END /*fin del for*/
end

Donde el código del procedimiento renopla2 es el siguiente:

Código SQL [-]
CREATE PROCEDURE RENOPLA2
RETURNS (
    NUMALU SMALLINT,
    NOMBRE VARCHAR(95),
    FNACI TIMESTAMP,
    TELFALU VARCHAR(30),
    CALLE VARCHAR(48),
    CPALU VARCHAR(5),
    POBLALU VARCHAR(35),
    SEXALU VARCHAR(1),
    NOMCLI VARCHAR(95),
    SEXCL VARCHAR(1),
    ALIAS VARCHAR(25),
    FECFINCURSO TIMESTAMP)
AS
DECLARE VARIABLE VNUMALU SMALLINT;
DECLARE VARIABLE VNOMBRE VARCHAR(95);
DECLARE VARIABLE VFNACI TIMESTAMP;
DECLARE VARIABLE VTELFALU VARCHAR(30);
DECLARE VARIABLE VCALLE VARCHAR(48);
DECLARE VARIABLE VCPALU VARCHAR(5);
DECLARE VARIABLE VPOBLALU VARCHAR(35);
DECLARE VARIABLE VSEXALU VARCHAR(1);
DECLARE VARIABLE VNOMCLI VARCHAR(95);
DECLARE VARIABLE VSEXCL VARCHAR(1);
DECLARE VARIABLE VALIAS VARCHAR(25);
DECLARE VARIABLE VFECFINCURSO TIMESTAMP;
begin
  /* Procedure Text */
FOR
select distinct alumnos.NUMALU, (alumnos.NOMALU || ' ' || alumnos.APELALU) AS APENOM,
 alumnos.FNACALU, alumnos.TELFALU, (alumnos.viaalu || ' ' || alumnos.domialu) as CALLE,
  alumnos.codpalu, alumnos.pobalu, alumnos.sexalu,
  matri.fecfin,
  grupos.alias,
 (clientes.nomcl || ' ' ||  clientes.apelcl) as nomcli, clientes.sexcl

 FROM (((alumnos INNER JOIN matri ON alumnos.numalu=matri.NUMALU)
       inner JOIN grupos ON matri.numgrup=grupos.numgrup)
       inner join clientes on alumnos.nic=clientes.nic)

/* En este caso nos interesa también, ver los alumnos que se han matriculado
y pasan a adultos, procedente de un grupo de niños. Por eso incluimos el producto 40*/
 WHERE ((matri.caubaja=1) AND (grupos.PRODUCTO>'21' AND grupos.producto<='34'))
 ORDER BY alumnos.numalu, matri.fecfin desc

into
:VNUMALU, :vnombre, :vfnaci,:VTELFALU,  :VCALLE, :VCPALU,
:VPOBLALU, :VSEXALU,  :VFECFINCURSO, :valias, :VNOMCLI, :VSEXCL
DO BEGIN                                            
  NUMALU= :VNUMALU;
  NOMBRE=  :vnombre;
  FNACI= :vfnaci;
  TELFALU=  :VTELFALU;
  CALLE= :VCALLE;
  CPALU= :VCPALU;
  POBLALU= :VPOBLALU;
  SEXALU= :VSEXALU;
  FECFINCURSO= :VFECFINCURSO;
  NOMCLI= :VNOMCLI;
  SEXCL= :VSEXCL;
  alias= :valias;

  suspend;
END /*fin del for*/
end

El nuevo que estoy montando es:

Código SQL [-]CREATE PROCEDURE RESERVASPTES
RETURNS (
    NUMALU SMALLINT,
    NOMBRE VARCHAR(95),
    TELFALU VARCHAR(30),
    NOMCLI VARCHAR(95),
    ALIASOLD VARCHAR(25),
    ALIASNEW VARCHAR(25))
AS
DECLARE VARIABLE VNUMALU SMALLINT;
DECLARE VARIABLE VNOMBRE VARCHAR(95);
DECLARE VARIABLE VALIASOLD VARCHAR(25);
DECLARE VARIABLE VALIASNEW VARCHAR(25);
DECLARE VARIABLE VTELFALU VARCHAR(30);
DECLARE VARIABLE VNOMCLI VARCHAR(95);
DECLARE VARIABLE VFECFINCURSO TIMESTAMP;
BEGIN
  /* Procedure Text */
  FOR

    select distinct
    mainalu.NUMALU, (mainalu.nomalu  ||'  ' || mainalu.apelalu) as NOMBRE,
    mainalu.telfalu,
    matri.fecfin,
    grupos.alias,/*OLDALIAS*/
    (clientes.nomcl || '  ' || clientes.apelcl) as nomcli,
     P_GRUPO.alias /*NEWALIAS*/



     FROM  alumnos mainalu
                    INNER JOIN matri ON (mainalu.numalu=matri.NUMALU)
                    inner JOIN grupos  ON (matri.numgrup=grupos.numgrup)
                    inner join clientes on (mainalu.nic=clientes.nic)
                    inner join comen on (mainalu.numalu=comen.numalu), p_grupo





     WHERE  ((matri.caubaja=1) AND (grupos.PRODUCTO>'21' AND grupos.producto<'34'))
     AND   (comen.tipo=97) and (comen.alias=p_grupo.alias)

     AND    ((select COUNT (*) FROM alumnos inter
              WHERE  inter.numalu = mainalu.numalu)=1)

     ORDER BY   mainalu.NUMALU, matri.fecfin desc

    into
    :VNUMALU, :vnombre, :VTELFALU, :vfecfincurso, :valiasold, :vnomcli, :valiasnew

    DO BEGIN

      If  ((extract(year from :VFECFINCURSO))=(extract(year from current_date))) Then Begin
         NUMALU= :VNUMALU;
         NOMBRE=  :vnombre;
         NOMCLI= :VNOMCLI;
         TELFALU=  :VTELFALU;
         ALIASNEW= :VALIASnew;
         ALIASold= :VALIASold;
           suspend;
      End /* fin del if*/
  END /*fin del for*/
end

El cual, supuestamente tendría que ser la unión de los otros dos.

No me filtra por el select count, ¿que estoy haciendo mal?
¿hay forma de forzar indices a un procedimiento?

alguien me puede ayudar??

Muchas gracias

Virginia

Hola.

El hecho de unir los dos procedimientos en uno solo, te va a proporcionar un aumento mínimo del rendimiento.

Yo no lo haría, sobretodo cuando está claro que tienes un problema de falta de índices.(Saltarse el procedimiento almacenado, solo valdria la pena si quieres pasar de un rendimiento de, pongamos 1.5 segunos a 1.0 segundos, pero no es el caso).

Tienes que estudiar el plan de ejecución de cada una de las consultas involucradas. Te va a indicar el índice que se utiliza para cada tabla y unión involucrada en la consulta. Lo primero que tienes que buscar es tablas donde te muestre NATURAL, aquí te está indicando que esa tabla no se optimiza con ningún índice. (En caso de que todas las tablas utilizen un índice, entonces tendrás que buscar que índice es de poca ayuda en la consulta, y sustituirlo por un índice compuesto de varios campos).

Además puedes utilizar un depurador de procedimientos almacenados. Ejecutando línea a línea el procedimiento almacenado, vas a ver en que línea se pierde el rendimiento.

Utiliza SQL'92 y no SQL'89 (creo). Estás creando un producto cartesiano :

Código:

select distinct

renopla2.NUMALU, renopla2.nombre, renopla2.TELFALU, renopla2.nomcli,

 renopla2.fecfincurso, grupos.alias



from renopla2 EXTE, grupos , comen





where (( select count (*) from renopla2 inte

         Where inte.numalu=exte.numalu)=1 )

and ((renopla2.numalu=comen.numalu) and (comen.tipo=97) and (comen.alias=grupos.alias))

El Optimizador va a tener dificultades en optimizar esto. Con los INNER JOIN y LEFT OUTER JOIN del SQL'92 te queda un código mucho más legible, y ayudas al optimizador.

Saludos.

Hola,

Cita:

Empezado por guillotmarc

El hecho de unir los dos procedimientos en uno solo, te va a proporcionar un aumento mínimo del rendimiento.

Yo no lo haría, sobretodo cuando está claro que tienes un problema de falta de índices.(Saltarse el procedimiento almacenado, solo valdria la pena si quieres pasar de un rendimiento de, pongamos 1.5 segunos a 1.0 segundos, pero no es el caso).

okis

Cita:

Empezado por guillotmarc

Tienes que estudiar el plan de ejecución de cada una de las consultas involucradas.

Bien, pero, ¿como hago esto?, ¿hay alguna herramienta que pueda utilizar?

Cita:

Empezado por guillotmarc

Utiliza SQL'92 y no SQL'89 (creo).

Perdón por mi torpeza pero, ¿como sé cual estoy usando, y en tal caso, como lo cambio?, ¿que implica tener uno u otro?

Cita:

Empezado por guillotmarc

Estás creando un producto cartesiano :

Código:

select distinct

renopla2.NUMALU, renopla2.nombre, renopla2.TELFALU, renopla2.nomcli,

 renopla2.fecfincurso, grupos.alias



from renopla2 EXTE, grupos





where (( select count (*) from renopla2 inte

         Where inte.numalu=exte.numalu)=1 )

and (renopla2.aliascomen=grupos.alias)

ORDER BY   renopla2.NUMALU, renopla2.fecfincurso desc

El Optimizador va a tener dificultades en optimizar esto. Con los INNER JOIN y LEFT OUTER JOIN del SQL'92 te queda un código mucho más legible, y ayudas al optimizador.

Saludos.

¿donde estoy creando un producto cartesiano?
Perdona pero, ¿me lo puedes aclarar algo más?

Muchas gracias por todo,

Virginia

Cita:

Empezado por Giniromero

Bien, pero, ¿como hago esto?, ¿hay alguna herramienta que pueda utilizar?

Con el IBExpert mismo puedes controlarlo (paso a paso) www.ibexpert.com
La versión personal es gratuita

Cita:

Empezado por Giniromero

Perdón por mi torpeza pero, ¿como sé cual estoy usando, y en tal caso, como lo cambio?, ¿que implica tener uno u otro?

no es una "version" de SQL "instalada" en tu PC, sino cómo usas el SQL, la definición de los estandares (soportados o no por los motores de bases de datos). El estándar del 92, la mayoría de SGBD lo soportan. En él, se definieron entro otras cosas, el uso de los JOINS (inner, left, outer)

Cita:

Empezado por Giniromero

¿donde estoy creando un producto cartesiano?

Aquí:

Código SQL [-]from renopla2 EXTE, grupos

Si no usas joins, lo que estás creando es un producto cartesiano. En este caso entre renopla2 y grupos.

Hola cadetill,

Cita:

Empezado por cadetill

Con el IBExpert mismo puedes controlarlo (paso a paso) www.ibexpert.com
La versión personal es gratuita

Pero con la versión gratuita, ¿se puede estudiar el plan de ejecución de cada una de las consultas involucradas? ¿como? ¿donde?

Cita:

Empezado por cadetill

no es una "version" de SQL "instalada" en tu PC, sino cómo usas el SQL, la definición de los estandares (soportados o no por los motores de bases de datos).

vale

Cita:

Empezado por cadetill

El estándar del 92, la mayoría de SGBD lo soportan. En él, se definieron entro otras cosas, el uso de los JOINS (inner, left, outer)

Aquí:

Código SQL [-]from renopla2 EXTE, grupos

Si no usas joins, lo que estás creando es un producto cartesiano. En este caso entre renopla2 y grupos.

¿por no usar un inner join?, vale, y si eso implica que creo un producto cartesiano, y eso implica que va más lento, ¿como hago para, usando el inner join, poder usar el código:

Código SQL [-]where (( select count (*) from renopla2 inte
         Where inte.numalu=exte.numalu)=1 )

Muchas gracias,

Virginia

Hola.

Cita:

Empezado por Giniromero

Pero con la versión gratuita, ¿se puede estudiar el plan de ejecución de cada una de las consultas involucradas? ¿como? ¿donde?

Cuando lanzas una consulta cualquiera en el SQL Editor de las IB-Expert, en la ventana inferior te muestra el plan de ejecución estimado y utilizado (asi como el tiempo de ejecución, etc. ...)

El plan de ejecución consta de la lista de índices que se utilizan para optimizar la consulta.

Para más información sobre los planes de ejecución puedes utilizar la herramienta IbPLANAnalyzer http://delphi.weblogs.com/IBPLANalyzer

Cita:

Empezado por Giniromero

¿por no usar un inner join?, vale, y si eso implica que creo un producto cartesiano, y eso implica que va más lento, ¿como hago para, usando el inner join, poder usar el código:

Se crea un producto cartesiano al lanzar una consulta de este tipo (sintaxis slq'89) :

select XXX from Tabla1, Tabla2

Deberias especificar el tipo de unión de las tablas mediante un Inner Join o un Left Outer Join.

select XXX
from Tabla1
inner join Tabla2 on Tabla1.Campo1 = Tabla2.Campo2

(consulta la documentación de Interbase para mas detalles del inner y outer join)

Cita:

Empezado por Giniromero

Código SQL [-]where (( select count (*) from renopla2 inte
Where inte.numalu=exte.numalu)=1 )

Este condición no tiene nada que ver con la sintaxis de uniones de tablas de sql'89 i sql'92. Es perfectamente válida independientemente de como se hayan indicado las uniones de las tablas involucradas.

Saludos.

Respecto a los planes de ejecución. En IB-Expert tienes una pestaña al cargar un procedimiento almacenado, llamada 'Plan Analyzer', en ella se muestran en forma de árbol los planes de las consultas del procedimiento.

NOTA: Aunque me parece que esto no está disponible en la versión gratuita.

Saludos.

Hola,

Vale, me he bajado el trial de la versión completa del IbExpert, y el IbPLANAnalyzer.

He descubierto, que hay algo que no necesitaba, y que me cargaba mucho el procedimiento.

En cualquier caso, ahora no me funciona correctamente el código de :

Código:

where (( select count (*) from alumnos inte

         Where inte.numalu=exte.numalu)=1 )

El código que tengo ahora mismo es:

Código:

select distinct exte.NUMALU, (exte.NOMALU || ' ' || exte.APELALU) AS NOMBRE,

 exte.TELFALU,

  matri.fecfin,

  grupos.alias,

 (clientes.nomcl || ' ' ||  clientes.apelcl) as nomcli,

 comen.alias



 FROM ((((alumnos exte INNER JOIN matri ON exte.numalu=matri.NUMALU)

       inner JOIN grupos ON matri.numgrup=grupos.numgrup)

       inner join clientes on exte.nic=clientes.nic)

       inner join comen on exte.numalu=comen.numalu)



/* Donde tengan matrícula activas y el producto asociado a esta, esto es el curso

esté entre el 21 y el 40, que son los grupos de los niños y jovenes*/



 where (( select count (*) from alumnos inte

         Where inte.numalu=exte.numalu)=1 )



 and ((matri.caubaja=1) and (grupos.PRODUCTO>'21' and grupos.producto<='40'))

 /* Que ademas tengan comentario de recomendacion curso siguiente y sea de este año*/

 AND  ((comen.tipo=97) and (extract (year from comen.fecha))=(extract (year from current_date)))



 ORDER BY exte.numalu

Tiene que haber algo mal que hace que la parte del:

Código:

where (( select count (*) from alumnos inte

         Where inte.numalu=exte.numalu)=1 )

No funcine correctamente. ¿alguna Idea?

Muchas gracias por vuestra paciencia,

Virginia