sql - 优化sql查询

Question

我有一个需要 7 分钟才能执行的 sql。它处理了一年的数据，但即便如此，我还是觉得它需要太长时间。您有任何优化建议吗？

select count(s.numserviciomedico) AS total 
from Actos a,
    pacientes p,
    Historias h,
    serviciosmedicos s 
where p.codigo=h.codpaciente 
    AND p.codigo=a.codPaciente 
    AND p.codigo = s.codPaciente 
    AND h.codPaciente = a.codPaciente 
    AND a.codpaciente=s.codPaciente 
    AND h.numHistoria = a.numHistoria 
    AND h.numHistoria = s.numHistoria 
    AND a.numHistoria = s.numHistoria 
    AND a.numActo = s.numActo 
    AND h.codSeccion=a.codSeccion 
    and p.codcompañia ='38' 
    and a.codseccion ='9' 
    and (CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >='20090101') 
    and (CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) <='20091231') 
    and h.modo ='Urgente' 
    and datename(weekday,a.fecatencion)!= 'Sabado' 
    and datename(weekday,a.fecatencion)!= 'Domingo' 
    and CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) NOT IN (
        select fechafestiva   
        from diasfestivos
    )

Answer 1

至少...

改变这个

and (CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >='20090101') and (CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) <='20091231') 

到

a.fecAtencion >= '20090101' AND a.fecAtencion < '20100101

并使用“加入”

和

CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) NOT IN (select fechafestiva from diasfestivos)

..成为

NOT EXISTS (SELECT * FROM diasfestivos af WHERE a.fecAtencion >= af.fechafestiva  AND a.fecAtencion < af.fechafestiva + 1)

这假设 diasfestivos 的行较少，并且在那里删除时间更便宜