Información de Tablas [Script SQL]

Este script puede ser utilizado para obtener la información de tablas.

SELECT TAB.NAME AS NOMBRE_TABLA, COL.NAME AS NOMBRE_COLUMNA, TIP.NAME AS TIPO_DATO, COL.MAX_LENGTH AS TAMAO,

CASE
    WHEN COL.IS_NULLABLE = '0' THEN 'NO'

    ELSE 'SI'

END AS ACEPTA_NULL,

EXT.VALUE AS DESCRIPCION
    
FROM SYS.TABLES TAB

INNER JOIN

SYS.COLUMNS COL ON TAB.OBJECT_ID = COL.OBJECT_ID

INNER JOIN

SYS.TYPES TIP ON TIP.USER_TYPE_ID = COL.USER_TYPE_ID

LEFT OUTER JOIN

SYS.EXTENDED_PROPERTIES EXT ON EXT.MAJOR_ID = TAB.OBJECT_ID AND COL.COLUMN_ID = EXT.MINOR_ID

Reemplazando Cursores con Consultas SQL

Para poner las cosas en claro, creo que así como yo, casi todos prefieren evitar los cursores en la mayoría de los casos. ¿Son necesariamente malos? ¿Es una regla absoluta el evitar a un cursor? No. Algunas veces la operación que se necesita no puede ser hecha en base a conjuntos de datos, y un cursor puede ser desarrollado más rápido que un programa. Algunas veces es conocido que el avance en el bucle sólo involucra cientos de registros y no tomará mucho tiempo ejecutarse. Algunas veces estás trabajando con cursores ya existentes y sólo necesitas unos cambios menores, modificando el cursor en este escenario será más rápido que reescribir el procedimiento desde cero. Pero en general, yo intento evitar cursores mientras sea posible, y siempre busco alternativas para codificar la lógica.

Es posible remover a esos feos cursores de la lógica de datos, en algunos casos es más complicado. Vamos a ver un ejemplo de un cursor que tal vez deberíamos reemplazar. Por ejemplo un cursor utilizado para extraer información de estudiantes y que actualice la elección de transporte, un estudiante a la vez. La lógica es la siguiente:

1. Recuperar el nivel de grado del estudiante (inicial, primaria, o secundaria) en una variable.

2. Limpiar el código de transporte si el estudiante está dentro de la distancia adecuada para caminar a la escuela (la distancia es determinada en base también al nivel del estudiante).

3. Si el estudiante no está en una categoría particular (educación especial por ejemplo) y no está en un conjunto particular de colegios, establece el código de transporte a inelegible si los estudiantes están fuera de la zona.

4. Si el estudiante está en un programa de mediodía, actualizar el código de transporte para resaltar el hecho que el transporte será por mediodía.

5. Actualizar el código de transporte si el estudiante no reúne ninguno de los otros criterios.

La lógica utilizada en el cursor sería una enredada telaraña de sentencias de actualización, anidadas en una lógica muy compleja. Para hacerlo peor, digamos que hay 60 mil estudiantes, y esto podría tomar algo de 20 minutos para ejecutarse.

El Cursor

Veamos ahora como podría ser el cursor a utilizar para la operación que explicamos anteriormente.







 
DECLARE  EstudiantesCursor   CURSOR FAST_FORWARD FOR
SELECT
NumeroEstudiante,
CodigoEscuela,
CodigoTransporte,
NivelGrado,
CodigoGeografico,
ZonaHogar,
CodigoBilingual,
CodigoSped
FROM
-- [Tablas para obtener la información de estudiantes]   OPEN EstudiantesCursor   FETCH NEXT FROM EstudiantesCursor INTO
@
NumeroEstudiante,
@
CodigoEscuela,
@
CodigoTransporte,
@
NivelGrado,
@
CodigoGeografico,
@
ZonaHogar,
@
CodigoBilingual,
@
CodigoSped


WHILE @@FETCH_STATUS = 0
BEGIN
-- Para el nivel de grado, ver si el estudiante es inelegible


              -- para transporte (vive muy cerca de la escuela).
IF EXISTS(SELECT Sch FROM DistanciaCaminando
WHERE CodigoEsc = @CodigoEscuela
AND CodigoGeo = @CodigoGeografico


                           AND NivelGrado = @NivelGrado)
BEGIN
UPDATE
Registro
SET
CodigoTransporte = '',
LastUpdateUserID= 'CScrpW' -- inelegible
WHERE
NumeroEstudiante = @
NumeroEstudiante



AND CodigoEsc = @CodigoEscuela  GOTO FetchNext
END     IF ((rtrim(@CodigoBilingual) = ''
OR RTRIM(isnull(@
CodigoBilingual,'')) = 'S')
AND (@CodigoSped = ''
OR SUBSTRING(@CodigoSped, 1, 1) IN ('S', 'R')
OR @CodigoSped IN ('V1', 'V2', 'P1', 'P2')))
BEGIN
IF @NivelGrado <> 'H'
AND NOT EXISTS
(SELECT Escuela FROM BufferZoneLookup
WHERE Escuela = @Escuela
AND
NivelGrado = @NivelGrado
AND (CodigoGeo = @CodigoGeografico
OR ZonaHogar IN (@ZonaHogar, 'C','H')))
BEGIN
-- codigo aqui para ejecutar una actualizacion pero


--indicando el codigo de transporte a 'C0RR' y el UltimoUsuario a 'CScrpZ'
END   GOTO FetchNext
END   -- Dos actualizaciones adicionales con condicionales



FetchNext:
FETCH NEXT FROM EstudiantesCursorINTO
@NumeroEstudiante,
@
CodigoEscuela,
@
CodigoTransporte,
@
NivelGrado,
@
CodigoGeografico,
@
ZonaHogar,
@
CodigoBilingual,
@
CodigoSped


	END
END
CLOSE EstudiantesCursor
DEALLOCATE EstudiantesCursor



Lo siguiente que hay que hacer para reemplazar el cursor con una sentencia SQL de conjuntos de datos es identificar las condiciones y bucles a repetir.



1. Encapsular las condiciones if/else que hay en el cursor en una tabla temporal con múltiples columnas tipo BIT. Para estas, mas de una de las condiciones pueden ser establecidas.



2. Utilizar la tabla temporal con las configuraciones tipo bit para determinar el código de transporte. Las sentencias de actualización simplemente modifican el código de transporte ý la última actualización al usuario, de modo que eso se realiza en una tabla temporal secundaria. En la lógica, el orden de las sentencias case realiza las actualizaciones en el cursor.



3. Utilizar los códigos de transporte que tenemos en la tabla 2 para realmente hacer las actualizaciones.



A continuación el SQL resultante:















 

-- Esta consulta llenará una tabla con diferentes campos tipo bit 
-- representando las condiciones if/else utilizadas en el cursor.
-- de modo que esto esencialmente centraliza las condiciones if/else en un
-- registro por cada estudiante que pueda ser usada para la siguiente consulta.
SELECT
ds.NumeroEstudiante,
ds.CodigoEscuel,
ds.CodiogTransporte,
ds.NivelGrado,
ds.CodigoBilingual,
ds.CodigoSped,
ds.CodigoGeografico,
ds.ZonaHogar,
(CASE WHEN ds.sch IN('4261','1010','1020','1340')
THEN 1 ELSE 0 END) AS 'NoPuedeEstarFueraDeZona',


– distancia ciudad
(CASE WHEN isnull(ZonaEsc.esc, '') <> '' THEN 1 ELSE 0 END)
AS 'EnZonaOZonaBuffer',
(CASE WHEN isnull(DistanciaCaminando.es, '') <> '' THEN 1 ELSE 0 END)
AS 'EstaEnLaDistanciaCamino',
(CASE WHEN ds.grado IN ('K0', 'K1')
AND ds.esdiaextendido = 0 THEN 1 ELSE 0 END)
AS 'InicialMedioDia',
-- Bilingual estudiantes de otro idioma que siempre necesitan transporte.
(CASE WHEN NOT isnull(ds.CodigoBilingual, '') = ''
AND NOT (isnull(ds.CodigoBilingual,'')) = 'S' THEN 1 ELSE 0 END)
AS 'BilingualInelegibleParaC0RR',
-- casos adicionales omitidos


 [SpedInelegibleParaC0RR]
INTO #EstudiantesProcesar


FROM


—[lista de tablas] similar a las que están en la definición del cursor


—usa los campos tipo bit para indicar el tipo de codigo de transporte


SELECT  ds.numeroEstudiante, ds.codigoTransporte AS 'curTransCodigo',
-- en el rango idnicado
(CASE WHEN
EstaEnLaDistanciaCamino= 1 THEN ''
-- indica fuera de zona, secundaria y otras categorias no pueden estar


--fuera de zona


	WHEN BilingualInelegibleForC0RR = 0
AND NOT sys.grado IN('09','10','11','12')
AND EnZonaOZonaBuffer = 0
AND NoPuedeEstarFueraZona = 0 THEN 'C0RR'
WHEN (EsInicialMedioDia = 1
AND isnull(sys.transcode, '') = '') THEN 'C5*R'
WHEN (EsInicialMedioDia = 1
AND isnull(sys.codigotransporte, '') <> '') THEN sys.codigotransporte
ELSE 'C5RR' END) AS 'CodigoPropuestoTransporte'
INTO #CambiosTransportecodigoPropuesto
FROM
-- [lista de tablas] 


--ejecuta la actualizacion, indica los codigos de elegibilidad de transporte 


--[c5rr, c0rr, etc].  
-- cada actualización usa un ID diferente
UPDATE ds SET codigotransporte =
CodigoPropuestoTransporte,
ultimoUsuario = (CASE isnull(CodigoPropuestoTransporte, '')
WHEN 'c5rr' THEN 'CScrpC'
WHEN 'c0rr' THEN 'CScrpZ'
WHEN '' THEN 'CScrpW'
WHEN 'C5*R' THEN 'CScrpK'
ELSE 'CScrpX' END)
FROM
-- [lista de tablas]

 


Esta consulta se ejecutará 10 o 100 veces más rápido que el cursor. ¿Porqué es tan lento el cursor? Porque cada estudiante es procesado uno a la vez. Y para empeorar las cosas, dentro de cada bucle del cursor, hay algo de cinco consultas a la vez, lo que si contamos con 60 mil estudiantes, serían algo de 300 mil consultas. Y sólo se necesitaban 3. Pero, y siempre hay un pero en la vida, la opción de usar un cursor mejora la depuración y lo hace más flexible que la opción de consultas. Siempre hay que decidir que es lo que queremos obtener: velocidad o simplicidad.

¿Dónde Poner la Lógica? ¿En SQL o en el código?

SQL es un poderoso lenguaje de programación y es muy bueno para lo que fue hecho, consultas de conjuntos de datos. C#, Java y otros lenguajes son también poderosos, más poderosos que SQL en muchas formas. Para bucles y tareas en forma de procedimientos, C# puede procesar cientos de registros muchas veces más rápido que un cursor SQL, e incluso hacerlo en unas líneas de código más escasas. SQL simplemente no fue hecho para hacer ese tipo de procesamiento. En vez de eso, SQL fue hecho para procesar conjuntos de datos donde las tablas y conjuntos de resultados son utilizados y combinados en base a condiciones.

De manera similar, SQL es de lejos la interfaz de usuario que uno quiere tener. Muchas tareas que necesitan una interfaz de usuario, no son apropiadas para la lógica SQL. Por ejemplo, si los nombres de clientes deberían aparecer en una rejilla web con el formato [nombres],[apellido paterno][apellido materno], una opción es retornar los datos desde una sentencia SQL con ese formato. Sin embargo, un método más apropiado es simplemente retornar las partes del nombre, y que la lógica en la interfaz de usuario formatee el nombre completo en el formato deseado.

Algunos puntos para tomar en cuenta:

1. ¿Qué pasa si la capa de negocios o la interfaz de usuario está bloqueada y la única forma de cambiar el formato es cambiar los datos que se carga?

2. ¿Qué pasa si en vez de que los datos sean llamados por una aplicación o sólo un sitio web, sean llamados por 2 o 3 aplicaciones web, y estén disponibles como un servicio web, y de paso por una aplicación de consola? Mientras que en teoría un componente de negocios puede ser desarrollado para manejar todas esas llamadas, una opción más fácil es simplemente centralizar a nivel de base de datos en un procedimiento almacenado la salida de datos.

3. ¿Qué pasa si los resultados necesitan ser llamados por otra pieza de lógica SQL? Podría ser transformados en una vista.

4. ¿Qué pasaría si el resultado está destinado a mostrarse como un archivo de Excel que será rápidamente modificado ya que el usuario pide un reporte personalizado? ¿Qué pasa si ellos solicitan otra vez una pequeña modificación de la lógica unos cuantos minutos después que finalices de modificar el formato del archivo de Excel? ¿No podría ser más fácil tener la lógica en el SQL en este caso?

5. ¿Qué sucede si el desarrollador que escribió la lógica es simplemente más adepto a una solución basada en SQL para lo que se necesita?

Para mi, si algo puede ser hecho en forma de conjuntos de datos, trato de hacerlo en SQL para terminar el trabajo, en todo lo que sea posible que pueda ser hecho en forma de conjuntos de datos, vayamos a la lógica SQL (y todo lo que requiera lógica de procedimientos va en el código).

¿Que dices tú?