consulta sql server muy lenta
Publicado por Jose Manuel (2 intervenciones) el 14/08/2015 18:32:33
Hola.
Tengo la siguiente consulta en sql para sql server
select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as fila
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') as t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) as t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
Me tarda 0.5 s lo cual para mi esta bien y devuelve 140 filas. Mi problema es que necesito cruzarla con ella misma y al hacer esto la consulta tarda minutos y minutos, lo cual no entiendo ya que son solo 142 filas las que devuelve.
Mi consulta final es esta:
select *
from
(select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as Filas
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
)tt1,
(select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as Filas
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
) tt2
where tt1.fecha1 = tt2.Filas -1
simplemente cruzar la tabla de la primera consulta (la que solo tarda 0.5s) consigo misma. Deberia devolver el mismo numero de filas que la otra.....
No se por que se vuelve tan lenta. Tengo que parar la consulta porque no devuelve nada.... se queda en ejecucion..
Por que pasa esto? y como solucionarlo?
Gracias de antemano por vuestra ayuda
Tengo la siguiente consulta en sql para sql server
select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as fila
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') as t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) as t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
Me tarda 0.5 s lo cual para mi esta bien y devuelve 140 filas. Mi problema es que necesito cruzarla con ella misma y al hacer esto la consulta tarda minutos y minutos, lo cual no entiendo ya que son solo 142 filas las que devuelve.
Mi consulta final es esta:
select *
from
(select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as Filas
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
)tt1,
(select t1.fecha1, t1.u1,ROW_NUMBER() OVER(ORDER BY t1.fila DESC) as Filas
from(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1 , ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01') t1 ,
(SELECT [LocalCol] as fecha1, isnull (Past3_User,'xx') as u1, ROW_NUMBER() OVER(ORDER BY Localcol DESC) as Fila
FROM [Log5Sec_PAST3a]
Where [localcol] > '2015-13-08 00:00:01' and [localcol] < '2015-13-08 23:59:01' ) t2
where t1.Fila = t2.Fila -1 and t1.u1 != t2.u1
) tt2
where tt1.fecha1 = tt2.Filas -1
simplemente cruzar la tabla de la primera consulta (la que solo tarda 0.5s) consigo misma. Deberia devolver el mismo numero de filas que la otra.....
No se por que se vuelve tan lenta. Tengo que parar la consulta porque no devuelve nada.... se queda en ejecucion..
Por que pasa esto? y como solucionarlo?
Gracias de antemano por vuestra ayuda
Valora esta pregunta
0