Orden Lógico de Procesamiento de Queries

Estimados colegas y amigos, estoy una vez más en contacto con ustedes...

Esta vez para tratar de echar luz sobre un tema que, habida cuenta de la cantidad de consultas a mi correo que ha generado,  entiendo que bien merece que le dediquemos un posteo. 

Se trata del orden lógico en el que el SQL Server procesa nuestras queries independientemente de la forma en la que las mismas deben ser escritas por sintaxis.

En efecto, en el siguiente ejemplo veremos una query con el orden normal en el que debe ser escrita y, en entre paréntesis, el orden en que realmente es procesada lógicamente por el SQL Server:

SELECT (5) / DISTINCT (5.2) /  TOP (7)

FIELDS (5.1)

FROM     (1)

WHERE    (2)

GROUP BY (3)

HAVING   (4)

ORDER BY (6)

 Y la pregunta cae de madura. Por qué lógicamente al procesar la query  el motor no sigue el mismo orden que exige la sintaxis? .

 La respuesta es sencilla…; Porque el SQL imita la forma en la que serían dadas las instrucciones para obtener algo para definir el orden de las sintaxis.

Ejemplo...

Supongamos que queremos solicitarle a un familiar que nos alcance unos libros del cuarto.

Estas serían las instrucciones que le daríamos  y su orden.. 

“Traéme (Select), estos  libros (Fields) desde mi cuarto (From) cuyo autor sea Borges (Where) y haslo Ordenados por Título (Order By)” - orden de solicitud-

Pero en verdad nuestro interlocutor tendría que, (en el sgte orden):

“Ir al cuarto (From), separar sólo los libros de Borges (Where), tomar dichos libros  (Select) y traer los libros ordenados por Título (Order By)” -orden lógico real de las acciones -

Así pues debemos tener en claro que el sql server tiene el siguiente orden lógico para ejecutar una query. 

El tenerlo en cuenta es vital para poder escribir una query performante.

Veamos:

1) FROM (joins)

2) WHERE 

3) GROUP BY 

4) HAVING 

5) SELECT

    5.1 SELECT fields

    5.2 DISTINCT

6) ORDER BY 

7) TOP 

Para terminar de desarrollar el tema veamos un ejemplo de cómo  un desconocimiento del orden lógico de ejeución de queries puede llevarnos a tomar decisiones incorrectas.

Supongamos que tenemos una tabla "Clientes" con 10 millones de registros, y, nos solicitan el armar una consulta para listar aquellos 10 clientes que posean el mayor saldo en su haber.

Si no supieramos lo aprendido en relación al orden lógico de ejeución de las queries tal vez nuestro razonamiento podría ser.

“Ok..la query es fácil entonces… ni me hago problema por los filtros del where ni
por ser preciso con los campos a seleccionar... Yo hago un Select top 10 * from
la tabla ordenado descendente y listo, si total sólo voy a trabajar con 10
registros”
Les parece correcto el  razonamiento? A muchos tal vez si verdad?
La query que harían sería esta?

Select top 10 *
From Clientes
order by saldo desc

La respuesta no es correcta dado que , como hemos aprendido, por orden lógico de ejecución de queries, no estaremos seleccionando sólo 10 registros sino que se estarán seleccionando 10 millones de registros, (no hay where en la query), y luego, recién luego, se estarán listando los 10 registros originalmente solicitados...
Ya saben entonces.. a tener muy en cuenta el orden lógico de procesamiento de las queries =)
Amigos es todo por ahora les dejo mis saludos desde Argentina y quedo a vuestra disposición como siempre.

Función Isnull - Su innecesario y perjudicial uso en sobre campos numéricos -

 

Estimados amigos, el placer de saludarlos nuevamente...

   Trabajando en tunning  sobre unos scripts y encontrando una vez más un pequeño pero perjudicial y repetido  error,  aprovecho para compartirles como trato de hacer asiduamente la experiencia,  con el objetivo de  mejorar la performance de nuestros códigos en general.

Ante todo supongo que todos estamos de acuerdo con el que el uso de funciones en campos donde es muy importante el uso de un índice invalida totalmente la utilización por parte del motor de base de datos del mismo.

 

Ejemplo:

Tenemos una tabla “CONSUMO”  de la cual tenemos que listar los campos “Id”  “FechaPresentacion” cuando la fecha sea mayor al '2020-05-20 23:59:59'

Si existe  un índice que cubra el campo  “FechaPresentación” tendremos una excelente performance a partir de la plena utilización del índice:

  Si, por el contrario, aplicamos algún tipo de función sobre el campo indexado “FechaPresentacion” vamos a dejar sin chances de ser utlizado al índice veamos:

 

Habiendo repasado lo perjudicial que son el uso de funciones en determinados campos, (sobre todo de filtro y ordenamiento, vayamos al uso de la función ISNULL

 

Es muy común ver en queries el uso de esta función ante la necesidad de validar un campo numérico que puede contener valores null

 

Ejemplo:

 

Dada la misma tabla CONSUMO, debemos listar aquellos “id” cuyo consumo,  (campo “importe” de tipo Integer),  es mayor a 5000 pesos.  (hay registros  que pueden tener a la fecha importes con valor NULL)

 

Así  es común ver este tipo de queries:

 

El uso de la función Isnull sobre el campo “Importe” inutiliza el índice sobre dicho campo…

 

Pero .. es necesario el uso de la función Isnull para evitar errores de comparación de campos numéricos con posibles valores nulos? Evitamos de ese modo un posible error?

La respuesta es “no”.   

Cualquier campo numérico null será tomado por el sql server como “0” no siendo necesario utilizar la función Isnull y así inutilizar el eventual índice dispuesto sobre dicho campo.

Utilizaremos el idx y el resultado será el mismo.

Bueno gente me despido esperando que haya sido de utlilidad el tip.

Problemas con "Huecos" o "Gaps" en Campos Identity - Posibles Motivos y Soluciones

Estimados colegas, el placer de saludarlos nuevamente.

  Cuántas veces nos hemos encontrado con el problema de "saltos" o "gaps" en el valor numérico de columnas definidas como "identity" en tablas de nuestras bases de datos?.

  Seguramente en más de una oportunidad hemos sido alertados por el equipo de desarrollo o por alertas propias sobre esta incidencia que le quita secuencialidad a nuestras tablas.

  Sobre este inconveniente trata el post del día de hoy. Asumo que todos sabemos lo que un campo "identity" signfica en una tabla y su funcionalidad y por que no, que también fue utilizado en una o más tablas de vuestros sistemas con la función de contar con un valor Unico y Secuencial, delegando estas característiscas en el motor de base de datos.

   Es así entonces que surje el primer punto a tener en cuenta y es el siguiente:

  "Un campo definido como identity NO GARANTIZA LA SECUENCIALIDAD de los valores del mismo"

 Así es colegas, tal como lo leen. Esto está expresado explícitamente por Microsoft en su documentación oficial. 

Si uds. requieren   por regla de negocios, generar secuencialidad. Por ejemplo si el campo identity sería el punto de partida de la numeración de la facturación de una empresa, deben recurrir a otros métodos alternativos de generación de Ids tales como Sequence no cacheado, (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15),  u otros generados a través de programación (eg guardar un id en una tabla isolada y sumarle 1 ante cada inserción).

  Pero por qué no podemos confiar la secuencialidad en un campo identity?

  Sencillamente, y aquí viene el primer motivo de un posible "gap" en una numeración identity, porque ante un rollback, los ids rollbackeados "se perderán" y pasarán a formar parte de un hueco de ids ya no disponibles para futuras inserciones..   Así pues, si las inserciones de dos ids cualquiera, digamos el  23 y 24,  se rollbackea, dichos ids estarán faltantes en nuestra numeración de facturas.

 Ok, pero uds. tal vez llegaron a este post buscando un motivo por el cual registraron un gap en la numeración identity y están seguros de que no se debió a un rollback....  Entonces  surge la pregunta...

 Hay otro motivo por el cual se pueden registrar gaps en una columna Identity?

 La respuesta es SI

 Si estás trabajando con un motor 2012 o superior y no has modificado el comportamiento por default del cacheo de identities en SQL Server, pueden ocurrir gaps muy grandes y no tan mensurables como el de un rollback de "x registros", ante estas situaciones:

• Caídas en General del Servicio de Motor de Base de Datos no Planificadas
• Failovers

 Efectivamente, y sin entrar en detalles técnicos, la gente de Microsoft con el noble fin de ganar performance comenzó, a partir de SQL 2012, a "cachear" los Identity como comportamiento por default de motor. 

Ventajas? ... supuestamente algunas de performance...(no comprobadas por mi nunca).

Desventajas?  las que devienen cacheados estos valores "core" en muchos sistemas de bases de datos cuyas lógicas de negocio dependen en muchos cosos de los mismos.

Cacheados = sin persistencia ante los eventos mencionados = Pérdida de correlatividad y Gaps a veces muy significativos.

En mi opinión esta fue una muy mala jugada de Microsoft quien no avisó con la debida firmeza sobre un cambio que complicó y sigue complicando la vida de muchos de quienes trabajan MSqlSrvr. Aún cuando se puede discutir sobre la mala praxys que implica el confiar la correlatividad y otros puntos importantes que hacen a la regla de negocios sobre este tipo de campos.

Pero.. hay modo de evitar este comportamiento que por defecto tiene el Sql Sever para que deje de cachear los Ids?

 Si lo hay veamos las opciones:

• Modificando el Identiy Cache a nivel Instancia de Motor de Base de Datos 

   Es es la única opción disponible si trabajás con las versiones 2012/2014 y 2016 

 

   Se trata de agregar el "TRACE flag 272" como parámetro de star-up 

   Los pasos son:

   1)  Ingresar al SQL Server Configuration Manager y editar properties

  2)  Agregar, (ver botón add), el flag "-T272" en la solapa "startup parameters" y aplicar cambios.

  

3) Restartear el servicio y para aplicar los cambios

 

• Modificando el Identiy a Nivel Base de Datos

     

 Esta opción está disponible a partir del SQL Server 2017 y es mi favorita puesto que pemite ser selectivo con la Base de Datos en la cual se desea implementar el setting y adicionalmente no requiere  del reinicio del motor una vez implementado el cambio.

   La sintaxys se debe aplicar en cada base de datos y es la siguiente:

    Use database;

  go

  ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Amigos, esto es todo por ahora. Espero que el post les haya sido de utilidad y les prometo para un post adicional próximo una serie de scripts que les faciilitará el trabajo cuando de trabajar y controlar con tablas con campos identity se trate.

 Saludos cordiales desde Argentina, sigo esperando sus preguntas, sólo tengan paciencia y serán respondidas.