Optimize sql query

Question

I have a sql that takes 1:20 min to execute. It processes a year of data but even so i feel it takes too long. I changed the IN uses with EXISTS as recommended for another query (in this case that optimization wasnt enought :S ) Do you have another suggerence to optimize it?

select gd.descripcion,count(gd.descripcion) as total 
from diagnosticos d,gruposdiagnosticos gd, ServiciosMedicos s, pacientes p,Actos a,historias h 
where p.codigo=h.codpaciente and p.codigo=a.codpaciente and p.codigo=h.codpaciente and p.codigo=s.codpaciente and h.codpaciente=a.codpaciente and h.codpaciente=s.codpaciente and a.codpaciente=s.codpaciente and h.numhistoria=a.numhistoria and h.numhistoria=s.numhistoria and a.numacto=s.numacto and h.codseccion=a.codseccion and a.codseccion=s.codseccion and d.codigo=s.codDiagnostico and gd.codigo=d.codgrupo 
and p.codcompañia ='35' and a.codseccion ='18' 
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 NOT EXISTS (select * from diasfestivos af where (datename(d,a.fecatencion) + datename(m,a.fecatencion))=(datename(d,af.fechafestiva) + datename(m,af.fechafestiva)) AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >= af.fechafestiva AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) < af.fechafestiva + 1 and datepart(yy,af.fechafestiva)='1990') 
AND NOT EXISTS (SELECT * FROM diasfestivos af WHERE CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >= af.fechafestiva AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) < af.fechafestiva + 1 AND datepart(yy,af.fechafestiva)!=1990) 
group by gd.descripcion order by gd.descripcion

Changing the query with your suggestions it makes it in 50 secs, thanks but there should be a way to reduce it more...The query now is:

select gd.descripcion,count(gd.descripcion) as total 
from diagnosticos d,gruposdiagnosticos gd, ServiciosMedicos s, pacientes p,Actos a,historias h 
where p.codigo=h.codpaciente and p.codigo=a.codpaciente and p.codigo=h.codpaciente and p.codigo=s.codpaciente and h.codpaciente=a.codpaciente and h.codpaciente=s.codpaciente and a.codpaciente=s.codpaciente and h.numhistoria=a.numhistoria and h.numhistoria=s.numhistoria and a.numacto=s.numacto and h.codseccion=a.codseccion and a.codseccion=s.codseccion and d.codigo=s.codDiagnostico and gd.codigo=d.codgrupo 
and p.codcompañia ='35' and a.codseccion ='18' 
and a.fecAtencion +1 >'20090101'
and a.fecAtencion -1 <'20091231' 
and h.modo ='Urgente'  
and DATEPART(dw,a.fecatencion)!=6 
and DATEPART(dw,a.fecatencion)!=7
AND NOT EXISTS (select * from diasfestivos af where (datename(d,a.fecatencion) + datename(m,a.fecatencion))=(datename(d,af.fechafestiva) + datename(m,af.fechafestiva)) AND a.fecAtencion +1 > af.fechafestiva AND a.fecAtencion -1 < af.fechafestiva and datepart(yy,af.fechafestiva)='1990')
AND NOT EXISTS (SELECT * FROM diasfestivos af WHERE a.fecAtencion +1 > af.fechafestiva AND a.fecAtencion -1 < af.fechafestiva AND datepart(yy,af.fechafestiva)!=1990) 
group by gd.descripcion order by gd.descripcion

I have 2 exists parts because i have two types of festivity dates. Ones especifical to the present year and others that apply for everyyear (so i insert them like 25/12/1990)

Finally I have found the problem..its in this part:

where datename(d,a.fecatencion) + datename(m,a.fecatencion))=(datename(d,af.fechafestiva) + datename(m,af.fechafestiva))

Someone knows a better way of doing it? (Compare 2 datetimes in tsql omiting the year)

Answer 1

• Use indexes
• Rewrite casts to use plain comparison probably between ... and syntax
• Use numeric compare on this datename(weekday,a.fecatencion)!= 'Sabado'
• You probably can remove the Actos a table, and replace a.codseccion ='18' with h.codseccion ='18'
• You probably can remove the diagnosticos d table as I don't see any reference to it
• You probably can remove the ServiciosMedicos s table as I don't see any reference to it
• All in all, I see you have a lot of joins, and you don't use all the tables, remove unnecessary joins
• Rewrite your last two sub queries at least to use union all eg: (select * from) table union all (select * from), then at least you will have one run for them and not two

Answer 2

Also try "Sql Server profiler".

Think about the order of your joins so that the large chunks of data are filtered out first.

Answer 3

If you have to do all that cast and floor stuff, then your database design is needs work. I firmly believe that data should be stored in the form that you need to query it in. Consider adding columns to do this once on insert/update of the data, you can make calculated fields. Then it doesn't have to happen every time you run a query.

Answer 4

You should learn how to use "DateDiff". The query should use this construct to get only records from 2009:

DATEDIFF(yy, a.fecAtencion, '2009-01-01')

A few other things...

FIRST - Don't do the casts for date.

Static dates (e.g. '2009-01-01') have an implied midnight (00:00:00) start time. Thus, you won't need the cast at all for the starting date since effectively everything in 2009 is greater than '2009-01-01'. You could also use '2010-01-01' as your end date since everything in 2009 (and nothing in 2010) will be before that.

Another reason not to use these ugly casts is that you can specify hours, minutes, seconds and msecs in a SQL Query like so: '2009-01-01 00:00:00:000' if you don't want to leave the query ambiguous in this area.

The other advice about using the profiler, etc. is still good. But I bet your problem lies in your date handling.

Answer 5

using vb.net as an example.

my objective is to move the pressure of grouping/counting the result off the sql server.

try this additional optimization, see if it can reduce the execution time?

strsql="
select gd.descripcion 
from diagnosticos d,gruposdiagnosticos gd, ServiciosMedicos s, pacientes p,Actos a,historias h  
where p.codigo=h.codpaciente and p.codigo=a.codpaciente and p.codigo=h.codpaciente and p.codigo=s.codpaciente and h.codpaciente=a.codpaciente and h.codpaciente=s.codpaciente and a.codpaciente=s.codpaciente and h.numhistoria=a.numhistoria and h.numhistoria=s.numhistoria and a.numacto=s.numacto and h.codseccion=a.codseccion and a.codseccion=s.codseccion and d.codigo=s.codDiagnostico and gd.codigo=d.codgrupo  
and p.codcompañia ='35' and a.codseccion ='18'  
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 NOT EXISTS (select * from diasfestivos af where (datename(d,a.fecatencion) + datename(m,a.fecatencion))=(datename(d,af.fechafestiva) + datename(m,af.fechafestiva)) AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >= af.fechafestiva AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) < af.fechafestiva + 1 and datepart(yy,af.fechafestiva)='1990')  
AND NOT EXISTS (SELECT * FROM diasfestivos af WHERE CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) >= af.fechafestiva AND CAST(FLOOR(CAST(a.fecAtencion AS float)) AS datetime) < af.fechafestiva + 1 AND datepart(yy,af.fechafestiva)!=1990)  
"
cmd = new sqlcommand(strsql, conn)
cmd.commandtimeout = 480
da = new sqldataadapter(cmd)

dt = new datatable
da.fill(dt)

dim dv as new dataview(dt)
dv.sort = "descripcion"

dt2 = dv.totable(true, "descripcion") 'simulate a select distinct from result
dt2.columns.add("total")

for each dr as datarow in dt2.rows
    dr("total") = dt.select("descripcion = '" & dr("descripcion ") & "'").length
next

dt2.acceptchanges()

gridview2.datasource = dt2
gridview2.databind

Answer 6

This part looks like it could be the main problem:

AND NOT EXISTS (
   select * from diasfestivos af 
   where (datename(d,a.fecatencion) + datename(m,a.fecatencion))=
         (datename(d,af.fechafestiva) + datename(m,af.fechafestiva)) 
   AND a.fecAtencion +1 > af.fechafestiva 
   AND a.fecAtencion -1 < af.fechafestiva 
   and datepart(yy,af.fechafestiva)='1990'
 ) 

This looks like it is basically:

AND NOT EXISTS (
   SELECT NULL FROM diasfestivos af 
   WHERE af.fechafestiva BETWEEN '19900101' AND '19910101'
   AND DATEPART(d, a.fecatencion) = DATEPART(d, af.fechafestiva)
   AND DATEPART(m, a.fecatencion) = DATEPART(m, af.fechafestiva)
   AND a.fecatencion != af.fechafestiva 
 )

I've been trying to find a way of doing your same date but different year without having to perform multiple functions on the dates but I haven't managed to.