Saltar al contenido

Encontrar lagunas de datos faltantes en una tabla con ~ 2,5 millones de filas

Solución:

Para empezar, si bien con solo 202 meses para verificar no será un gran problema, un CTE recursivo es generalmente la peor forma posible de derivar un conjunto, en términos de rendimiento (lo demuestro aquí y aquí).

Si va a ejecutar esta consulta más de una vez (y parece que lo hará, hasta que resuelva el problema separado de quién / qué está eliminando estos datos y creando las brechas en primer lugar), ¿por qué no simplemente compilar una mesa de meses que siempre estará ahí?

CREATE TABLE dbo.Months([Month] date PRIMARY KEY);

DECLARE @StartDate     date="20000101", 
        @NumberOfYears int  = 30;

INSERT dbo.Months([Month])
  SELECT TOP (12*@NumberOfYears) 
  DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY number) -1, @StartDate) 
FROM master.dbo.spt_values;

30 años de meses, que funcionarán hasta el año 2029, almacenados en un grandísimo 72kb. Cuando escribí esto por primera vez, enfaticé sarcásticamente la friolera, pero debería explicar por qué tiene 9 páginas en lugar de las 2 esperadas. En las versiones actuales de SQL Server (inicialmente probé esto en SQL Server 2016, pero lo mismo es cierto en v.Next ), el motor de almacenamiento reserva una extensión completa y uniforme para nuevos objetos. Se trata de 8 páginas de 8 kb, más la página de IAM por 72 kb; en este caso, solo se requiere una de las páginas de datos, por lo que 7 permanecen sin asignar. Esto significa que no aparecerán en todas las vistas del catálogo, pero aún así son fáciles de encontrar (haga clic para ampliar):

ingrese la descripción de la imagen aquí

Puede desactivar este comportamiento para las bases de datos de los usuarios, pero yo personalmente no lo haría (lo convirtieron en el predeterminado por una razón). Su primer instinto podría ser ahorrar memoria en lugar de espacio en disco, pero mientras esto pone 72kb en disco, solo se cargarán 16 kb en el grupo de búferes. Así que no hay necesidad de entrar en pánico por eso.

Ahora tu consulta puede ser:

DECLARE @startDate date="20000101", @endDate date="20161101";

;WITH shortcodes AS
(
  SELECT DISTINCT ShortCode 
  FROM dbo.VWTBL_INDICATOR
  WHERE MonthYear >= @startDate AND MonthYear <= @endDate
)
SELECT m.[Month], s.ShortCode 
FROM dbo.Months AS m
CROSS JOIN shortcodes AS s
LEFT OUTER JOIN dbo.VWTBL_INDICATOR AS vwtbl
ON s.ShortCode = vwtbl.ShortCode
AND m.[Month] = vwtbl.MonthYear
WHERE m.[Month] >= @startDate AND m.[Month] <= @endDate
AND vwtbl.MonthYear IS NULL;

Tenga en cuenta que actualmente esto identificará todos los meses en su rango definido donde no aparece un ShortCode, incluso si está fuera del rango válido para ese Código corto. Si esos rangos válidos por ShortCode están definidos en algún lugar, agregue esa información a la pregunta.

¿Qué diablos es un “VWTBL”?

Voy a abordar la pregunta que no hizo: ¿Por qué mis datos están desapareciendo??

Datos hipocresía simplemente desaparece de las tablas SQL por sí solo (sin corrupción), debe haber algo que lo elimine.

Podría ser un usuario malintencionado o algo así, pero en mi experiencia es mucho más probable que sea algo así como una rutina de archivo mal escrita que captura más filas de las previstas. ¿Existen rutinas de mantenimiento que se ejecutan en la base de datos para limpiar registros antiguos?

Mencionaste que contrataste parte del soporte de la base de datos, ¿puedes plantear esto como un problema de alta prioridad con ellos? Podría ser una de sus rutinas hacerlo.

Además, es posible que estas filas no se eliminen como usted piensa: tal vez hay una consulta mal escrita que ACTUALIZA un montón de filas con la fecha incorrecta y una rutina diferente que las marca como inválidas / duplicadas y las BORRA o algo así.

Finalmente, ¿esta tabla está particionada? Si está particionado por fecha, y tiene algunas ventanas de fecha móviles elegantes, podría haber problemas con la forma exacta en que se configura.

Pero desde cero, esto es lo que comprobaría:

1. Verifique la base de datos en busca de corrupción

Si no lo está haciendo de forma rutinaria, haga un DBCC CHECKDB en la base de datos durante las horas libres. Si devuelve un error, es posible que tenga un problema mayor.

2. Bloquea la seguridad de tu usuario

Identifique los tipos de acceso que necesitan los diferentes grupos de personas y bríndeles el mínimo necesario. Puede hacer esto en el nivel de la base de datos (a través de roles) o en el nivel de la tabla individual (a través de permisos explícitos).

¿Solo ejecuta informes? Solo lectura.

¿Haciendo importaciones de datos? INSERT, pero no ACTUALIZAR ni ELIMINAR.

3. Ejecute un seguimiento para observar la actividad de la base de datos.

Puede ejecutar un Profiler Trace (o iniciar un rastreo del lado del servidor) para ver cuándo ocurren las eliminaciones. Agregue un filtro para ELIMINAR para reducir el número de filas capturadas.

4. Realice un seguimiento de las eliminaciones en la mesa

Hay algunas formas de realizar un seguimiento de las declaraciones de eliminación que se produzcan, que se analizan en esta pregunta. En su situación, parece que un disparador de mesa sería la solución más simple.

No es necesario generar fechas.


La siguiente consulta le dará una lista de SHORTCODES sin filas en absoluto:

select SHORTCODE from shortcodes
except
select SHORTCODE from VWTBL_INDICATOR

La siguiente consulta le dará los rangos continuos de MonthYear por SHORTCODE.

select      SHORTCODE
            ,min(MonthYear) as from_MonthYear
            ,max(MonthYear) as to_MonthYear
            ,count(*)       as months

from       (SELECT   SHORTCODE
                    ,MonthYear
                    ,row_number() over (partition by SHORTCODE order by MonthYear)  as rn

            From     VWTBL_INDICATOR
            ) t

group by    SHORTCODE
            ,DATEADD(month,-rn,MonthYear)   

order by    SHORTCODE
            ,from_MonthYear

Si lo desea, puede utilizar la siguiente versión que tiene una capa adicional de información:

  • missing_from_MonthYear + to_MonthYear: rango faltante en el medio
  • rangos: número de rangos por SHORTCODE (rangos> 1 significa que tiene espacios en el medio)
  • range_seq: el número secuencial de cada rango de SHORTCODE
  • is_first: Indicación para el primer rango por SHORTCODE (verifique from_MonthYear para ver si faltan fechas anteriores)
  • is_last: Indicación para el último rango por SHORTCODE (verifique to_MonthYear para ver si faltan las siguientes fechas)

select      SHORTCODE
           ,from_MonthYear                                                                                  as exists_from_MonthYear
           ,to_MonthYear                                                                                    as exists_to_MonthYear
           ,dateadd (day,1,to_MonthYear)                                                                    as missing_from_MonthYear
           ,dateadd (day,-1,lead (from_MonthYear) over (partition by SHORTCODE order by from_MonthYear))    as missing_to_MonthYear
           ,count       (*) over (partition by SHORTCODE)                                                   as ranges
           ,row_number  ()  over (partition by SHORTCODE order by from_MonthYear)                           as range_seq
           ,case from_MonthYear when min(from_MonthYear) over (partition by SHORTCODE) then 1 end           as is_first
           ,case to_MonthYear   when max(to_MonthYear)   over (partition by SHORTCODE) then 1 end           as is_last

from       (select      SHORTCODE
                       ,min(MonthYear)  as from_MonthYear
                       ,max(MonthYear)  as to_MonthYear
                       ,count(*)        as months

            from       (SELECT      SHORTCODE
                                   ,MonthYear
                                   ,row_number() over (partition by SHORTCODE order by MonthYear)   as rn

                        From        VWTBL_INDICATOR
                        ) t

            group by    SHORTCODE
                       ,DATEADD(month,-rn,MonthYear)    
            ) t

order by    SHORTCODE
           ,from_MonthYear
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *