Optimize sql query

Question

I have a sql that takes 7 min to execute. It processes a year of data but even so i feel it takes too long. Do you have any suggerences to optimize it?

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

Use joins that is faster and more readable

Answer 2

At least...

Change this

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

to

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

And use "JOIN"

And

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

..to be

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

This assumes diasfestivos has less rows and it's cheaper to remove time there

Answer 3

The SQL keyword 'IN' is usually a good candidate to replace.

AND NOT EXISTS (select fechafestiva from diasfestivos where CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) = fechafestiva )

And what's up with the datetime casting?