I need to get the number of records of that query
select Atenciones.IdAtencion,count(*) as partos
from Atenciones left outer join
AtencionesDiagnosticos on Atenciones.IdAtencion=AtencionesDiagnosticos.IdAtencion left outer join
Servicios on IdServicioEgreso=Servicios.IdServicio
where Atenciones.IdServicioEgreso=302 and AtencionesDiagnosticos.IdDiagnostico between '9628' and '9654'
and (Atenciones.FechaEgreso between '20220801' and '20220831')
group by Atenciones.IdAtencion
I tried to do it like this, but the execution time is shot from 0 to 10 sec
with TablaC as(
Select Atenciones.IdAtencion,count(*) as partos
from Atenciones left outer join
AtencionesDiagnosticos on Atenciones.IdAtencion=AtencionesDiagnosticos.IdAtencion left outer join
Servicios on IdServicioEgreso=Servicios.IdServicio
where Atenciones.IdServicioEgreso=302 and AtencionesDiagnosticos.IdDiagnostico between '9628' and '9654'
and (Atenciones.FechaEgreso between '20220801' and '20220831')
group by Atenciones.IdAtencion
)
select count(*)from TablaC
Any other way to optimize the execution time?
So I got the ejection to be from 0 to 1s
Conceptually you want to count the distinct
IdAtencion
, so you could use theDISTINCT
function modifierCOUNT()
:The times should be similar to that of the original query with the
GROUP BY
, frankly I don't know why a table expression degrades performance so much.