Como comprobar la existencia de versiones solapadas en Tablas Versionadas SCD Tipo 2 (con Fecha Desde y Fecha Hasta)

Una tarea de comprobación que puede resultar de mucha utilidad, es verificar que no existe Solapamiento de Versiones en una tabla versionada SCD Tipo 2 (con Fecha Desde y Fecha Hasta). Es decir, si tengo una tabla de Titulares, para el mismo Titular (misma Clave de Negocio o Business Key) comprobar que no existen dos Versiones válidas en un mismo momento del tiempo (no existen versiones solapadas). Claro, que lo interesante es realizar ésta comprobación SIN utilizar CURSORES.

La comprobación de versiones solapadas sobre tablas versionadas SCD Tipo 2, resulta especialmente útil en entornos de Data Warehouse (DW) y Business Intelligence (BI), principalmente porque en ocasiones los procesos de carga realizan intensivas transformaciones sobre los datos de origen (cara a su explotación en entornos de Reporting, OLAP, etc), de tal modo que estas comprobaciones de integridad ayudan a garantizar que el dato resultante de nuestras transformaciones sigue siendo un dato buendo (es decir, transformado, pero bueno ;-). En entornos operacionales, estas comprobaciones pierden importancia, ya que las propias aplicaciones (ej: COBOL/DB2/CISC, Natural/ADABAS, Infomix/4GL, etc.) suelen garantizar (por programa) la integridad de los datos.

La realización de esta comprobación es bastante sencilla. Para su explicación, vamos a continuar utilizando nuestro modelo del ejemplo bancario de las Cuentas Corrientes y los Titulares. Así, una forma de hacerlo (que no es la única) es utilizar una consulta SQL (SELECT) sobre la tabla que se desea comprobar (ej: Titulares) realizando un INNER JOIN sobre la misma tabla (ej: otra vez Titulares ;-). En la cláusula ON del INNER JOIN se debe relacionar la clave de negocio de ambas tablas (ej: TitularId).

Con esta consulta, conseguiríamos relacionar cada versión existente de la tabla Titulares (la tabla Hijo desde el punto de vista de la consulta SQL), con todas las versiones del mismo Titular (la tabla Padre) incluyendo la propia versión que se está comprobando.

Sin embargo, para cada versión, nos interesa limitar la comprobación a sólo las versiones de la tabla Padre (desde el punto de vista de la consulta SQL) que se solapan en el tiempo, es decir, que coinciden en el periodo de tiempo desde Fecha Desde (incluido) hasta Fecha Hasta (excluido). Para ello, es necesario ampliar la cláusula ON de nuestra consulta SQL para comprobar el solapamiento de los campos Fecha Desde y Fecha Hasta, como se muestra a continuación:

Con este cambio, nuestra consulta SQL ya es capaz de devolvernos para cada versión de la tabla Hijo, todas las versiones de la tabla Padre que se solapen con ella. Sin embargo, esto no es lo que realmente queremos, ya que cada versión de la tabla Hijo, al menos se va a solapar con la misma versión de la tabla Padre (dado que son la misma tabla, es decir, ambas son la tabla Titulares en nuestro ejemplo). Para solucionar este problema, debemos utilizar un campo único (o una combinación de campos únicos) de la tabla (ej: un autonumérico, un Timestamp, etc.), que nos permita comprobar las versiones solapadas, pero excluyendo de la tabla Padre la versión que se está comprobando. Es suficiente con ampliar la cláusula ON en nuestra consulta SQL, como se muestra a continuación (para el ejemplo utilizaremos un campo ID que se supone único):

Una detalle muy importante sobre esta consulta, es que aunque estemos utilizando el operador INNER JOIN, por el tipo de comparación que estamos realizando, es como realizar un CROSS JOIN (un producto cartesiano de la tabla consigo misma) ya que estamos comparando cada fila de una tabla con cada fila de la misma tabla. Es decir, se trata de una consulta SQL bastante costosa (desde el punto de vista de rendimiento de SQL Server), por lo tanto será importante cuidar aspectos como la cobertura de índices, es decir, que la consulta pueda resolverse accediendo a índices (sin necesidad de acceder a la tabla) con el objetivo de minimizar el acceso a disco.

Una vez construida esta consulta SQL, podremos utilizarla en la forma que nos interese, ya sea tal cual se muestra en el anterior ejemplo, o de cualquier otro modo. Por ejemplo, se puede encerrar el código en Bloques TRY/CATCH, y realizar estas comprobaciones dentro del bloque TRY con sentencia IF lanzando un error RAISERROR(‘Texto error’,16,1), de tal modo que la ejecución del bloque TRY quede abortada con la ocurrencia del error, y transmitida la ejecución al Bloque CATCH, en la que se puede registrar o tratar el error producido.

Es importante, tener en cuenta que ejecutar una sentencia SQL como la anterior (tal como la vemos) a ejecutarla dentro de un IF EXISTS, nos puede cambiar el Plan de Ejecución. En las pruebas que he realizado (tanto en mi Laboratorio, como en entornos de producción de clientes), al utilizar dicha consulta SQL dentro de IF EXISTS el Plan de Ejecución utiliza operaciones NESTED LOOP, mientras que al utilizar dicha consulta SQL tal cual, el Plan de Ejecución generado utiliza operaciones HASH MATCH. La diferencia era muy clara y representativa: el Plan de Ejecución con NESTED LOOPS tardaba varios minutos para comprobar una tabla con unos pocos miles de filas, mientras que el Plan de Ejecución HASH MATCH era instantáneo al realizar la comprobación de la misma tabla. La solución, sencilla: Declarar una variable local dónde recoger el COUNT(*) de la Consulta SQL anterior, y evaluar con una sentencia IF dicha variable local para determinar si existen versiones solapada o no en nuestra tabla versionada SCD Tipo 2.

Resulta curioso como es necesario alterar la codificación de nuestro código Transact-SQL para conseguir optimizar el rendimiento de SQL Server, pues el razonamiento natural para muchos habría sido utilizar IF EXISTS en vez de una variable local, con la consecuente penalización de rendimiento.