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
    )

Pleas use some proper code formatting, both in your projects as on SO and other forums. E.g. like I just did — Jens Schauder
– Jens Schauder, Commented Jan 29, 2010 at 8:27
Testing i have seen the problem relays in this part: and CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) NOT IN (select fechafestiva from diasfestivos) If i comment it the query executes in 1 sec... — VSP
– VSP, Commented Jan 29, 2010 at 9:38
The CAST(FLOOR thing its a trick to remove the hour minute part of the datetime...otherwise 2008-03-18 16:19:10.000 != 20080318 If somebody know an optimal way i would be thankful — VSP
– VSP, Commented Jan 29, 2010 at 9:50
And sorry for the bad formatting i didnt see any tags for code regions... — VSP
– VSP, Commented Jan 29, 2010 at 9:51

gbn · Accepted Answer · 2010-01-29 08:24:51Z

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

answered Jan 29, 2010 at 8:24

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

edosoft · Accepted Answer · 2010-01-29 08:25:53Z

1

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?

answered Jan 29, 2010 at 8:25

edosoft

17.4k25 gold badges81 silver badges114 bronze badges

1 Comment

VSP Over a year ago

Using this change took a little more time than the original 7:28 min

Collectives™ on Stack Overflow

Optimize sql query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related