Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-11-13 : 08:06:02
|
| Carlos writes "I have an SQL instructions that works fine for a small number of records but once I have more than a 1000 it takes forever. Could you please help or refer to a solution because the " IdEmpleado not in" (Select...) makes a huge overhead, but I do not know any other way to do it. Table Datos has information of employees, Historico has everyday registrations, Rgrupo has groups for people registered on Datos. The whole idea is to get the people that did not register in Historico a period defined by FechaHoy. select IdEmpleado,FechaHoy,Apellido,Nombre,Grupo from Datos D, RGrupo G, Historico H where D.IdGrupo = G.IdGrupo and H.FechaHoy>=D.DiaEnt and ((D.DiaSal is null) OR (D.DiaSal >=H.FechaHoy)) and (EXTRACT(DAY FROM H.FechaHoy)>=01) and (EXTRACT(DAY FROM H.FechaHoy)<= 04) and D.IdEmpleado not in (Select F.IdEmpleado from Historico F where EXTRACT(DAY FROM F.FechaHoy)>=01 and EXTRACT(DAY FROM F.FechaHoy)<= 04 and H.FechaHoy = f.FechaHoy)Group by IdEmpleado,FechaHoy,Apellido,Nombre,Grupo" |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-11-13 : 14:09:43
|
| select IdEmpleado,FechaHoy,Apellido,Nombre,Grupofrom Datos D, RGrupo G, Historico H whereD.IdGrupo = G.IdGrupo and H.FechaHoy>=D.DiaEnt and((D.DiaSal is null) OR (D.DiaSal >=H.FechaHoy)) and(EXTRACT(DAY FROM H.FechaHoy)>=01) and (EXTRACT(DAY FROM H.FechaHoy)<= 04)and NOT EXISTS(Select * from Historico F where D.IdEmpleado=F.IdEmpleado andEXTRACT(DAY FROM F.FechaHoy)>=01 and EXTRACT(DAY FROM F.FechaHoy)<= 04and H.FechaHoy=F.FechaHoy)Group by IdEmpleado,FechaHoy,Apellido,Nombre,Grupo |
 |
|
|
|
|
|
|
|
Improve query, any hints