Consulta, seleccion, por periodo de tiempo

Sugey · #1 (**permalink**) 14/05/2007, 14:25

Hola, tengo una consulta, que forma parte de un procedimiento almacenado, ahorita estoy analizando la consulta, y me regresa registros de una tabla, todo lo demas son validaciones de relaciones de tablas, mm algo asi..

consulta sql:
SELECT TOP 1000 RES.Id_Senal, RES.Fecha_Registro, RES.Valor
FROM (SELECT DISTINCT VALORES_ANALOGICOS.Id_Senal, VALORES_ANALOGICOS.Fecha_Registro,
VALORES_ANALOGICOS.Valor FROM VALORES_ANALOGICOS
INNER JOIN VARIABLES_C01 ON VALORES_ANALOGICOS.Id_Senal = VARIABLES_C01.Id_Senal
INNER JOIN TIPOS_DE_SISTEMAS ON VARIABLES_C01.No_Sistema = TIPOS_DE_SISTEMAS.No_Sistema
WHERE (TIPOS_DE_SISTEMAS.No_Central IN ('1') )
AND (CAST(VALORES_ANALOGICOS.Fecha_Registro AS DATETIME)
BETWEEN '02/05/2007 10:00:00' and '02/05/2007 15:00:00')
AND VALORES_ANALOGICOS.Id_Senal IN ('A01U1040','l01u1040')
UNION SELECT DISTINCT VALORES_BINARIOS.Id_Senal, VALORES_BINARIOS.Fecha_Registro,
VALORES_BINARIOS.Valor FROM VARIABLES_C01 INNER JOIN TIPOS_DE_SISTEMAS
ON VARIABLES_C01.No_Sistema = TIPOS_DE_SISTEMAS.No_Sistema INNER JOIN
VALORES_BINARIOS ON VARIABLES_C01.Id_Senal = VALORES_BINARIOS.Id_Senal
WHERE (TIPOS_DE_SISTEMAS.No_Central IN ('1') )
AND (CAST(VALORES_BINARIOS.Fecha_Registro AS DATETIME)
BETWEEN '02/05/2007 10:00:00' and '02/05/2007 12:00:00')
AND VALORES_BINARIOS.Id_Senal IN ('A01U1040','l01u1040') )
RES ORDER BY RES.Fecha_Registro, RES.Id_Senal

Resultados :

A01U1040 2007-05-02 12:20:32.000 59.969999084472668
A01U1040 2007-05-02 12:20:41.000 59.990001678466797
A01U1040 2007-05-02 12:20:52.000 59.9400016784668
A01U1040 2007-05-02 12:21:02.000 59.930001678466802
A01U1040 2007-05-02 12:21:12.000 59.960001678466803
A01U1040 2007-05-02 12:21:22.000 59.920001678466804
A01U1040 2007-05-02 12:21:52.000 59.900001678466801
A01U1040 2007-05-02 12:22:02.000 59.910001678466799
A01U1040 2007-05-02 12:22:12.000 59.910001678466799
A01U1040 2007-05-02 12:22:23.000 59.930001678466802
A01U1040 2007-05-02 12:22:33.000 59.930001678466802
A01U1040 2007-05-02 12:22:43.000 59.9400016784668
A01U1040 2007-05-02 12:22:54.000 59.910001678466799
A01U1040 2007-05-02 12:23:05.000 59.920001678466797
A01U1040 2007-05-02 12:23:13.000 59.910001678466799
A01U1040 2007-05-02 12:23:21.000 59.920001678466797
A01U1040 2007-05-02 12:23:33.000 59.950001678466798
A01U1040 2007-05-02 12:23:42.000 59.920001678466797
A01U1040 2007-05-02 12:23:51.000 59.910001678466799
A01U1040 2007-05-02 12:24:02.000 59.900001678466801
A01U1040 2007-05-02 12:24:12.000 59.890001678466803
A01U1040 2007-05-02 12:24:23.000 59.930001678466802
A01U1040 2007-05-02 12:24:31.000 59.930001678466802
A01U1040 2007-05-02 12:24:42.000 59.970001678466801
A01U1040 2007-05-02 12:24:53.000 60.000001678466802
A01U1040 2007-05-02 12:25:02.000 60.020001678466805
A01U1040 2007-05-02 12:25:13.000 60.020001678466805
A01U1040 2007-05-02 12:25:22.000 60.020001678466805
A01U1040 2007-05-02 12:25:33.000 60.010001678466807
A01U1040 2007-05-02 12:25:42.000 59.990001678466804
A01U1040 2007-05-02 12:25:52.000 59.970001678466801
A01U1040 2007-05-02 12:26:03.000 59.980001678466799
A01U1040 2007-05-02 12:26:23.000 59.980001678466799
A01U1040 2007-05-02 12:26:33.000 59.970001678466801
A01U1040 2007-05-02 12:26:43.000 59.980001678466799
A01U1040 2007-05-02 12:27:02.000 59.990001678466797
A01U1040 2007-05-02 12:27:12.000 60.020001678466798
A01U1040 2007-05-02 12:27:22.000 60.000001678466795
A01U1040 2007-05-02 12:27:32.000 60.020001678466798
A01U1040 2007-05-02 12:27:42.000 60.020001678466798
A01U1040 2007-05-02 12:27:52.000 60.000001678466795
A01U1040 2007-05-02 12:28:02.000 59.970001678466794
A01U1040 2007-05-02 12:28:12.000 59.940001678466793
A01U1040 2007-05-02 12:28:32.000 59.950001678466791
A01U1040 2007-05-02 12:28:54.000 59.930001678466787
A01U1040 2007-05-02 12:29:01.000 59.900001678466786
A01U1040 2007-05-02 12:29:12.000 59.920001678466789
A01U1040 2007-05-02 12:29:24.000 59.930001678466787
A01U1040 2007-05-02 12:29:32.000 59.910001678466784
A01U1040 2007-05-02 12:29:42.000 59.930001678466787
A01U1040 2007-05-02 12:29:54.000 59.940001678466786
A01U1040 2007-05-02 12:30:06.000 59.930001678466787
A01U1040 2007-05-02 12:30:12.000 59.920001678466789
A01U1040 2007-05-02 12:30:42.000 59.920001678466789
A01U1040 2007-05-02 12:30:52.000 59.910001678466791
A01U1040 2007-05-02 12:31:02.000 59.920001678466789
A01U1040 2007-05-02 12:31:15.000 59.920001678466789
A01U1040 2007-05-02 12:31:24.000 59.930001678466787
A01U1040 2007-05-02 12:31:32.000 59.940001678466786
A01U1040 2007-05-02 12:31:42.000 59.990001678466783
A01U1040 2007-05-02 12:31:54.000 59.980001678466785
A01U1040 2007-05-02 12:32:05.000 59.970001678466787
A01U1040 2007-05-02 12:32:12.000 59.970001678466787
A01U1040 2007-05-02 12:32:23.000 59.99000167846679
A01U1040 2007-05-02 12:32:32.000 60.000001678466788
A01U1040 2007-05-02 12:32:42.000 60.000001678466788
A01U1040 2007-05-02 12:32:52.000 60.000001678466788
A01U1040 2007-05-02 12:33:15.000 59.970001678466787
A01U1040 2007-05-02 12:33:32.000 59.99000167846679
A01U1040 2007-05-02 12:33:42.000 60.000001678466788
A01U1040 2007-05-02 12:34:12.000 59.960001678466789
A01U1040 2007-05-02 12:34:21.000 59.960001678466789
A01U1040 2007-05-02 12:34:32.000 59.940001678466786
A01U1040 2007-05-02 12:34:53.000 59.910001678466784
A01U1040 2007-05-02 12:35:03.000 59.900001678466786
A01U1040 2007-05-02 12:35:13.000 59.900001678466786
A01U1040 2007-05-02 12:35:33.000 59.900001678466786
A01U1040 2007-05-02 12:35:44.000 59.910001678466784
A01U1040 2007-05-02 12:35:54.000 59.930001678466787
A01U1040 2007-05-02 12:36:03.000 59.930001678466787
A01U1040 2007-05-02 12:36:14.000 59.920001678466789
A01U1040 2007-05-02 12:36:22.000 59.920001678466789
A01U1040 2007-05-02 12:36:33.000 59.950001678466791
A01U1040 2007-05-02 12:36:42.000 59.940001678466793
A01U1040 2007-05-02 12:37:04.000 59.940001678466793
A01U1040 2007-05-02 12:37:13.000 59.960001678466796
A01U1040 2007-05-02 12:37:21.000 59.950001678466798
A01U1040 2007-05-02 12:37:33.000 59.9400016784668
A01U1040 2007-05-02 12:37:43.000 59.930001678466802
A01U1040 2007-05-02 12:38:04.000 59.910001678466799
A01U1040 2007-05-02 12:38:13.000 59.910001678466799
A01U1040 2007-05-02 12:38:23.000 59.900001678466801
A01U1040 2007-05-02 12:38:32.000 59.930001678466802
A01U1040 2007-05-02 12:38:46.000 59.900001678466801
A01U1040 2007-05-02 12:38:51.000 59.890001678466803
A01U1040 2007-05-02 12:39:01.000 59.910001678466806
A01U1040 2007-05-02 12:39:12.000 59.910001678466806
A01U1040 2007-05-02 12:39:22.000 59.900001678466808
A01U1040 2007-05-02 12:39:32.000 59.900001678466808
A01U1040 2007-05-02 12:39:52.000 59.900001678466808

Ahora si se dan cuenta, esa consulta me regresa registros, de una misma variable (A01U1040) que realmente son mas, pero bueno, esos registros, son cambios que tuvo esa variable, y cada reglon se registra cada 10 segundos, ahora por ejemplo si yo quisiera solo, los registros ke pertenece a un minuto, es decir ahora ke no se vean todos los registros con un margen de 10 segundos, si no de un minuto, me explico ???

me pidieron, con valor instantaneo, ke es el valor normal
valor promedio :S
valor maximo :S

Es decir tengo q hacer una seleccion, de esos registros, komo puedo decirle ke solo me mand los registros ke cambiaron en un minuto

gracias, saludos

Sugey · #2 (**permalink**) 16/05/2007, 17:07

Hola, creo que mi mensaje anterior, no tuvo mucho exito, jajaja.

Ahora tratare de explicarlo mejor, ya hice un script para solucionar mi necesidad de informacion, y me devuelve lo que quiero, pero tiene un detalle se tarda demasiado :(

Tengo una tabla que tiene datos, que varian por 10 segundos, tiene muchos registros, son 7 millones en la tabla, pero el usuario quiere poder elegir un margen de uno o mas minutos.

Para buscar registros que variaron en un minuto de diferencia, de un lapso de 2 horas, se tarda 16 minutos en procesar la consulta :S

No puedo poner eso en la web, se va a terminar el timeout :S

Miren este es mi script:

DROP TABLE #temp_val_ana
GO
CREATE TABLE #temp_val_ana (
[id_senal] [nvarchar] (10) COLLATE Modern_Spanish_CI_AS NOT NULL ,
[fecha_registro] [datetime] NOT NULL ,
[Valor_promedio] [float] NOT NULL ,
[Valor_maximo] [float] NOT NULL ,
[Valor_minimo] [float] NOT NULL
) ON [PRIMARY]

declare @fecha_inicial datetime
declare @fecha_final datetime
DECLARE @f_reso datetime
declare @reso int

set @fecha_inicial= '02/05/2007 12:20:00'
set @fecha_final= '02/05/2007 14:20:00'
set @reso= 1

while (@fecha_inicial <= @fecha_final )
begin
select @f_reso = dateadd(mi,@reso,@fecha_inicial)

insert into #temp_val_ana

SELECT VAL_ANA.Id_Senal, MIN(VAL_ANA.Fecha_Registro) AS Expr1, AVG(VAL_ANA.Valor) AS promedio, MAX(VAL_ANA.Valor) AS val_max, MIN(VAL_ANA.Valor) AS val_min

FROM dbo.VALORES_ANALOGICOS VAL_ANA INNER JOIN
dbo.VARIABLES_C01 VAR_C01 ON VAL_ANA.Id_Senal = VAR_C01.Id_Senal INNER JOIN
dbo.TIPOS_DE_SISTEMAS SIST ON VAR_C01.No_Sistema = SIST.No_Sistema
WHERE ( SIST.No_Central IN ('1')) AND (CAST(VAL_ANA.Fecha_Registro AS DATETIME) BETWEEN @fecha_inicial AND @f_reso)
AND VAL_ANA.Id_Senal= 'A01U1040'
GROUP BY VAL_ANA.Id_Senal

set @fecha_inicial=@f_reso
end

-- La fecha se va incrementando minuto a minuto, se seleccionan los registros pertenecientes a ese minuto, y se sacan algunos calculos, despues la fecha se incrementa, y asi continua hasta que llega a la fecha final, que introdujo el usuario, la resolucion es de un minuto, para este ejemplo, pero el usuario podra elegirlo de un combo.

No se que esta alentando ese script, no se si sea la tabla temporal, o ke...... me dijeron que otra alternativa son vistas, pero como puedo crear la vista e ir insertandole registros en el ciclo ??

Creo que no pueden modificarse, y se construyen a partir de un select, mmm bueno, ojala tengan alguna otra sugerencia..

Gracias, Saludos.

Andres95 · #3 (**permalink**) 17/05/2007, 09:00

Espero te sirva...

1. Para evitar realizar n consultas (60 consultas en un rango de una hora) a la tabla super grande, primero se insertan los rangos en una temporal (seria bueno tener los rangos en una fisica para no estarlos calculando cada vez, pero por el momento servirá) y luego se realiza un solo select agrupando por rangos.

2. Se agrega (nolock) a las tablas, porque cuando algun proceso realiza inserts/updates a una tabla, la bloquea (por pagina, tabla, etc dependiendo..) y el select tiene que esperar a que se libere, suponiendo que el bloqueo dura escasos milisegundos, pero considerando la cantidad de registros y la cantidad de consultas, esto incrementa considerablemente el tiempo de respuesta. Con el nolock, el select ignora los bloqueos y permite leer datos no confirmados (de alguna transaccion) y como en este caso se trata de un historico no nos pegará.

3. Me quedó duda con el In para el tipo de sistema (central) como solo tiene un valor lo reemplace por un = (igual).

4. Si el campo fecha_registro es de tipo fecha no se requerirá el cast.

5. Si existen indices en las tablas, la consulta debe ser muy rapida (por fecha, id_senal, etc.. los campos del where y join)

Saludos!

Código:

 
Declare  @fecha_inicial  datetime
        ,@fecha_final    datetime
        ,@f_reso         datetime 
        ,@reso           int


If Object_id('tempdb..#tblTmpRangosFechas') is not null  drop table #tblTmpRangosFechas

Create table #tblTmpRangosFechas (
       [fecha_inicial] [datetime] NOT NULL 
      ,[fecha_final]   [datetime] NOT NULL 
      ) ON [PRIMARY]

CREATE  INDEX [IX_TmpRangosFechas] ON [dbo].[#tblTmpRangosFechas]([fecha_inicial], [fecha_final])




Set @fecha_inicial = '02/05/2007 12:20:00'
Set @fecha_final   = '02/05/2007 14:20:00'
Set @reso          = 1
Set @Id_Senal      = 'A01U1040'


-------------------------------------------------------------------------
-- Obtenemos los rangos de fechas a evaluar.
-------------------------------------------------------------------------
While (@fecha_inicial <= @fecha_final )
Begin 
   Insert into @tblTmpRangosFechas (fecha_inicial, fecha_final)
   Values (@fechainicial, @fecha_final)
   
   Select @fecha_inicial = dateadd(mi,@reso,@fecha_inicial)
End


-------------------------------------------------------------------------
-- Obtenemos el resultado agrupando por rangos de fechas
-------------------------------------------------------------------------
Select  a.id_senal
       ,min(a.fecha_registro) as expr1
       ,avg(a.valor)          as promedio
       ,r.fecha_inicial       as val_max    -- Rango inicial
       ,r.fecha_final         as val_min    -- Rango final
From   #tblTmpRangosFechas r
Inner join
       dbo.valores_analogicos a (nolock)
On     a.fecha_registro  between r.fecha_inicial and r.fecha_final
Inner join
       dbo.variables_c01 v  (nolock)
On     a.Id_Senal = v.Id_Senal  And
       v.id_senal=  @Id_Senal   And
       a.id_senal = @Id_Senal   
Inner join
       dbo.tipos_de_sistemas s (nolock)
On     v.no_sistema = s.no_sistema
Where  s.no_central = '1' 
Group by
       r.fecha_inicial
      ,r.fecha_final 
      ,a.Id_Senal

If Object_id('tempdb..#tblTmpRangosFechas') is not null  drop table #tblTmpRangosFechas

GO