Tarda mucho en ejecutar un SP

Choclito · #1 27-07-2010

Buenas amigos del foro tengo el siguiente problema: tengo dos procedimientos almacenados el primero es el siguiente:

Código SQL [-]  SET TERM ^ ;

CREATE OR ALTER PROCEDURE LISTADO_REGISTRO_SERVICIOS_AMB (
    f1 date,
    f2 date,
    valor char(1))
returns (
    fecha date,
    nro_recibo integer,
    tipo_servicio varchar(30),
    servicio varchar(70),
    costo numeric(18,2),
    costo_total numeric(18,2),
    literal varchar(300),
    nro_ambulatorio integer,
    usuario varchar(20),
    nombre_amb varchar(70),
    edad integer,
    sexo char(1),
    tipo_pac varchar(20),
    factura varchar(10),
    hora time,
    monto_cobrar numeric(18,2),
    porcentaje integer,
    nro_registro integer,
    nro_factura integer)
as
declare variable nro_asegurado integer;
declare variable nro_internado integer;
begin
/*para el caso de consulta medica */
for
select  r.nro_recibo,cast(r.fecha as date),d.ap_paterno||' '||d.ap_materno||' '||d.nombres||'-->'||e.nombre_e, case r.porcentaje_cobro when 100 then  c.costo else (r.porcentaje_cobro*c.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,c.nro,r.nro_factura
from  (recibo r inner join registro_consulta_med c on r.nro_recibo=c.nro_recibo),c_externa c1,especialidad e,doctor d
where ((c.nro_consulta=c1.nro)and(c1.id_doctor=d.ci)and(d.esp=e.nro)and(cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
  tipo_servicio='CONSULTA EXTERNA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
suspend;
end
/*para emergencias*/
for
select  r.nro_recibo,cast(r.fecha as date),e.nombre, case r.porcentaje_cobro when 100 then  g.costo else (r.porcentaje_cobro*g.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,g.nro,r.nro_factura
from  recibo r,registro_emergencias g,emergencias e
where ((R.nro_recibo=G.nro_recibo)AND(g.nro_emergencias=e.nro)and(cast(g.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado, :factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='EMERGENCIAS';
suspend;
end
/*PARA ECOGRAFIA*/
for
select  r.nro_recibo,cast(r.fecha as date),g.nombre, case r.porcentaje_cobro when 100 then  r1.costo else (r.porcentaje_cobro*r1.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,r1.nro,r.nro_factura
from  recibo r,registro__ecografia R1,ecografia g
where ((r1.nro_recibo=r.nro_recibo)and(r1.tipo_ecografia=g.nro)and(cast(r.fecha as date) between :f1 and :f2)and(r.cancelado=:valor)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='ECOGRAFIA';
suspend;
end
/*PARA LABORATORIO*/
for
select  r.nro_recibo,cast(r.fecha as date),a.nombre_a, case r.porcentaje_cobro when 100 then  e.costo else (r.porcentaje_cobro*e.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,e.nro,r.nro_factura
from  recibo r,registro_examenes_lab E,analisis_clinico A
where ((E.nro_recibo=r.nro_recibo)and(e.examen=a.nombre_a)and(cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='LABORATORIO';
suspend;
end
/*PARA RAYOS X*/
for
select  r.nro_recibo,cast(r.fecha as date),g.nombre, case r.porcentaje_cobro when 100 then  x.costo else (r.porcentaje_cobro*x.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,x.nro,r.nro_factura
from  recibo r,registro_radiologia X,estudio_radiografia G
where ((x.nro_recibo=r.nro_recibo)and(g.nro_estudio=x.nro_estudio)and(cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='RAYOS X';
suspend;
end
/*para farmacia*/
for
select  r.nro_recibo,cast(r.fecha as date),v.cantidad||'==>'||m.nombre_producto||'==>'||p.nombre, case r.porcentaje_cobro when 100 then  v.costo else (r.porcentaje_cobro*v.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,v.nro,r.nro_factura
from  recibo r,venta_med v,medicamento m,presentacion_med p
where ((v.nro_recibo=r.nro_recibo)and(m.id_producto=v.id_medicamento)and(m.id_presentacion=p.id_presentaci  on)and(cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
  if (valor='F') then
  begin
  select distinct v.usuario
  from venta_med v
  where (v.nro_recibo=:nro_recibo)
  into :usuario;
  end
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='FARMACIA';
suspend;
end
/*para otros servicios*/
for
select  r.nro_recibo,cast(r.fecha as date),o.nombre, case r.porcentaje_cobro when 100 then  s.costo else (r.porcentaje_cobro*s.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,s.nro,r.nro_factura
from recibo r,registro_otros_servicios s,otros_servicios o
where  ((s.nro_recibo=r.nro_recibo)and(s.nro_servicio=o.numero)and(cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))
into :nro_recibo,:fecha,:servicio,:costo,:costo_total,:literal,:nro_ambulatorio,:usuario,:tipo_pac,:nro_a  segurado,:nro_internado,:factura,:hora,:monto_cobrar,orcentaje,:nro_registro,:nro_factura
DO
begin
   if (factura='F') then
   factura='RECIBO';
   ELSE
   FACTURA='FACTURA';
if (tipo_pac='AMBULATORIO') then
BEGIN
  select X.ap_paterno||'  '||coalesce(X.ap_materno,'')||'  '||X.nombreS,X.edad,X.sexo
  from paciente_externo  X
  where (x.nro=:nro_ambulatorio)
  into :nombre_amb,:edad,:sexo;

  end
  if (tipo_pac='ASEGURADO') then
  BEGIN
 select R.ap_paterno||'  '||coalesce(R.ap_materno,'')||'  '||r.nombres,R.edad,R.sexo
  from paciente_asegurado  R,instituciones i
  where ((r.nro_registro=:nro_asegurado)and(R.habilitado='T')and(r.institucion=i.nro))
  into :nombre_amb,:edad,:sexo;
 
  END
  if (TIPO_PAC='INTERNADO') then
  BEGIN
 select  p.ap_paterno||'  '||coalesce(p.ap_materno,'')||'  '||p.nombre,p.edad,p.sexo
  from paciente p,registro_internacion n
  where ((n.nro=:nro_internado)and(n.nro_historia=p.nro))
  into :nombre_amb,:edad,:sexo;
  END
  tipo_servicio='OTROS SERVICIOS';
suspend;
end

end
^

SET TERM ; ^

este procedimiento hace un listado con todos los ingresos que se tiene en la seccion caja por diferentes servicios
luego tengo otro procedimiento que genera los totales por cada servicio en funcion a las fechas y que llama al procedimiento anterior, el SP es el siguiente:

Código SQL [-]begin
for
/*select distinct m.tipo_servicio
from listado_registro_servicios_amb (:f1,:f2,'T') m
into :tipo_servicio*/
  select distinct (l.tipo)
   from listado_servicios l
   where tipo<>'INTERNACION'
   into :tipo_servicio
do
begin
  for
  select distinct m.usuario
  from  listado_registro_servicios_amb (:f1, :f2, 'T') m
  where  (m.tipo_servicio=:tipo_servicio)
  into :usuario
  do
  begin
 select sum(m.costo)  /*  select  coalesce(sum(case when m.costo is null then 0 else m.costo end),0)  as monto_factura*/
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='FACTURA')and(m.tipo_servicio=:tipo_servicio))
into :monto_factura;
select  coalesce(sum(case when m.costo is null then 0 else m.costo end),0) as monto_recibo
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='RECIBO')and(m.tipo_servicio=:tipo_servicio))
into :monto_recibo;

select m.tipo_servicio,sum(m.costo)
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((m.tipo_servicio=:tipo_servicio)AND(M.usuario=:USUARIO))
group by m.tipo_servicio
into :tipo_servicio,:total;

  suspend;
  end
  end
end

pero cuando ejecuto el segundo procedimiento con rango de fechas de 5 dias me tarda como 2,5 minutos.
He visto en el foro el tratamiento de indices, comentarles que las llaves primarias de las tablas tienen ya creado sus indices y aun eso tarda.
Por lo que e visto en el foro creo q esta parte seria la causante:

Código SQL [-]select  coalesce(sum(case when m.costo is null then 0 else m.costo end),0) as monto_recibo
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='RECIBO')and(m.tipo_servicio=:tipo_servicio))
into :monto_recibo;

Les pido porfavor sugerencias pues mi cliente me tiene ya loco porq tarda mucho en mostrar los datos el reporte asociado al SP
Trabajo con delphi y firebird 2.1
Agradesco cualquier sugerencia
COnsulta todos los campos a consultas en la sentencia where tienen que tener su indice creado????
Estare agradecido por la colaboracion de los amigos del foro

Choclito · #2 27-07-2010

la tabla recibo tiene 12000 registros aproximadamente y el resto de las tablas una cantidad menor a esa.
y si el rango de fechas es mas grande tarda mucho mas en ejecutar el segundo procedimiento, espero que se haya entendido el problema q tengo y gracias por las sugerencias o la ayuda que me brinden.

guillotmarc · #3 27-07-2010

Hola,

En este segundo procedimiento almacenado no tienes demasiadas consultas. Ejecútalas manualmente, y verás cual de ellas es la que se demora tanto.

Es decir en IBExpert (o el gestor que utilizes) lanza directamente :

select distinct (l.tipo)
from listado_servicios l
where tipo<>'INTERNACION'

Si se ejecuta rápido, te olvidas de ello y pasas a la siguiente consulta :

select distinct m.usuario
from listado_registro_servicios_amb (:f1, :f2, 'T') m
where (m.tipo_servicio=:tipo_servicio)

Hasta dar con la consulta que va lenta.

Supongamos que efectivamente resulta ser :

select coalesce(sum(case when m.costo is null then 0 else m.costo end),0) as monto_recibo
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='RECIBO')and(m.tipo_servicio=:tipo_servicio))

Entonces tienes que optimizar la consulta, y para hacerlo se utilizan, como bien has dicho, índices.

Pero crear los índices no es simplemente hacer un índice para cada campo implicado. No, puesto que eso no le sirve al sistema para agilizar una consulta como la anterior.

En ese caso necesitas un índice múltiple, es decir un solo índice con los campos : usuario, factura, tipo_servicio

Si la consulta que te demora la ejecución del procedimiento almacenado resulta ser otra, simplemente dinos cual es y te ayudaremos a definir el índice adecuado para optimizarla.

Saludos.

guillotmarc · #4 27-07-2010

Mejor cambia el índice múltiple que te he dicho por un índice sobre : usuario, tipo_servicio, factura (el orden es importante), y ahora ya podrá optimizar las tres consultas que tienes sobre listado_registro_servicios_amb

En cambio el índice que te había propuesto inicialmente : usuario, factura, tipo_servicio no podía ser usado para optimizar la consulta :

select m.tipo_servicio,sum(m.costo)
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((m.tipo_servicio=:tipo_servicio)AND(M.usuario=:USUARIO))
group by m.tipo_servicio

Saludos.

Kipow · #5 28-07-2010

Utiliza el IB PlanAlizer ese utilizo yo para mejorar mis consultas.

[fjcg02] · #6 28-07-2010

Hola,
he estado mirando por encima la consulta, y hay algo que me 'cruje'.

Código SQL [-]from  
    (recibo r inner join registro_consulta_med c on r.nro_recibo=c.nro_recibo),
    c_externa c1,  // ** AQUI ME CRUJE **//
    especialidad e, // ** AQUI ME CRUJE **//
    doctor d // ** AQUI ME CRUJE **//
where 
    (
      (c.nro_consulta=c1.nro)and(c1.id_doctor=d.ci)and(d.esp=e.nro)and(cast(r.fecha as date) between :f1 and :f2)
      and
      ((r.cancelado=:valor)or(r.cancelado='M'))

Estás utilizando tablas en la consulta que no están incluidas en ningún inner join, por lo que te debe ( en teoría según lo que tengo entendido ) devolverte más filas de las que necesitas. Ahora no me sale el nombre de esa forma de hacer una select
SELECT * form TABLA1, TABLA2 -> devuelve el nº de registros de TABLA1 multiplicado por el nº de registros de TABLA2, que obviamente PUEDE que no sea el resultado que necesites.
Lo normal es hacer
SELECT * FROM TABLA1 T1
INNER JOIN TABLA2 T2 ON ( T1.ID=T2.ID)
WHERE condiciones
que devolverá los registros que existan en las dos tablas y cumplan las condiciones

Espero haberte ayudado.

Saludos

[fjcg02] · #7 28-07-2010

Ya me acuerdo, el nombre de este tipo de select es producto cartesiano, que devuelve por cada registro de una tabla todos los registros de la otra.

Creo que puede ser lo que ralentiza la consulta. Haces el producto cartesiano, te devuelve tropecientos mil registros, pero al aplicar las condiciones de la where te devuelve el resultado esperado. Pero a costa de 'machacar' al servidor.

Prueba poniendo INNER JOIN en todas las consultas ( que tienes unas cuantitas ) en todas las tablas que no lo tienen. Seguramente el rendimiento mejorará.

Como dice guillotmarc, hazlo por separado para comparar resultados de cada select, primero tal y como la tienes y luego la versión modificada con INNER JOIN.

Cuentanos los resultados... y cuando consigas que tarde un par de segundos, ya verás como los usuarios te dirán que sigue tardando mucho

( te lo digo por porpia experiencia ).

Un saludo

guillotmarc · #8 28-07-2010

No me había fijado que LISTADO_REGISTRO_SERVICIOS_AMB es el primer procedimiento almacenado y no una tabla.

Esta claro que el problema lo tienes en ese procedimiento almacenado.

Pues ya sabes lo que tienes que hacer, ejecutar por separado cada consulta de ese procedimiento almacenado para identificar las que van realmente lentas, y después te ayudaremos a crear los índices a optimizarlas (necesitas índices múltiples).

Como dice Kipow, puedes usar programas como el IB PlanAlize para ejecutar cada una de las consultas implicadas en el procedimiento almacenado y detectar los cambios a hacer en los índices.

Choclito · #9 28-07-2010

mil disculpas no pude entrar antes, pero muchas gracias por las respuestas
,disculpas pues haciendo pruebas mande el codigo del primer SP con inner join y where.
Respecto al programa que me sugieren lo estoy buscando en la red para bajarlo y probarlo
en lo que respecta a la sugerencia del primer compañero hice las pruebas por separado y la ejecucion es casi inmediata de los select del 2do SP.
Trabajo con el ibexpert y quite todo lo que esta dentro del begin suspend y tarda como 30 seg
el SP 2 es este:

Código SQL [-]CREATE OR ALTER PROCEDURE CONSOLIDADO (
    f1 date,
    f2 date)
returns (
    tipo_servicio varchar(30),
    monto_factura numeric(15,2),
    monto_recibo numeric(15,2),
    total numeric(15,2),
    usuario varchar(20))
as
begin
for
/*select distinct m.tipo_servicio
from listado_registro_servicios_amb (:f1,:f2,'T') m
into :tipo_servicio*/
  select distinct (l.tipo)
   from listado_servicios l
   where tipo<>'INTERNACION'
   into :tipo_servicio
do
begin
  for
  select distinct m.usuario
  from  listado_registro_servicios_amb (:f1, :f2, 'T') m
  where  (m.tipo_servicio=:tipo_servicio)
  into :usuario
  do
  begin
 select sum(m.costo)  /*  select  coalesce(sum(case when m.costo is null then 0 else m.costo end),0)  as monto_factura*/
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='FACTURA')and(m.tipo_servicio=:tipo_servicio))
into :monto_factura;
select  coalesce(sum(case when m.costo is null then 0 else m.costo end),0) as monto_recibo
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((M.usuario=:USUARIO)AND(m.factura='RECIBO')and(m.tipo_servicio=:tipo_servicio))
into :monto_recibo;

select m.tipo_servicio,sum(m.costo)
from listado_registro_servicios_amb (:f1,:f2,'T') m
where ((m.tipo_servicio=:tipo_servicio)AND(M.usuario=:USUARIO))
group by m.tipo_servicio
into :tipo_servicio,:total;

  suspend;
  end
  end
end

y cuando lo quito la parte central queda asi : (ahi me tarda promedio 20 seg)

Código SQL [-]CREATE OR ALTER PROCEDURE CONSOLIDADO (
    f1 date,
    f2 date)
returns (
    tipo_servicio varchar(30),
    monto_factura numeric(15,2),
    monto_recibo numeric(15,2),
    total numeric(15,2),
    usuario varchar(20))
as
begin
for
/*select distinct m.tipo_servicio
from listado_registro_servicios_amb (:f1,:f2,'T') m
into :tipo_servicio*/
  select distinct (l.tipo)
   from listado_servicios l
   where tipo<>'INTERNACION'
   into :tipo_servicio
do
begin
  for
  select distinct m.usuario
  from  listado_registro_servicios_amb (:f1, :f2, 'T') m
  where  (m.tipo_servicio=:tipo_servicio)
  into :usuario
  do
  begin
  suspend;
  end
  end
end

Y respecto a indices multiples como me suguieren como tendria que realizarlo??? en la tabla donde estan estos campos?? porfavor quisiera un poco de ayuda sobre los indices multiples
Mil gracias

Choclito · #10 29-07-2010

Muchas gracias a todos los amigos del foro con sus criterios, tengo varias consultas:
1ro.- Pedirles porfavor la direccion de donde pueda bajar el programa ib plananalyzer pues busce en la web y no encontre ningun sitio.
2do.- Me baje un programa Interbase&Firebird Development Studio con el cual como uno de los amigos del foro me dijo probar los select del primer SP por separado para ver los resultados y probe el siguiente select:

Código SQL [-]select  r.nro_recibo,cast(r.fecha as date),g.nombre, case r.porcentaje_cobro when 100 then  r1.costo else (r.porcentaje_cobro*r1.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,r1.nro,r.nro_factura
from  recibo r,registro__ecografia R1,ecografia g
where ((r1.nro_recibo=r.nro_recibo)and(r1.tipo_ecografia=g.nro)and(cast(r.fecha as date) between :f1 and :f2)and(r.cancelado=:valor)and((r.cancelado=:valor)or(r.cancelado='M')))

y me muestra lo siguiente:
PLAN JOIN (R1 NATURAL, G INDEX (PK_ECOGRAFIA), R INDEX (PK_RECIBO))
aparte en el selec trabajo con las tablas recibo, recibo_ecografia y ecografia
y me muestra una grafica en barras donde me muestra que la tabla registro_ecografia no esta indexada, ademas me muestra que el tiempo que tarda es de 15 ms
Tengo indices creados tanto por las llaves primarias y foraneas de las tablas

Espero me puedan ayudar porfavor o darme sugerencias de como solucionar este problema mil gracias

guillotmarc · #11 30-07-2010

Cita:

Empezado por Choclito

Muchas gracias a todos los amigos del foro con sus criterios, tengo varias consultas:
1ro.- Pedirles porfavor la direccion de donde pueda bajar el programa ib plananalyzer pues busce en la web y no encontre ningun sitio.
2do.- Me baje un programa Interbase&Firebird Development Studio con el cual como uno de los amigos del foro me dijo probar los select del primer SP por separado para ver los resultados y probe el siguiente select:
Código SQL [-]select r.nro_recibo,cast(r.fecha as date),g.nombre, case r.porcentaje_cobro when 100 then r1.costo else (r.porcentaje_cobro*r1.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,r1.nro,r.nro_factura from recibo r,registro__ecografia R1,ecografia g where ((r1.nro_recibo=r.nro_recibo)and(r1.tipo_ecografia=g.nro)and(cast(r.fecha as date) between :f1 and :f2)and(r.cancelado=:valor)and((r.cancelado=:valor)or(r.cancelado='M')))

y me muestra lo siguiente:
PLAN JOIN (R1 NATURAL, G INDEX (PK_ECOGRAFIA), R INDEX (PK_RECIBO))
aparte en el selec trabajo con las tablas recibo, recibo_ecografia y ecografia
y me muestra una grafica en barras donde me muestra que la tabla registro_ecografia no esta indexada, ademas me muestra que el tiempo que tarda es de 15 ms
Tengo indices creados tanto por las llaves primarias y foraneas de las tablas

Espero me puedan ayudar porfavor o darme sugerencias de como solucionar este problema mil gracias

Hola.

Mi primera recomendación es que hagas explícitas las uniones, queda todo mucho más claro, fácil de entender y modificar, y mucho más fácil de optimizar, tanto para el motor SQL como para que tu veas los índices necesarios.

Esa misma consulta queda en :

Código SQL [-]select recibo.nro_recibo,cast(recibo.fecha as date) as fecha, cast(recibo.fecha as time) as hora,
       ecografia.nombre as ecografia,
       case recibo.porcentaje_cobro when 100 then registro__ecografia.costo
                                             else (recibo.porcentaje_cobro * registro__ecografia.costo) / 100 end as costo,
       recibo.costo_total, recibo.literal, recibo.nro_ambulatorio, recibo.usuario, recibo.tipo_paciente, recibo.nro_asegurado,
       recibo.nro_internacion, recibo.factura, recibo.monto_cobrar, recibo.porcentaje_cobro, recibo.nro_factura,
       registro__ecografia.nro
from recibo
     inner join registro__ecografia on registro__ecografia.nro_recibo = recibo.nro_recibo
     inner join ecografia on ecografia.nro = registro__ecografiaa.tipo_ecografia
where cast(recibo.fecha as date) between :desde and :hasta and
      recibo.cancelado = :valor

Es fácil entender que la tabla registro__ecografia necesita un índice para el campo nro_recibo, la tabla ecografia necesita un índice en el campo nro, y la tabla recibo necesita un índice múltiple sobre los campos cancelado, fecha (en ese orden).

NOTA : Fíjate que he acortado la condición :
and(r.cancelado=:valor)and((r.cancelado=:valor)or(r.cancelado='M'))

dejándola en :

and recibo.cancelado = :valor

Puesto que : A ^ (A v B) es igual a A

Saludos.

guillotmarc · #12 30-07-2010

Como haces una conversión sobre el campo FECHA, quizás el optimizador no podrá usar el índice correspondiente, en ese caso tendrás que realizar la consulta sin hacer la conversión. Es decir ese filtro es equivalente a :

Código SQL [-]where recibo.fecha between :desde and :hasta + 1 and
          recibo.fecha <> :hasta + 1 and
          recibo.cancelado = :valor

Que es exactamente lo mismo que :

Código SQL [-]where recibo.fecha >= :desde and recibo.fecha < :hasta + 1 and
          recibo.cancelado = :valor

Aunque este último filtro es más difícil de optimizar para Firebird, puesto que puede necesitar que el índice sobre FECHA sea bidireccional, cosa que no es así en todas las versiones de Firebird.

Todo es cuestión de probar, hasta dar con los índices y la consulta que mejor puede optimizar el motor.

Aunque la consulta por la que te recomiendo empezar es :

Código SQL [-]select recibo.nro_recibo,cast(recibo.fecha as date) as fecha, cast(recibo.fecha as time) as hora,
       ecografia.nombre as ecografia,
       case recibo.porcentaje_cobro when 100 then registro__ecografia.costo
                                             else (recibo.porcentaje_cobro * registro__ecografia.costo) / 100 end as costo,
       recibo.costo_total, recibo.literal, recibo.nro_ambulatorio, recibo.usuario, recibo.tipo_paciente, recibo.nro_asegurado,
       recibo.nro_internacion, recibo.factura, recibo.monto_cobrar, recibo.porcentaje_cobro, recibo.nro_factura,
       registro__ecografia.nro
from recibo
     inner join registro__ecografia on registro__ecografia.nro_recibo = recibo.nro_recibo
     inner join ecografia on ecografia.nro = registro__ecografiaa.tipo_ecografia
where recibo.fecha between :desde and :hasta + 1 and
      recibo.fecha <> :hasta + 1 and
      recibo.cancelado = :valor

Saludos.

guillotmarc · #13 30-07-2010

El IB PLANnalyzer lo puedes encontrar aquí :

http://www.ibphoenix.com/main.nfs?a=...ibp_misc_tools

Pero el enlace de descarga está muerto. Lo siento, yo no lo tengo y no conozco ninguna descarga alternativa, no lo uso, el IB-Expert ya me da toda la información que necesito para optimizar mis consultas.

Saludos.

Choclito · #14 03-08-2010

Muchas gracias guillotmar por responder realizare las pruebas que me suguieres y luego comento como me fue y disculpas por no responder mas antes.

guillotmarc · #15 03-08-2010

No olvides crear los índices que te comenté en el primer mensaje, es decir :

Cita:

Es fácil entender que la tabla registro__ecografia necesita un índice para el campo nro_recibo, la tabla ecografia necesita un índice en el campo nro, y la tabla recibo necesita un índice múltiple sobre los campos cancelado, fecha (en ese orden).

Choclito · #16 03-08-2010

Amigo guillotmarc te comento que logre bajar el ib planalyzer y en la grafica me muestra que la tabla registro_consulta_medica no esta indexado
la estructura de mis tablas es la siguiente
tabla recibo (
nro_recibo llave primaria,
fecha,
observaciones.
etc)
tabla registro_consulta_medica(
nro llave primaria
nro_recibo llave foranea y llave primaria,
nro_consulta llave foranea y llave primaria,
costo,
observaciones)
tabla consulta_medica(
nro llave primaria,
id_doctor,
costo1,
costo2)

Tengo la tabla recibo y la tabla consulta_medica y como existe una relacion de n a n es que la relacion de ellas es la tabla registro_consulta_medica con tres campos como llave principal.
Creo q ahi esta el error .
Esta sitacion tengo tanto con las tablas emergencias, venta_medicamentos, etc donde la relacion de las tablas tiene 3 campos como llaves primarias
Al crear las llaves primarias y foraneas Ib expert ya me crea los indices por los campos
Porfavor quisiera sugerencias de como arreglar pues la tabla recibo ya tiene mas de 16000 registros
Estare muy agradecido por la ayuda q me puedan brindar
y comentarte que ya cree los indices multiples de cancelado y costo

guillotmarc · #17 03-08-2010

Hola.

Nos tienes que decir para que consulta te indica que no tiene índices sobre registro_consulta_medica. Lo que te está indicando es que ninguno de los índices existentes le sirve para acelerar esa consulta en concreto.

Eso cambia entre consulta y consulta. Algunas consultas se pueden acelerar usando unos índices, otras se pueden acelerar con otros índices, y para algunas consultas puede que no tengas definido aún ningún índice útil (como es tu caso).

Los índices sobre los campos de clave foránea básicamente se utilizan para los JOINS, para poder optimizar la uníon ON clave_foranea = clave_primaria. Pero si tu tabla no está entre las relacionadas, entonces esos índices son de poca utilidad.

En tu caso concreto, lo más probable es que la tabla registro_consulta_medica solo esté filtrada en el WHERE de la consulta (y no en los JOINS), por tanto no te sirve ni el índice de la clave primaria, ni los índices de las claves foráneas.

Tienes que mirar la condición del WHERE, y en función de los campos que estés filtrando, tienes que crear índices sobre esos campos. Como solo se va a utilizar un índice, si tu filtro implica varios campos, entonces es cuando creamos un índice compuesto para que la base de datos pueda encontrar instantáneamente, usando el índice compuesto, los registros que coindicen con los campos buscados (y que se encuentran definidos en el índice).

Si nos dices la consulta en que te marca que no hay un índice adecuado para esa tabla, te podremos ayudar más.

Saludos.

Choclito · #18 04-08-2010

antes gracias por responder amigo guillotmarc, la aplicacion q tengo es para un hospital en ese sentido tengo la tabla recibo y las tablas consulta medica, rayos, medicamentos,ecografia, otros servicios
Existe relaciones entre la tabla recibo y las demas tablas indicadas y como esas relaciones son de n a n es que esas tablas generadas de las relaciones tienen tres campos como llaves primarias para el caso de la tabla medicamentos como ejemplo (tiene el campo nro_recibo,nro_medicamento y nro ) los dos primeros son llaves foraneas y el tercero es uno q he creado en la tabla para que los tres sean llaves primarias
Esta bien lo que he creado de esa manera las tablas ?? yo las cree asi pues puede ser que para el caso de medicamentos haya varios medicamentos en un recibo y para mantener la consistencias y no tener datos duplicados es que adicione el campo nro a la tabla de la relacion de las tablas recibo y medicamento
Muchas gracias
Y sobre lo que me consultas acabo de llegar y lo probare nuevamente y mando mas detalles sobre lo que me indica el IB plananalizer muchas gracias

Choclito · #19 04-08-2010

Sobre la consulta amigo guillotmarc, es esta:

Código SQL [-]select  r.nro_recibo,cast(r.fecha as date),g.nombre, case r.porcentaje_cobro
                         when 100 then  r1.costo else (r.porcentaje_cobro*r1.costo)/100 end,r.costo_total,r.literal,r.nro_ambulatorio,r.usuario,r.tipo_paciente,r.nro_asegurado,r.nro_intern  acion,r.factura,cast(r.fecha as time),r.monto_cobrar,r.porcentaje_cobro,r1.nro,r.nro_factura
from  recibo r
    inner join registro__ecografia R1 on r1.nro_recibo=r.nro_recibo
    inner join ecografia g on g.nro=r1.tipo_ecografia
where ((cast(r.fecha as date) between :f1 and :f2)and((r.cancelado=:valor)or(r.cancelado='M')))

cabe indicas que trabajo con el ib expert y al crear las llaves primarias y foraneas me a creado automaticamente los indices en las tres tablas
el ib plan analyzer me muestra lo siguiente:
PLAN JOIN (G NATURAL, R1 INDEX (FK_REGISTRO__ECOGRAFIA_1), R INDEX (PK_RECIBO))
Pero en la seccion de R1 INDEX (FK_REGISTRO__ECOGRAFIA_1) me indica un icono de exclamacion y me muestra el campo selectivity de 10.91 ademas que en la grafica me muestra que la tabla ecografia es secuencial y las demas tablas estan indexadas
Gracias por responder, espero me puedan ayudar sobre esta parte pues no se que hacer mil gracias
El primer sp que les mande es grande y como me sugeriste que pruebe con los select que tengo esta consulta es con la primera q pruebo mil gracias

guillotmarc · #20 04-08-2010

Hola.

Los índices que debes tener para acelerar esa consulta estan muy claros.

Para registro__ecografia, necesitas un índice sobre nro__recibo

Para ecografia necesitas un índice sobre nro

Para recibo te recomiendo un índice simple sobre fecha.

A la vista del filtro en el WHERE te recomiendo ese índice simple, pero dado que haces una conversión del campo antes de evaluarlo, estás impediendo que el optimizador use un índice. Por ello tienes que cambiar esa parte de la consulta, por el equivalente sin conversiones.

Es decir, cambia el :

(cast(r.fecha as date) between :f1 and :f2)

Por su equivalente :

(r.fecha between :f1 and :f2 + 1 and r.fecha <> :f2 + 1)

Saludos.

Temas Similares
Tema	Autor	Foro	Respuestas	Último mensaje
Firebird, tarda mucho en conectar a base de datos en red	sonjeux	Conexión con bases de datos	1	09-04-2009 08:29:40
rewrite tarda si no hay red	jonmendi	OOP	0	25-09-2008 10:03:23
Ayuda Urgente, Por favor. Tarda mucho en traer los datos.	Paradiso	Firebird e Interbase	25	31-05-2007 04:02:37
Form que se tarda mucho en abrir	IVAND	Varios	3	29-05-2007 02:14:07
Por que tarda mucho en abrir un EXE	IcebergDelphi	Varios	5	16-06-2004 11:05:28