sql server para programadores. aprendiendo a optimizar ... server para programadores.pdf · indices...
TRANSCRIPT
ADVERTENCIA LEGAL
Todos los derechos de esta obra están reservados a SolidQTM Press.
El editor prohíbe cualquier tipo de fijación, reproducción, transformación o distribución de esta obra, ya sea mediante venta, alquiler o cualquier otra forma de cesión o comunicación pública de la misma, total o parcialmente, por cualquier sistema o en cualquier soporte, ya sea por fotocopia, medio mecánico o electrónico, incluido el tratamiento informático de la misma, en cualquier lugar del mundo.
La vulneración de cualquiera de estos derechos podrá ser considerada como una actividad penal tipificada en los artículos 270 y siguientes del Código Penal.
La protección de esta obra se extiende al mundo entero, de acuerdo a las leyes y convenios internacionales.
© SolidQTM Press, 2015
Título: SQL Server para Programadores: Aprendiendo a Optimizar Procesos
Autor: Luis J. Morán
ISBN:
SolidQ Global S.A. Apartado de correos 202 03340 Albatera, Alicante, España http://www.solidq.com
solidq.com
2
SOLIDQ SolidQ, desde el año 2002, suministra servicios para plataformas Microsoft que le ayudan a diseñar, integrar y optimizar su utilización de datos.
Combina una amplia experiencia técnica y de implementación en el mundo real, con un compromiso firme en la transferencia de conocimiento, dada la combinación única de dotes lectivas y experiencia profesional que nuestros mentores ofrecen. De este modo, no solamente ayudamos a nuestros clientes a solventar sus necesidades tecnológicas, sino que somos capaces de incrementar la capacidad técnica de sus profesionales, dándoles una ventaja competitiva en el mercado. Por eso llamamos Mentores a nuestros expertos: por su compromiso en asegurar el éxito de su empresa y de sus equipos profesionales a largo plazo.
Nuestros expertos son profesionales reconocidos en el mercado, con más de 100 premios Microsoft MVP (Most Valuable Professional) obtenidos hasta la fecha. Se trata de autores y ponentes en las conferencias más importantes del sector, con varios centenares de ponencias presentadas en conferencias nacionales e internacionales durante los últimos años.
Nuestra misión es la de transmitir todo el conocimiento adquirido resolviendo problemas del mundo real para miles de clientes, escribiendo artículos y libros, publicando whitepapers, creando contenidos educativos y formando a decenas de miles de trabajadores de TI en todo el mundo, para que los proyectos de nuestros clientes obtengan los mayores éxitos. Esta transferencia de conocimiento la realizamos fundamentalmente con dos tipos de servicios:
Consultoría: hazlo bien la primera vez (haz clic aquí)
Mentoring: conoce tu potencial personal y mejora tus decisiones (haz clic aquí)
Formación: la mejor inversión posible es pagar por el conocimiento de otros. Conoce nuestro Plan Formativo (haz clic aquí) y nuestro Calendario (haz clic aquí)
Publicaciones: ponemos nuestros conocimientos a su alcance. Acceda a nuestros blogs (haz clic aquí) y a nuestras publicaciones, la mayoría gratuitas (haz clic aquí)
solidq.com
3
Evolución de la Programacion en TSQL ..................................................................................... 4
Tablas ......................................................................................................................................... 5
Introducción a la Estructura Física ......................................................................................... 8
Página ................................................................................................................................. 8
Extensiones ........................................................................................................................ 9
Particiones.......................................................................................................................... 9
Indices .................................................................................................................................. 26
Cluster .............................................................................................................................. 28
No Cluster ........................................................................................................................ 35
Conclusión ............................................................................................................................ 43
Vistas ........................................................................................................................................ 44
Vistas Indexadas ................................................................................................................... 46
Conclusión ............................................................................................................................ 49
Procedimientos ........................................................................................................................ 49
Queries Ad Hoc .................................................................................................................... 50
Tipos De procedimientos Almacenados .............................................................................. 54
Creación de Procedimientos ............................................................................................ 55
Rendimiento ......................................................................................................................... 63
Funciones ................................................................................................................................. 67
Escalares ............................................................................................................................... 67
Tipo Tabla ............................................................................................................................. 68
En línea ............................................................................................................................. 68
Multilínea ......................................................................................................................... 69
Rendimiento ..................................................................................................................... 70
Triggers ..................................................................................................................................... 71
Tipos de Triggers DML .......................................................................................................... 72
Instead Of ......................................................................................................................... 72
After ................................................................................................................................. 78
Clausula Output ................................................................................................................... 86
Cursores vs Conjuntos .............................................................................................................. 95
solidq.com
4
Evolución de la Programacion en TSQL Muchas veces cuando nos enfrentamos a un problema de programación solemos centrarnos en encontrar la solución más rápida y no tanto la mejor en términos de reusabilidad de codigo, mantenibilidad, complejidad, nuevas tecnologías o buenas practicas de codificación.
Junto con esto suele estar asociado la insuficiencia, o desactualizada formación en el lenguaje de programación en el que debemos desarrollar nuestra solución.
Yendo un paso más allá, y orientandonos a las bases de datos, si nos “acostumbramos a programar de esta manera” generaremos un problema mayor que suele generar falta de escalabilidad1 y deficiencia de rendimiento en nuestro código de base de datos ya que los humanos repetimos patrones de comportamiento. Además seguramente lo hayamos expandido porque habremos tocado mucho código.
A menudo nos encontramos con esta situación a los clientes que vamos. Los programadores suelen potenciar conocimientos en la parte de “lenguajes de aplicación” (C#, vb.net, Java) y no en la parte de “lenguajes de datos”, limitandose a utilizar los gestores de bases de datos relacionales como herramientas simples para hacer inserts, updates, deletes y selects sin ser capaces de explotar las capacidades máximas de estos productos. Los gestores relacionales y concretamente SQL Server son programas muy complejos y completos que están preparados (si las estructuras de programación son las adecuadas) de gestionar muy eficientemente los datos, sus operaciones y concurrencia.
Reflexionando un poco, ¿Para qué se gastaría nadie el dinero que cuesta una licencia de un gestor de bases de datos relacional moderno (que suele ser bastante dinero) si simplemente lo utiliza para operaciones simples?. La respuesta es se basa en la realidad actual, cada día se necesitan:
Gestionar el acceso de múltiples usuarios
Guardar cada vez más datos
Devolver los datos cada vez más rápido
Mayor escalabilidad
Implementar medidas de alta disponibilidad2
1 Escalabilidad es la capacidad que tiene un sistema para aumentar su rendimiento conforme aumenta la carga
2 Alta disponibilidad hace referencia a aquellas arquitecturas o configuración que permiten a un sistema seguir trabajando aunque se produzcan fallos de software/hardware
solidq.com
5
Ir contra esta tendencia es negar la realidad. Es por esto que este libro pretende establecer las bases de una buena programación de Transact SQL (TSQL) que nos permita establecer los cimientos para obtener el mayor rendimiento de nuestro SQL Server.
Comentar adicionalmente que este es un libro donde se comentarán los aspectos básicos (pero importantes) de la programación con TSQL.
Tablas Son objetos de la bases de datos que contienen información distribuida en filas y columnas. En SQL Server existen varios tipos de tablas:
Tablas en memoria. Su contenido se almacena en la memoria como dispositivo principal de trabajo y también en disco si queremos que en caso de caída, parada o reinicio de la instancia recuperar dichos datos. Este comportamiento es opcional. Esta nueva tecnología no se comenta en este libro ya que es un concepto avanzado y este libro tiene como objetivo proporcionar conocimientos básicos de programación con TSQL
Tablas en disco, son las habituales.
Tablas virtuales. Pueden ser vistas, constantes de tabla, subselects etc. Se basan en una consulta de una o varias tablas
Tablas temporales, se almacenan en tempdb y pueden ser locales (con una “#”) o globlales (con dos “#”). Las locales solo se pueden utilizar en una sesión (un ejemplo de sesión es una pestaña de SQL Server Management Studio) y las globales en todas
solidq.com
6
Variables de tabla, son variables que se comportan en algunos aspectos como tabla pero no debemos entenderlas totalmente como tales ya que también son variables y se comportan como tal. En el siguiente ejemplo lo se puede apreciar:
1. Creamos una tabla temporal 2. Declaramos una variable de tabla con la misma estructura 3. Abrimos una transaccion 4. Insertamos un registro en cada tabla 5. Cancelamos la transaccion con rollback 6. Hacemos una select de cada tabla para confirmar que al hacer rollback
no hay ningún registro en ninguna tabla
Al hacer esto podemos apreciar que la tabla temporal está sujeta al comportamiento de las transacciones ya que al hacer rollback borra el registro. La variable de tabla, se comporta como eso, como una variable, las variables no están sujuetas a las reglas de las transacciones y a pesar de hacer rollback sigue manteniendo el registro.
Aparte de esta diferencia fundamental tambien a nivel de rendimiento son distintas ya que una vez creadas las tablas se les puede añadir índices (excepto a las tablas en memorias). En las variables de tabla esto ocurre.
Estos ejemplos están en el script “0_tablas_temporales.sql”
solidq.com
7
solidq.com
8
Introducción a la Estructura Física
Página
Es la unidad básica de almacenamiento en SQL Server, cada página ocupa 8KB.
La estructura de una página es la siguiente:
La cabecera de la página ocupa 96 bytes y contiene información resumida de la página:
Número de página
Tipo de página
Espacio libre
La parte intermedia la página la ocupan las filas de datos y el final la tabla de desplazamiento donde se encuenta un “registro” por cada fila y el desplazamiento desde el principio de la página hasta ella.
Existen distintos tipos de página:
Datos, contienen la información del usuario excepto para los tipos text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml si la propiedad text in row de la tabla está establecida con el valor ON. Ver procedimiento sp_tableoption.
Indice, contienen información del contenido de los índices
LOB (Large Object Binary). Contiene información de los tipos text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml
GAM, SGAM (Global Allocation Map/Shared Global Allocation Map). Las GAM controlan las extensiones que han sido asignadas y SGAM controlan las extensiones mixtas con al menos 1 página libre
PFS (Page Free Space), controlan el espacio libre
IAM (Index Allocation Map). Gestiona las páginas que pertenecen aun índice o una tabla
solidq.com
9
DCM (Differential Changed Map). Se utiliza para indicar si una extensión en un intervalo GAM ha sido modificado
BCM (Bulk Changed Map). Este tipo de páginas se utiliza para indicar cuando una extensión en un intervalo GAM ha sido modificada por una operación mínimamente registrada. Las operaciones mínimamente registradas son aquellas que almacenan la cantidad mínima de información para recuperar la información pero no la suficiente como para poder recuperarla en un momento dado en caso de error. Las operaciones mínimamente registradas son:
o Comando bcp o Operación BULK INSERT o Operación INSERT... SELECT
Extensiones
Son conjuntos de 8 páginas. Si las páginas pertenecen a un único objeto se llaman uniformes y pertenecen a varios mixtas.
Las extensiones mixtas se utilizan cuando se crea un objeto o índice y tiene menos de 8 páginas, cuando ocupan más se pasan a extensiones uniformes.
La forma en la que SQL Server asigna extensiones es la siguiente:
Las páginas GAM tienen un bit por cada extensión del intervalo que cubren. Si el bit tiene valor 1 la extensión está disponible, si el bit es 0 es que está asignada
Las páginas SGAM tienen un bit por cada extensión del intervalo que cubren. Si el bit tiene valor 1, la extensión se está utilizando como extensión mixta y tiene una página disponible al menos. Si el bit es 0, la extensión no se utiliza como extensión mixta o se trata de una extensión mixta cuyas páginas están todas en uso
A la hora de gestionar el espacio libre se trabaja con las páginas PFS. Cada página ocupa un byte que indica si la página está asignada y si lo está el porcentaje de llenado.
Particiones
Las tablas como tal no son objetos físicos, entendiendo como físicos que se plasman en un fichero o varios dependiendo del filegroup al que pertenezcan. Los objetos físicos son las particiones.
Las particiones son contenedores que agrupan registros de una tabla de acuerdo a un criterio. Una tabla puede tener 1 partición o “N”. Todas las tablas tienen al menos 1 partición. En realidad todas la tablas están particionadas, pero cuando en la industria se habla de tablas particionadas nos solemos referir a tablas que tienen más de una. En el futuro cuando mencionemos a tablas particionadas nos referiremos a este hecho (tablas con más de una partición).
solidq.com
10
Las tablas se particionan para tener menos lecturas y optimizar procesos de mantenimiento. Imaginemos que tenemos una tabla de facturas que contiene las facturas de todos los años comenzando en 2010 y terminando en 2015. Cada año se crean 10.000.000 de registros. Lo normal es que las facturas que más se consulten sean las mas recientes. Para limitar el número de lecturas en las consultas, modificaciones o borrados particionamos de acuerdo a la fecha de la factura. En este caso tendremos una unica tabla divida en 5 “trozos” cada uno con 10.000.000 de registros en lugar de un “trozo” con 50.000.000. Si informamos en nuestras operaciones la fecha de la factura se beneficiarán de ella, ej:
Si consultamos solo las facturas del 2015 como mucho se tendrá que leer 10.000.000 de registros ya que como las particiones están organizadas por año discriminará todas las particiones anteriores. De no ser así tendría que buscar en los 50.000.000 de registros esas facturas
Imaginemos que hay que reconstruir el índice cluster. Si disponemos de una única partición tendremos que recontruir 50.000.000 de registros. Si tenemos 5 y suponemos que los datos que cambian son los del año actual o como mucho del actual y del anterior, tendremos que trabajar con 2 particiones de 10.000.000 que incluso se podrán reconstruir en paralelo
Además si cada partición está en un disco distinto se beneficiara de la lectura/escritura en paralelo.
Particionamiento Físico
Este particionamiento que hemos comentando en la explicación anterior se le conoce como particionamiento físico. En SQL Server requiere versión Enterprise o si lo quieres probar Developer.
El particionamiento físico se base en una jerarquía de funcionalidades que es:
1. Esquema de partición 1.1. Función de particionamiento
Un esquema de partición es el objeto que vincula las particiones a los filegroups3. El motivo de esta asociación con los filegroups es son los contenedores de los ficheros. Además si los ficheros están distribuidos en multiples discos físicos el rendimiento será mucho mayor.
La función de particionamiento es la que establece los rangos de valores que irán a cada filegroup. Cuando se inserte, modifique o borre cualquier dato se encargará de ubicarlo donde corresponda. La función de particionamiento tiene una columna de particionamiento que es la que toma de referencia para clasificar.
3 El concepto de filegroup se explica extensamente en el ebook SQL Server para Administradores Accidentales.
solidq.com
11
El siguiente gráfico muestra un esquema de la estructura de particionado:
A continuación vamos a crear una bbdd con 4 Filegroups y cada filegroup con un fichero, script 1_create_bbdd_particion.sql.
Ahora vamos a a mostrar como crear dos tablas particionadas con dos ejemplos. Para el primer ejemplo:
1. Borramos las objetos si existieran, en este caso: 1.1. La tabla “tabla_particionada” 1.2. El esquema “esquema_particionado” 1.3. La función “funcion_particionado”
solidq.com
12
2. Creamos los objetos:
2.1. La función de partición consta:
2.1.1. Del nombre (funcion_particionado) 2.1.2. Tipo de la columna de particion (Bigint) 2.1.3. Tipo de rango (LEFT/RIGHT). La diferencia entre LEFT/RIGHT en la función de
particionado es donde dejamos el valor límite de cada intervalo, si ponemos LEFT lo dejamos a la izquierda y RIGHT a la derecha
2.1.4. Intervalos de las particiones (1,50,100,200). El siguiente gráfico ilustra el funcionamiento:
solidq.com
13
2.2. El esquema de partición consta: 2.2.1. Del nombre (esquema_particionado) 2.2.2. Nombre de la función a la que está vinculado (funcion_particionado) 2.2.3. Filegroups a los que está vinculado cada intevalo. Siempre hay un filegroup
más que valores de rango. Siguiendo el gráfico anterior, 4 valores de particionamiento, definen 5 intervalos, por eso son necesarios 5 filegroups, porque cada intervalo se guarda en un filegroup
2.3. La tabla particionada consta: 2.3.1. Del nombre (tabla_particionada) 2.3.2. Columnas de la tabla 2.3.3. Esquema del esquema de particionado (esquema_particionado) y columna de
la tabla por la que particionaremos (id). Cuando no particionamos este dato es un filegroup y si no lo especificamos el filegroup por defecto
2.4. El índice cluster consta: 2.4.1. Del nombre (idx_cl) 2.4.2. Columnas del índice 2.4.3. Esquema del esquema de particionado (esquema_particionado) y columna de
la tabla por la que particionaremos (id). Cuando no particionamos este dato es un filegroup y si no lo especificamos el filegroup por defecto
3. Ahora insertaremos valores:
solidq.com
14
4. A continuación comprobamos que de acuerdo a la configuración de la función de la funcion coindice con el comportamiento indicado 4.1. La particion 1:
4.2. La partición 2:
4.3. La partición 3:
4.4. La partición 4:
4.5. La partición 5:
solidq.com
15
La segunda demostración es igual pero en lugar creando una función con los mismos intervalos con la opción RIGHT. Tanto a la tabla, al esquema y a la función se le ha añadido un 2 como sufijo.
1. Insertamos los datos (son los mismos que los de la demo anterior).
2. A continuación comprobamos que de acuerdo a la configuración de la función de la funcion coindice con el comportamiento indicado 2.1. La particion 1:
solidq.com
16
2.2. La partición 2:
2.3. La partición 3:
2.4. La partición 4:
2.5. La partición 5:
solidq.com
17
Indices Alineados Y No Alineados Aunque los índices los veremos más adelante vamos a anticipar una forma de clasificación de los índices. Un índice se denomina alineado cuando está definido de acuerdo al esquema de partición y usa la columna de partición de la tabla. Ahora vamos a crear un índice alineado.
Si nos fijamos estamos utilizando la columna id que es la columna sobre la que está particionada la tabla. Ahora vamos a crear un índice no alineado:
La diferencia estriba en que NO utilizamos la columna de partición ni lo almacenamos en el esquema de particionado, por lo que guardará este índice en el filegroup por defecto que en nuestro caso es FG1.
VentajasyDesventajasDeLosIndicesAlineadosYNoAlineadosAlineados Ventajas:
Optimización y simplificación de tareas de mantenimiento. Podemos reconstruir las particiones más usadas y en paralelo
Si se informa la columna de partición en operaciones puede llegar a evitar leerse la tabla entera, pensemos que se suele modificar y consultar los datos más recientes.
Las particiones están distribuidas en varios filegroups
Permiten operaciones de administración como switch (intercambiar particiones entre tablas) o merge (mezclar intervalos)
Alineados Desventajas:
Los índices ocupan más ya que requieren siempre la columna de particionamiento, si hay millones de registros el espacio ocupado puede ser grande
solidq.com
18
En los no alineados es al revés.
No Alineados Ventajas:
Los índices ocupan menoes espacio ya que no requieren la columna de particionamiento
No Alineados Desventajas:
Solo tiene una única particion donde no es posible discriminar lo más reciente de lo más antiguo por lo que en el mantenimiento tendrá que trabajar con todos los registros del índice en lugar de hacerlo con los “trozos” que más cambien
En las operaciones va a tener que recorrerse todos los valores por el mismo motivo que la anterior premisa
Solo almacena en un filegroup los datos
Si existe un índice no alineado en una tabla particionada aunque el resto de los índices lo estén no permitirán operaciones de administración
Hemos utilizado el script “3_IndicesAlineados_IndicesNoAlineados.sql”.
Particionamiento Lógico
Existe otra manera de manera de realizar el particionamiento en SQL Server sin necesidad de tener edición Enterprise. La manera es hacerla lógicamente. Conceptualmente se basa en dividir una tabla en “n” y unirlas por una vista.
solidq.com
19
Como se puede observar es muy similar al particionamiento físico:
Las tablas simulan las particiones
La vista simula la tabla
El particionamiento lo estamos haciendo conceptualmente al partir por año, en cada tabla solo se insertan las facturas de ese año
Como en todas las cosas existen ventajas y desventajas respecto al particionado físico nativo de SQL Server.
Ventajas:
Cada tabla tiene una partición única por lo que no existe el concepto de alineamiento o no alineamiento de los índices
Aunque no es muy lógico cada tabla podría tener algun campo más o menos que el resto siempre que en la vista se unan por los campos comunes
Podemos poner a cada tabla los índices que deseemos aunque el resto de tablas de la vista no tengan los mismos, recordemos que una tabla particionada físicamente, todas las particiones tienen los mismos índices
Podemos reconstruir unas tablas si y otras no, según nuestro criterio
Desventajas:
Tenemos que administrar más de una tabla
La programación se nos complica. A la hora de insertar/modificar/borrar tenemos que hacerlo su tabla correspondiente teniendo que saber el programa donde hacerlo o utilizar un trigger en la vista instead of. En una tabla particionada físicamente nos despreocupamos porque la función de partición ya se encarga de ello
A nivel de rendimiento podría llegar a ser el mismo que la tabla particionada físicamente si utilizamos los mismos filegroups para almacenar los mismos rangos de datos.
Para esta demostración vamos a utilizar el script “4_particionado_logico.sql”. En este caso en lugar de partir por un campo númerico vamos a realizar particionamiento por un campo fecha y varias tablas facturas que es un ejemplo bastante común.
1. Lo primero que vamos a hacer es crear una secuencia y un procedimiento almacenado para cargar nuestras tablas aleatoriamiente. Una secuencia “seq” es un objeto que nos va a proporcionar números secuenciales y nos va a servir simular que tenemos usuarios y que han ido introduciendo el ID de factura secuencialmente en las tablas de factura que creemos. La primera factura empieza por el valor 1.
solidq.com
20
El procedimiento rellenar_tabla permitirar generar un nº de registros aleatorio que cargaremos en cada tabla. Recibe un parámetro llamado @FIRSTDAYYEAR de tipo fecha, que es el primer día del año. El cuerpo del procedimiento hace lo siguiente: 1.1. La variable @TOP recibe un número aleatorio de la función rand() (es un número
decimal) y lo multiplica por 3.500.000 para definir el número de registros que se crearán
1.2. La variable @I será un contador para realizar un bucle desde el valor 1 hasta el valor de @TOP e insertar tantos registros como valor tenga
1.3. La variable @FX es una variable auxiliar que la utilizaremos para calcular la fecha de la factura
1.4. La tabla temporal #FACTURAS almacenará las facturas antes de insertarlas en la tabla definitiva. Tendrá la misma estructura que la tabla final.
1.5. Realizamos un bucle while desde @I = 1 al valor que tenga @TOP 1.6. Calculamos una fecha aleatoria, lo que hace la expresion es de dentro hacia fuera:
1.6.1. Genera un número entre 0 y 364. Genera un número entre 0 y 364. Para generar un valor aleatorio entre dos números hay que aplicar la expresion FLOOR(RAND()*(Límite Superior – Límite Inferior)+Límite inferior. IMPORTANTE según las reglas de precedencia de operadores en SQL Server el operador multiplicacion “*” se ejecuta antes que el “+” salvo que haya paréntesis. La siguiente expresión lo demuestra, SELECT 4*(9‐1)+1 AS PRUEBA1, 1+(9‐1)*4 AS PRUEBA2. Nuestro caso sumar 0 no impacta pero si fuera otro número habría que ponerlo
1.6.2. Se convierte a SMALLINT, si generara un error por si acaso devolvería nulo (TRY_CAST)
solidq.com
21
1.6.3. La función COALESCE evalua si es un nulo, si no lo es devuelve ese valor si lo es, lo sustituye por un 0.
1.6.4. Toda la expresión anterior forma parte de la función DATEADD, que lo que hace es sumar un número a una fecha generando otra fecha, la idea inicial para generar una fecha de factura aleatoria dentro del año es partiendo del primer día del año generar un número entre 0 y 364 y sumarselo, de esta manera nos aseguramos que siempre estará dentro de ese año.
1.7. Una vea generada la fecha insertamos la factura, que estará compuesta de: 1.7.1. El siguiente valor de la secuencia como ID 1.7.2. La fecha de la factura con que hemos calculado en la variable @FX 1.7.3. Un valor aleatorio entre 0 y 10.000.000 millones para el Importe de la factura 1.7.4. Un valor aleatorio entre 0 y 10 para el Id del cliente1
1.8. Incrementamos en 1 el contador @I 1.9. Hacemos la SELECT la tabla #FACTURAS que devolverá todas las facturas que hemos
generado 1.10. Borramos la tabla #FACTURAS
2. Creamos las tablas de facturas del 2010 al 2014 y las rellenamos. Importante hay que fijarse que el índice cluster (ya profundizaremos más adelante que es ese concepto) es distinto a la Primary Key. La explicación se encuentra en el punto Indices Cluster y Primary Key.
3. Creamos la vista
solidq.com
22
Rendimiento
Ahora vamos a demostrar que tanto el particionado lógico como el físico tienen efecto en el rendimiento y si lo utilizamos bien puede discriminar muchos registros.
Vamos a crear la misma demostración con facturas para particionado físico y compararemos después.
1. Creamos una función de partición pero esta vez con los límites de cada año. En la demo del particionado lógico todas las tablas se crearon en el filegroup por defecto FG1, ahora vamos a hacer lo mismo para demostrar que el comportamiento es el mismo. Creamos tambien la tabla FACT con la misma estructura que las tablas factura del particionamiento lógico
solidq.com
23
2. Cargamos la tabla FACT con los datos de las tablas del particionamiento lógico
Ahora vamos a comparar el plan de ejecución de una consulta contra la vista V_FACTURAS para ver las facturas del año 2014 y de la misma consulta pero de la tabla FACT. Ver script “6_Rendimiento_Particionado.sql”.
solidq.com
24
En esta consulta sobre la vista como consultamos los datos del 2014 y en cada tabla los datos de facturas están ordenados por la fecha vemos que ignora las tablas que no contienen datos del 2014 que es precisamente el comportamiento que deseamos, esto es, que dado un gran conjunto de datos al separarlo en trozos más pequeños en base a un criterio este nos permita discriminar un gran conjunto de registros para mejorar el rendimiento.
Respecto a la tabla particionada físicamente pasa lo mismo, el plan de ejecución es más sencillo (solo hay una tabla) pero trabaja solo con la partición del 2014 por eso el número final de registros en ambas queries es el mismo 2.360.180.
Ahora vamos a realizar otra demostración para indicar lo que no se debe de hacer. Indicamos no se debe ya que no vamos a explotar la ventaja de que las tablas estén particionadas. Vamos a consultar las facturas del cliente 1. Al solo darle este dato, y uno usar una fecha o rango de fechas no se puede aprovechar del particionamiento y tendrá que recorrerse todas las tablas (particionamiento lógico) o todas las particiones (particionamiento físico).
solidq.com
25
Como no es habitual tener una gran cantidad de datos y solicitar todos los datos historicos de un cliente (como mucho los más recientes y se podría utilizar fecha) aconsejamos que siempre se intente utilizar la columna de particion y el filtro adicional (cliente, proveedor, tienda etc).
solidq.com
26
Indices
Nos vamos a centrar en los índices orientados a registro de tablas tradicionales puesto que los otros índices que existen (los orientados a columna (columnares)) o los de tablas en memoria que se consideran avanzados.
Un índice es una estructura ordenada que acelera las búsquedas de tablas y vistas. Como estructura ordenada cualquier inserción, modificación o borrado requerirá obligatoriamente su actualización para mantener el orden. Esto implica que ralentiza estas operaciones de escritura.
Un índice no es útil cuando ninguna búsqueda lo utiliza ya sea realizada por el usuario o del sistema4 y es actualizado por operaciones de escritura ya que en este caso estamos desperdiciando recursos y espacio (los índices son datos y se guardan en páginas) para no hacer nada con ellos.
La dmv5 sys.dm_db_index_usage_stats devuelve información acerca de que índices se están utilizando y como. El como se refiere a que tipo de búsquedas se están haciendo sobre él. Los Existen tres tipos de búsquedas en SQL Server en función de la operación que se está realizando para recuperar los valores:
Lookups. Ver índices incluidos
Scans o recorridos. Un scan consiste en recorrerse los nodos hoja de un índice de izquierda a derecha o viceversa (todos o parte). El que empiece a recorrerlo por la izquierda o la derecha depende si le hemos indicado si queremos orden ascendente o descendente. Ej “select * from numeros where num >=117 and num<777”
Seeks. Los seeks son búsquedas de valores concretos recorriendo el árbol del índice de arriba hacia abajo empezando por los nodos superiores. Ej select * from numeros where num=427”. Utiliza esta técnica porque realiza menos lecturas que recorriendo el nivel hoja de izquierda a derecha
4 SQL Server puede utilizar cualquier índice existente si así lo considera
5 Las dmvs (Data Management Views) son vistas de SQL Server que nos devuelven información sobre su funcionamiento, objetos o eventos
solidq.com
27
Una búsqueda vertical en el índice SQL Server lo representa con este icono:
Una búsqueda horizontal en el nivel hoja del íncide SQL Server lo representa con este icono:
Tras este inciso sobre los tipos de búsqueda que se pueden realizar en SQL Server volvemos al tema que nos ocupaba que era el grado de uso de los índices. Ante un índice que no utiliza se pueden hacer dos cosas:
Deshabilitarlo. Esta operación hace que cuando se produzca una inserción, modificación o borrado el índice no se actualice pero tampo se podrá utilizar en consultas. Esta operación suele venir bien si se hacen cargas masivas
Borrarlo. Evitamos actualizarlo y que ocupe espacio en disco
El hecho de que los índices añadan retardo a las operaciones de escritura no quiere decir que sean inutiles pero tampoco llegar al extremo de que todos los campos tengan índices. Este es un caso que aunque parezca extremo en alguna empresa lo hemos llegado a ver. Por regla general sucede lo contrario, las bases de datos están poco indexadas.
solidq.com
28
En cuanto a la estructura los índices tienen estructura de B‐Tree o árbol balanceado lo cual permite ordenar la estructura y realizar scans y seeks.
Cluster
Un índice cluster o agrupado es el índice por el cual se ordenan los campos de una tabla. Solo puede haber un índice cluster por tabla. El índice cluster es la tabla ya que contiene todas las columnas. Todas las tablas deberían tener índice cluster, en caso de no tenerlo a la tabla se le denomina “Heap” o “montón”.
Imaginemos que tenemos una tabla compuesta de 4 campos:
Id INT índice cluster
Nombre Varchar(100)
Población Varchar(100)
Fx Nac Date (fecha)
La estructura del índice cluster es la siguiente:
Existen tres tipos de nodos en los índices:
Nodo Raíz, es el primer nodo del árbol, su contenido está formado por valores clave y los punteros a páginas intermedias
Nodos Intermedios, puede haber varios niveles en función de la cantidad de datos del índices. Como el nodo raíz tiene valores clave y punteros a páginas intermedias o nodos hoja
Nodos Hoja, contienen los datos del índice, en el caso de los índices cluster contienen además del valor clave el resto de columnas que tiene la tabla
Se pueden realizar tanto búsquedas verticales (seeks) u horizontales (scans).
solidq.com
29
Indices Cluster y Primary Key
Es conveniente advertir de la diferencia entre índice cluster y primary key ya que a menudo son conceptos que se interpretan como sinónimos y realmente no es así.
Una primary key es una constraint es decir una regla que garantiza que todos valores son únicos y no nulos.
Un índice cluster es un elemento de rendimiento puede tener duplicados y nulos.
El problema es que en un porcentaje altísimo de los casos cuando se diseña la tabla ambos coinciden y por eso se interpretan como tal. No obstante y como hemos demostrado en la tabla particionada una tabla puede tener pk e índice cluster y no necesariamente en el mismo campo.
Vamos a ver un ejemplo (script “7_idx_cluster_vs_pk.sql”):
En esta imagen se puede ver como un índice cluster admite valores duplicados y nulos.
En la siguiente imagen creamos una tabla con primary key. Tras insertar un “2”, intentamos insertar otro “2” y nos da error.
solidq.com
30
Repetimos la prueba pero con valor “null”.
Como comentamos la constraint primary key no permite los valores nulos.
Rendimiento
La existencia o no de un índice cluster impacta mucho en el rendimiento:
En las consultas, modificacion y borrados si no puede utilizar otro índice más apropiado utilizará el cluster. En el caso de los heaps, se recorrera la tabla entera puesto que como los datos no están ordenados el valor que buscamos podría ser el último. Recordemos que para borrar o modificar primero hay que llegar al dato, de ahí la “pseudoconsulta”. Precisamente la forma más rápida de acceder a un registro para borrarlo o modificarlo es a través del índice cluster
Para las inserciones también aumenta el rendimiento puesto que como el índice cluster es una estructura ordenada y tiene que mantener ese orden tan solo tiene que buscar el lugar que le corresponde. En el caso de los las tablas sin índice cluster tiene que recorrerse las páginas con espacio libre para ver si puede alojar el nuevo registro ahí. Si el heap tiene muchas inserciones concurrentes este efecto pernicioso se amplificará y tardaran mucho más
Vamos a demostrar este hecho. En la siguiente demostración vamos a crear una tabla “heap” a partir de la tabla “FACT”
solidq.com
31
En el gráfico que se presenta a continuación se aprecia que “COPY_FACT” es un heap ya que no tiene índice cluster. La tabla FACT tiene índice cluster.
Ambas tienen los mismos registros, vamos a realizar una consulta para que nos diga el número de facturas que hubo el 01/01/2012.
Antes de pasar a la parte de resultados queremos comentar una serie de instrucciones que utilizamos para nuestras pruebas:
SET STATISTICS IO ON/OFF. Esta cláusula solo afecta a aquellas instrucciones que hacen lecturas. Nos va a decir:
o El número de recorridos de tabla o “Scan Count”(scans) o Lecturas lógicas o “logical reads”, son paginas leídas de la caché de datos de la
memoria o Lecturas físicas o “physical reads”, son páginas leídas del disco y son mucho
más lentas que las de memoria o Lecturas adelantadas o “read‐aheads reads”, número de páginas llevadas a la
caché de datos por la consulta o Lecturas lógicas de LOB o “lob logical reads”, son el número de páginas leidas
de los tipos text, ntext, image, varchar(max), nvarchar(max), varbinary(max) de la cache de datos de la memoria
o Lecturas físicas de LOB o “lob physical reads”, son el número de páginas leidas de los tipos text, ntext, image, varchar(max), nvarchar(max), varbinary(max) del disco
o Lecturas anticipadas de LOB o “lob read‐ahead reads” on el número de páginas leidas de los tipos text, ntext, image, varchar(max), nvarchar(max), varbinary(max) llevadas a la caché de datos por la consulta
SET STATISTICS TIME ON/OFF. Muestra el número de milisegundos necesarios para analizar, compilar y ejecutar cada instrucción
solidq.com
32
CHECKPOINT. Escribe todas la páginas desfasadas en el disco
DBCC DROPCLEANBUFFERS. Borra de la memoria las páginas desfasadas de la memoria. Es una forma de probar cuando tarda una consulta sin que haya nada en memoria para que no se aproveche de ejecuciones previas. Esta instrucción no se debe utilizar en un entorno productivo porque limpiaría la memoria y afectaría gravemente al rendimiento
DBCC FREEPROCCACHE. Limpia la caché de procedimientos. Cada sentencia que se ejecuta en SQL Server tiene un plan de ejecución que se almancena en la caché de procedimientos. Limpiando la caché lo que conseguimos es que tenga que generar un plan nuevo ya que si cogiera el existente se estaría aprovechando y tardaría menos
Lo que queremos hacer con estas instrucciones es simular que reiniciamos SQL Server. Cuando lo reiniciamos en la memoria que tiene asignada no hay ningún dato ni nada que pueda aprovechar nuestra consulta para que vaya más rápido. De esta manera sabemos cuando tarda con la ejecución más lenta.
Dicho esto vamos a examinar los resultados.
solidq.com
33
En cuestión de lecturas la consulta sobre la tabla con índice cluster (FACT) ha realizado:
1 scan frente a 9 de la consulta sobre la tabla sin índice cluster
14 lecturas lógicas frente a 21084
2 lecturas físicas frente a 0
4 lecturas adelantadas frente a 21091. Esto quiere decir que se ha llevado practimente todas las páginas de datos a la memoria
Se puede apreciar que el consumo de recursos es mucho mayor y esto también se va a trasladar también a la duración de la consulta como veremos a continuación.
En la consulta sobre la tabla con índice cluster SQL Server no ha realizado consumo de CPU por eso su duración en milisegundos es 0. El tiempo que ha transcurrido en ejecutar toda la consulta es 41.
En cambio en la consulta sobre la tabla sin índice cluster el tiempo de uso de la CPU ha sido de 469 milisegundos (casi medio segundo) y la duración total de 1,3 segundos.
El consumo de CPU y duración de ambas consultas devolviendo los mismos datos es ampliamente superior en el caso de no utilizar índice cluster.
solidq.com
34
Estas queries están en el script “8_Rendimiento_tb_idx_cluster_vs_heaps.sql”
Criterios Para Poner Indices Cluster
Cuando no existe un índice apropiado en una consulta o son muchos los valores a cruzar o buscar SQL Server utiliza los índices cluster. Los índices cluster pueden estar formados por una o varias columnas. Es preferible que el número de columnas sea el mínimo posible y ocupe también lo mínimo, ya que de esa manera en una página de datos cabrán más registros, necesitaremos menos paginas y al leer menos páginas las operaciones tardará menos. Los índices cluster son adecuados para:
Consultas muy habituales (pero hay que utilizar el campo/s de este índice) ya que es la forma más rápida de acceder al registro
Rangos de datos, es decir: o Operadores >, <,>=, <= y BETWEEN o Cuando se devuelven muchos datos o muchas columnas. Los índices cluster
contienen todos los datos organizados en columnas. Si necesitamos la muchas columnas de una tabla o si utilizados el “*” lo más probable es que SQL Server utilice el índice cluster salvo que el número de registros sea muy bajo y le merezca la pena utilizar un índice no cluster realizando “lookups”. Ver índices incluidos
o En clausulas JOIN. Normalmente suele pasar que un índice cluster es tambien primary key. Cuando un campo es primary key, es posible que sea clave foránea en otra tabla. Un ejemplo de esto podría ser en una tabla de facturas la columna ID_FACTURA. Si tenemos una tabla de líneas de factura obviamente cada línea debe pertenecer a una factura. Si cruzamos con un join la tabla de facturas y de líneas de factura se debería cruzar por el campo ID_FACTURA
o Clausular ORDER BY si utilizamos el campo/s clave del índice como criterio de ordenación
solidq.com
35
No Cluster
Un índice no cluster o no agrupado contiene una porción de los datos de una tabla. Se pueden poner tanto sobre tablas con índices cluster como sobre heaps.
Su estructura sería la siguiente en el caso de la tabla de ejemplo anterior si crearamos un índice por el campo nombre:
Como se puede apreciar los datos estar ordenados por la columna Nombre. En los nodos hoja que es donde se encuentra la lista completa ordenada está el valor del índice cluster que es el que le permite recuperar datos del resto de columnas de la tabla en el caso de que se utilizara el índice y se necesitara algún campo más que no está.
La imagen anterior es para un índice que esta en una tabla con índice cluster, la siguiente es para una tabla sin índice cluster o “heap”. En este caso en los nodos hoja en lugar de el/los campos del índice cluster se encuentra un campo que ha creado SQL Server para identificar unívocamente cada registro que se llama RID. Este campo RID no lo podemos utilizar en nuestras queries.
solidq.com
36
Esta sería la estructura en un heap:
A continuación vamos a demostrar como la inclusión de índices no cluster en nuestras queries mejora el rendimiento ostensiblemente. De hecho solo puede haber un índice cluster en una tabla pero no cluster tantos como necesitemos, son los más habituales.
Queremos obtener todas las facturas del cliente 7. Las intrucciones de anteriores a la select están explicadas en el primer ejemplo del índice cluster por lo nos centraremos solo en lo nuevo.
El resutado en terminos de lecturas y tiempo son las siguientes:
solidq.com
37
A falta de un índice mejor utiliza el índice cluster que se creó sobre el campo FX_FACT:
Ahora vamos a crear un índice no cluster sobre el campo ID_CLI:
Repetimos la query (tras ejecutarse se borra el índice):
Como vemos la consulta utiliza el nuevo índice:
Los resultados son los siguientes:
Comparando con los de la query sin el índice las lecturas se han reducido enormemente y la query ha pasado de 3,5 segundos a 2,3.
solidq.com
38
Indices Con Múltiples Columnas Tanto los índices cluster como los no cluster pueden tener más de una columna, vamos a consultar el importe y la fecha de las facturas del cliente 1 cuyo importe se encuentre entre 1 y 2 millones de euros.
Como en el primer caso a falta de un índice mejor utiliza el índice cluster:
En términos de rendimientos estos son los costes de la consulta, se recorre la tabla al completo:
Creamos el índice por el campo cliente e importe:
Volvemos a ejecutar la consulta y tras terminar borramos el índice:
La consulta utiliza el nuevo índice:
solidq.com
39
Los resultados a nivel de rendimiento son los siguientes:
Comparandolos con los de la consulta sin índice cluster la reducción del número de lecturas provoca que la consulta pase de 2,7 segundos a 390 milisegundos.
Incluidos
Los índices incluidos son índices muy eficientes (si contienen las columnas adecuadas) que evitan los lookups. Los lookups suceden cuando una consulta utiliza un índice no cluster y este tiene que utilizar el índice cluster para recuperar las columnas que le faltan al índice cluster. Vamos a verlo con un ejemplo:
Queremos los clientes y el importe de las facturas que están entre la 1 y la 1000. Creamos un índice en el campo ID de la factura. Tras esto ejecutamos la consulta:
solidq.com
40
En el plan de ejecución podemos ver que:
El índice que hemos creado se utiliza para fitrar entre 1 y 1000
Que se produce un lookup para recuperar del índice cluster la columna importe y el cliente. En el índice cluster están todas las columnas (es la tabla):
A nivel de rendimiento estas son las lecturas y la duración:
Creamos el índice incluido, la particularidad respecto a lo que ya hemos visto es la clausula INCLUDE.
Este índice utiliza el campo ID en el nodo raíz, nodos intermedios y hoja. Además para no tener que utilizar el índice cluster incorpora en el nodo hoja del índice el cliente y el importe.
La consulta utiliza el nuevo índice:
Estos son los resultados a nivel de rendimiento con el nuevo índice:
Al reducirse el número de lecturas hemos pasado de 882 milisegundos a 124.
solidq.com
41
Filtrados
La última variedad dentro de los índices no cluster son los filtrados. Contienen una clausula where, no permiten variables, ni “OR” o “IN”.
En esta query queremos conocer el importe y la fecha de la factura del cliente 1.
Sin índices más efectivos se utiliza el índice cluster:
Las lecturas y tiempos son los siguientes:
Vamos a crear el índice filtrado (el que sea filtrado no excluye que sea también incluido):
Con el nuevo índice los lanzamos de nuevo la query:
solidq.com
42
El plan de ejecución coge el nuevo índice filtrado:
De nuevo observarmos que reduciendo las lecturas disminuye el tiempo que necesita SQL Server para ejecutar la consulta:
Respecto a la consulta sin índice filtrado hemos pasado de 3,6 segundos a 2,5.
Todos estos ejemplos se encuentran en el script “9_indices_no_cluster.sql”.
Criterios Para Poner Indices No Cluster
Los índices no cluster se deben/pueden utilizar en:
En JOINS o en sentencias que tengan GROUP BY
Consultas que requieran trabajar con subconjuntos de columnas (no se debería poner “select *” precisamente para poder utilizar índices, ya que casi lo mas seguro es que en ese caso utilice el cluster si hay muchos registros)
o Hay que intentar cubrir la consulta, es decir utilizar índices que utilice todos los campos de las tabla dentro de una consulta
Evitar lookups creando índices incluidos. Importante, no conviene abusar de los índices incluidos ya que ralentizarán las escrituras
Intentar utilizar índices filtrados para reducir la lectura. Los índices filtrados son especialmente efectivos cuando se discriminan muchos registros. Imaginemos campos de estado con pocos valores, si creamos indices por algún valor de ellos ya discriminaremos un gran número de registros. Imaginemos que el campo estado de una factura tiene 5 valores distintos, la tabla tiene 25.000.000 de registros queremos hacer un informe que nos diga que consultas tenemos que enviar a los clientes. Evidentemente este estado solo afecta a las nuevas facturas que serán un número bajo (imaginemos 100.000). Un indice en una consulta que filtrara por este estado estaría excluyendo 24.900.000 registros
solidq.com
43
Conclusión
Queremos recordar algo, los índices se diseñan para las consultas, no las consultas para los índices. Esto quiere cuando realizamos una aplicación tenemos que pensar que consultas podrían realizar los usuarios y ponerles los índices adecuados. Una vez que la aplicación está en producción debemos revisar que índices se utilizan y cuales no para borrarlos o deshabilitar (ver usos de índices). Otra labor además de esta a realizar es ver que operaciones usan más los usuarios para optimizarlas, sobre todo las consultas si son dinámicas y vienen de pantallas con multitud de filtros de búsqueda.
Poner índices ya sean cluster o no cluster es algo sencillo y que nos puede ayudar mucho a mejorar el rendimiento en las operaciones que hacemos. Es el principal problema que nos encontramos en las empresas que visitamos. Pensemos en lo siguiente:
Normalmente el tamaño de la memoria es mucho menor que el de disco, esto quiere decir que todos los datos guardados en el disco no van a caber en la memoria. Si no caben en la memoria SQL Server va a tener que ir a disco que es mucho más lento Si utilizamos mucho el disco la duración de sus lecturas o escrituras tardarán más y degradaran mucho en rendimiento de las operaciones, es decir las, selects, inserts, updates y deletes tardaran más
Si no tenemos índices no cluster más optimos utilizará el índice cluster o de no existir recorrerá toda la tabla
solidq.com
44
Vistas Están compuestas por una consulta y su contenido está almacenado en las tablas a las que hace referencia en el la clausula from. La excepcion son las vistas indexadas que las veremos más adelante. Las vistas nos pueden servir para:
Gestionar la seguridad reduciendo el número de permisos porque una vista puede “contener” varias tablas. Dando permiso a la vista evitamos o podemos denegar permisos sobre las tablas subyacentes
Simplificar el modelo de datos. Ej supongamos que tenemos la vista ventas, esta vista contiene información de las tablas, productos, clientes, facturas y proveedores. Al unificar esta información consigue la simplificación del modelo de datos
Imaginemos que tenemos una aplicación en produccion muy grande. Esta aplicacion tiene muchos módulos y muy antiguos que funcionan bien pero tenemos que desarrollar nuevas funcionalidades que implican modificiaciones en el modelo de datos existente. Imaginemos que tenemos una tabla “X” que vamos a dividir para esas nuevas funcionalidades en otras tres. Para evitar tener que retocar esos módulos antiguos podemos crear una vista “X”que relacione las tres nuevas tablas. Esto implica que la tabla vieja la tendremos que renombrar o borrar pero la aplicación ni se enterará ya que la vista tiene los mismos campos que la tabla original. Ver “triggers Instead of”.
Debemos evitar el anidar vistas. Supongamos que tenemos una vista A, a esa vista A se le consulta desde una vista B y a su vez a B sobre una vista C pero tan solo para devolver un campo. SQL Server tendrá que devolver. Lo podemos observar con un ejemplo:
La vista V_C solo usa la columna “name” de la vista V_A. De la vista V_B no utiliza nada.
Realizamos una query sobre la vista V_C y obtenemos las lecturas y la duración.
solidq.com
45
Realizamos una consulta que hace lo mismo que V_C (es la misma tabla) pero hace un inner join directo sobre la tabla de la vista V_A.
Comparamos resultados empezando sobre la vista V_C:
solidq.com
46
El rectángulo en rojo es de la tabla que usa la vista V_B. El resto de filas identicas en ambas consultas. Como en los ejemplos de índices la reducción de lecturas reduce el tiempo de ejecución, el hecho de pasar por una tabla menos hace que la consulta pase de 202 milisegundos a 68.
Ver script “10_vistas_anidadas.sql”.
Las vistas no aceptan parámetros de entrada, solo lo permiten las funciones y procedimientos.
Tampoco puede existir la clausula ORDER BY en las vistas excepto si se le pone TOP 100 PERCENT.
Vistas Indexadas
Como el nombre señala en estas vistas se pueden crear índices. Las vistas indexadas son utiles para:
Realizar agregaciones en tablas con muchos datos (SUM,AVG etc)
No lo son para:
Consultas sin agregaciones
Tablas con muchas escrituras (insert/update/delete)
Los requerimientos para crear un índice en una vista son muchos, pero los principales son estos:
Opciones SET establecidas a ON en la/s tabla/s de la vista
Clausula SCHEMABINDING obligatoria
El índice cluster debe crearse sobre un una combinación de campos que genere datos únicos
Referencia: https://technet.microsoft.com/es‐es/library/ms191432(v=sql.120).aspx
solidq.com
47
Vamos a demostrar esto utilizando el script “11_vista_indexada.sql”. Lo primero que hacemos es crear una nueva tabla, tiene la misma estructura que la tabla Fact pero en este caso le indicamos que ningún campo sea nulo. Esto es porque para crear la vista índexada necesita un índice unico y cluster. Tras crear la tabla hacemos un insert/select para cargarla con los datos de la tabla FACT.
Ahora lanzamos una consulta que nos diga el importe y el número de facturas por día y cliente.
Esta consulta genera el siguiente plan de ejecucion:
Los costes a nivel de lecturas y duración son los siguientes:
solidq.com
48
El siguiente paso es crear la vista y el índice de la vista:
La vista tiene la clausula WITH SCHEMABINDING que enlaza la vista a la tabla T1 para evitar que se hagan cambios en su estructura. La query es la misma que hemos lanzado antes.
En la creación del índice hay que indicarle que el índice tiene que ser UNIQUE (contendrá valores unicos) y CLUSTERED. La unicidad la proporcionarán los campos ID_CLI y FX_FACT ya que lo que queremos es el importe por día y cliente, por lo que en esa combinación no puede haber duplicados.
Volvemos a lanzar la query pero esta vez utilizando la vista:
En el plan de ejecución se puede apreciar que el índice de la vista se utiliza:
Los resultados son los siguientes a nivel de costes:
Comparandolos con los de la query sin vista son muchisimo menores y ello incide en la duración pasando de 1,5 segundos a 166 milisegundos:
solidq.com
49
Otra particularidad que queremos mostrar es que una vez creado el índice y estar la vista vinculada a la tabla T1 SQL Server la puede aprovechar para otras consultas de agregado que considere similares. En este caso vamos a lanzar una query sobre la tabla T1 pero le vamos a pedir el importe de las facturas por día:
Como se puede apreciar utiliza el índice de la vista.
Conclusión
Las vistas no contienen datos, contienen consultas sobre tablas, excepto las índexadas. No debemos usar las vistas para anidarlas ya que realizaremos lecturas de mas y desperdiciaremos recursos. Las vistas indexadas nos pueden ayudar para mejorar el rendimiento de consultas con agregaciones pero exigen cumplir una serie de reglas muy estrictas que de no cumplirse no nos dejaran crear el índice sobre la vista.
Procedimientos Los procedimientos almacenados son objetos que agrupan instrucciones TSQL y/o CLR6.
La principal ventaja es que permiten modularizar y el código e independizarlo ya que funcionan como una caja negra. Pueden opcionalmente:
o Recibir parámetros de entrada o de entrada/salida o Devolver resultados de selects o Retornar un valor
Seguridad. Los procedimientos almacenados pueden simplificar la seguridad ya que es posible dar permisos al procedimiento y no a los objetos que dentro de el se hacen referencia e impersonalizar su ejecución
Rendimiento y tráfico de red reducido. Los procedimientos almacenados presentan un mejor rendimiento que las queries “ad hoc” y reducen el tráfico de red. Para entender mejor este concepto vamos a explicar que son las queries “ad hoc”
6 CLR significa Common Runtime Language. Es código que se apoya y aprovecha funcionalidades de .NET Framework
solidq.com
50
Queries Ad Hoc
Las queries “ad hoc” pueden ser consultas que se encuentran almacenadas en un programa y que se invocan dentro de el o cadenas de texto que se ejecutan que forman una sentencia TSQL y se ejecutan con EXEC o sp_execute.
Este tipo de programación no es la manera más adecuada de lanzar peticiones a SQL Server ya que ante una query “ad hoc” SQL Server tiene que:
1. Verificar la sintaxis para comprobar que se utilizan clausulas correctas, que los objetos existen, que existe un orden correcto etc
2. Buscar el mejor plan de ejecución. Algunas veces este plan no es el mejor posible. El motivo es simple, cuando una sentencia es muy compleja (imaginemos una select que es muy grande y cruza muchas tablas) existen muchos planes de ejecución posibles ya que existen muchas opciones. Nosotros además no estaríamos dispuestos a que SQL Server tardara 5 minutos para encontrar el mejor plan de ejecución posible, por tanto trata de encontrar en un tiempo razonable el mejor plan posible
3. Compilar el plan de ejecución 4. Ejecutar el plan 5. Devolver resultados a la aplicación cliente
En un procedimiento almacenado estos pasos se ejecutan la primera vez7. La siguiente ejecución (supongamos que tiene parámetros de entrada) solo usaria los pasos 4 y 5 ya que reutiliza el plan de ejecución. Esta reutilización hace que se mejore el rendimiento.
Este flujo de acciones se realiza siempre que SQL Server recibe una query “ad hoc”. Estás operaciones utilizan mucha CPU por lo que si la aplicación que lanza las queries utiliza este patrón de programación es posible que vaya a tener problemas por consumo de cpu y por tanto reducir su escalabilidad.
Un ejemplo de una query ad hoc dentro de código sería el siguiente, (es un ejemplo con c#)
7 Existen excepciones como la cláusula WITH RECOMPILE y otras
solidq.com
51
Otro ejemplo de queries “ad hoc” son las que se encuentran dentro de procimientos. Estos procedimientos que contienen queries ad hoc suelen ayudar a pantallas de búsqueda dinámica, ejemplo:
solidq.com
52
Este procedimiento contiene un parámetro por cada campo de la tabla FACT. Imaginemos que esto equivale a una caja de texto o lista de una pantalla de una aplicación.
La primera sentencia se declara en una variable (@STR) que contedrá la select principal. Se añade “ WHERE 1=1” por si no se informará ningún parámetro con valor.
A continuación y por cada parámetro se examina si su valor no es nulo. Si tiene valor se concatena a la variable @STR un filtro por ese campo.
Las últimas dos sentencias muestran la select que se ejecutará y ejecuta la select que se ha creado dinámicamente devolviendo los resultados de esta.
Este ejemplo se encuentra en el script “12_procedimientos_con_queries_ad_hoc.sql”.
solidq.com
53
Ejemplos:
solidq.com
54
Tipos De procedimientos Almacenados
Existen distintos tipos de procedimientos almacenados.
Definidos por el usuario. Se pueden hacer con TSQL o CLR
Temporales
Pueden ser locales (con una “#”) o globlales (con dos “#”). Las locales solo se pueden utilizar en una sesión (un ejemplo de sesión es una pestaña de SQL Server Management Studio) y las globales en todas. No suelen ser muy utilizados. Script “13_procedimientos_temporales.sql”.
Procedimientos de Sistema. Son procedimientos proporcionados por Microsoft tanto para su gestión interna como para facilitarnos tareas
Procedimientos almacenados extendidos. Son procedimientos almacenados del sistema que proceden de DLLs normalmente programadas en lenguaje C. Microsoft recomienda no utilizarlas y en su lugar usar CLR
solidq.com
55
Creación de Procedimientos
Vamos a comentar la forma de crear un procedimiento con las opciones más usadas:
Referencia: https://msdn.microsoft.com/es‐es/library/ms187926(v=sql.120).aspx
La clausula CREATE PROC / CREATE PROCEDURE obligatorias
En el marco rojo se debe indicar el nombre (obligatorio) y el esquema si se desea ya que es opcional
@parameter son nombres de parámetros y son opcionales, en el caso de que se especifiquen se tiene que indicar con un tipo
El marco verde es para indicar el valor por defecto del parametro, también es opcional
El marco rosa es para indicar que son parametros de entrada/salida o readonly para los tvps que veremos más adelante
La cláusula AS es obligatorioa
La cláusula ENCRYPTION encripta el contenido del procedimiento
La cláusula RECOMPILE hace que cada vez que se invoque se busque un nuevo plan de ejecución (no es muy usual utilizar esto)
EXECUTE AS se utiliza para impersonar la ejecución con las siguientes opciones: CALLER | SELF | OWNER | 'user_name'
o CALLER, se ejecuta en el contexto de ejecución que del usuario que lo invoca, es la opción por defecto
o SELF, se ejecuta en el contexto de ejecución de la persona que crea el modulo o OWNER, se ejecuta en el contexto de ejecución de la persona que es
propietaria del modulo. Normalmente el usuario que crea el procedimiento y el propietario suelen ser el mismo pero podría no serlo
o user_name, se ejecuta en el contexto del usuario que se indica
solidq.com
56
Procedimientos Y Tablas Temporales
Las tablas temporales creadas dentro de un procedimiento padre son visibles y pueden ser utilizadas por los procedimientos hijo sin necesidad de tener que pasarse como parámetro. Ver script “14_procedimientos_y_anidamiento_tablas_temporales.sql”
En el procedimiento A se crea una tabla temporal que se utiliza en el procedimiento B.
Aunque simultaneamente fuera utilizado el procedimiento almacenado en paralelo múltiples veces para cada ejecución crearía una tabla temporal distinta.
solidq.com
57
Inserción En Tablas
Podemos utilizar las salidas de los procedimientos para insertar en tablas.
Ahora creamos una tabla y un procedimiento. El procedimiento realiza una select. El procedimiento lo podemos utilizar para consultar o para insertar los datos de la query en una tabla. Ver script “15_procedimientos_para_insertar_en_una_tabla.sql”.
solidq.com
58
Procedimientos Con Múltiples Consultas
Un procedimiento puede lanzar múltiples consultas cuyo resultado puede ser capturado por las aplicaciones en un DataSet con un DataTable para cada consulta.
Ver script “16_procedimientos_multiples_selects.sql”.
Los procedimientos pueden combinar múltiples operaciones de lectura (selects y escritura (inserts, updates, merges, deletes, truncate tables) etc.
solidq.com
59
Gestión de Errores
Se aconseja utilizar la cláusula BEGIN TRY / BEGIN CATCH para simplificar a gestión de errores en funciones, procedimientos y triggers. Su uso es equivalente al que de otros lenguajes de programación.
Se pueden utilizar uno o “n” de forma anidada. Se pueden lanzar errores con la cláusula “throw”. El ejemplo lo veremos en el próximo punto.
TVPS Y Merge
Las aplicaciones actuales usan muy frecuentemente grids para facilitar a los usuarios tanto la carga, la modificación y el borrado de registros. La forma tradicional de persistir esta información en la base de datos era recorriendo cada registro y en cada caso hacer insert, update o delete. Actualmente existe un método técnico que nos simplifica esa tarea que es con Tvps o “Table Values Parameters”. Básicamente consiste en meter todos los registros del grid en un objeto DataTable y pasarle esta tabla como parámetro a SQL Server para tratar todos los registros en conjunto insertándolos, modificándolos o borrándolos según nos interese.
solidq.com
60
El siguiente ejemplo ilustra cómo se puede hacer esto:
1. Creamos los la tabla clientes e insertamos dos registros
2. Creamos un tipo tabla indicando los mismo campos que la tabla clientes
3. Creamos el procedimiento almacenado
solidq.com
61
Lo que está haciendo el procedimiento es recibir un parámetro de tipo tabla TCLIENTE que es solo de LECTURA dentro del procedimiento. El siguiente paso tras abrir una transacción es realizar una operación merge donde:
o En la operación merge le tenemos que indicar donde realizaremos los cambios (tabla cliente)
o Cual es el origen de los datos (variable tipo tabla @ que le hemos pasado como parámetro
o El criterio de intersección para saber cuando un cliente de la variable tabla, existe en la tabla clientes
Tras esto comenzamos con las acciones (son opcionales elegimos las que nos interesen o todas): o Si registro del parámetro tabla tiene un id que existe en la tabla clientes, indica
que el cliente ya existe con lo cual actualizamos los campos nombre y fecha para ese cliente
o Si un registro del parámetro tabla no existe en la tabla cliente lo insertamos ya que es nuevo
o Si un registro de la tabla cliente no existe en la tabla parámetro borramos ese cliente
solidq.com
62
4. Ahora cargamos la variable tabla e invocamos al procedimiento almacenado
5. Lo que va a pasar es:
o Se actualizará el registro 1 o Borrará el registro 2 o Insertará el registro 3
Ver script “17_tvps_merge_gestion_errores.sql”.
Esta tabla que pasamos como parametro tambien la podemos utilizar para listas.
solidq.com
63
Rendimiento
Ahora que conocemos un poco más de los procedimientos almacenados vamos a comparar un proceso que insertaría con operaciones “ad hoc” que vendrían de una aplicación con el mismo proceso pero la aplicación utilizaria un procedimiento almacenado para realizar las inserciones. Ver script “18_procs_vs_achocs.sql”.
Este script utiliza cursores que veremos más adelante.
El script hace lo siguiente:
1. Crea una tabla
2. Creamos un cursor. El cursor es un bucle sobre un conjunto de registros que viene de
una select.
La instrucción DECLARE CUR crea el cursor que con esa select recorrerá 743.000 registros.
La instruccion OPEN CUR abre el cursor
La instrucción FETCH NEXT se situa en un registro y pasa los campos a cada variable de la lista
La instrucción @@FETCH_STATUS=0 recorre todos los registros hasta que se llega al final
La variable @STR forma una instrucción INSERT de la misma manera que lo haría una aplicación que enviara queries “ad hoc”
La instrucción EXEC(@STR) ejecuta el contenido TSQL que contiene una variable de cadena de texto
La instrucción FETCH NEXT se situa en el registro y pasa los campos a cada variable de la lista
La instrucción CLOSE cierra el cursor
La instrucción DEALLOCATE lo libera
solidq.com
64
La ejecución de esto tarda 1 minuto 15 segundos:
solidq.com
65
A continuación vamos a repetir la prueba pero en lugar de con sentencias “ad hoc” con procedimientos almacenados.
Primero creamos el procedimiento, es muy simple, recibe los parametros y hace el insert.
El cursor hace lo mismo pero esta vez utiliza el procedimiento para insertar:
solidq.com
66
Tras este cambio y ejecutar de nuevo el proceso hemos pasado de 1 min 15 segundos a 32 segundos:
Como se puede apreciar los procedimientos almacenados son muy útiles para ya que evitan tener que tan solo tienen que ejecutar el plan de ejecución que tienen almacenado y devolver los datos lo cual es más eficiente que:
1. Verificar la sintaxis para comprobar que se utilizan clausulas correctas, que los objetos existen, que existe un orden correcto etc
2. Buscar el mejor plan de ejecución. Algunas veces este plan no es el mejor posible. El motivo es simple, cuando una sentencia es muy compleja (imaginemos una select que es muy grande y cruza muchas tablas) existen muchos planes de ejecución posibles ya que existen muchas opciones. Nosotros además no estaríamos dispuestos a que SQL Server tardara 5 minutos para encontrar el mejor plan de ejecución posible, por tanto trata de encontrar en un tiempo razonable el mejor plan posible
3. Compilar el plan de ejecución 4. Ejecutar el plan 5. Devolver resultados a la aplicación cliente
solidq.com
67
Funciones Las funciones son objetos que encapsulan código que hace una/s determinada/s tarea/s. Son parametrizables. Vamos a comparar cada una de ellas realizando la misma funcionalidad.
Vamos a crear una tabla con dos campos de tipo entero con valores del 1 al 100. Vamos a realizar 10000 inserciones con lo cual nos aseguramos que haya duplicados.
Para un valor de la columna A tendra que devolver concatenados todos los valores de la columna B. Ej:
A B
16 7
16 9
16 47
8 93
Esto devolvería:
16 7,9,47,
8 93,
Escalares
Son las más antiguas, como su nombre indica siempre devuelven un valor. Tienen los siguientes problemas:
No son visibles en los planes de ejecución. (Distribuyen su carga por otros operadores falseandolos)
El código de la función se interpreta en cada llamada (cada fila)
Por último y lo más importante de todo: NO PUEDEN APROVECHAR PARALELISMO, lo cual indica que no pueden utilizar toda la potencia de procesamiento del servidor
solidq.com
68
Este es un ejemplo de función escalar:
Lo que hace esta función es:
1. Se declara una variable de tipo cadena 2. La inicializamos con ‘’ ya que inicialmente contiene NULL y si concatenamos algo con
nulo el resultado es NULL 3. La select lo que hace es concatenar todos los valores de la columna B quitandoles los
espacios y añadiendo una “,” tras cada uno de ellos 4. Se devuelve el resultado
Tipo Tabla
Como su nombre indica en lugar de un valor devuelven conjuntos de registros lo cual puede llegar a optimizar el rendimiento y hacer operaciones tipo join.
En línea
Son las más eficientes, devuelven el resultado de una select.
Esta función en lugar de un valor devuelve una tabla con una select directamente.
Como la expresión XML PATH(‘’) puede parecer rara vamos a explicar lo que hace con un ejemplo paso a paso:
solidq.com
69
Esta select devuelve los datos de la columna B, les quita los espacios, les concatena una “,” y al resultado lo llama columna “text()”.
Al añadir XML PATH(‘’) concatena todo en una línea automáticamente.
Como el nombre de la columna lo genera automáticamente (en este caso ha sido XML_F52EB261....) y no podemos adivinarlo realizamos otra operación adicional. El resultado de la consulta es una tabla por lo que la renombramos como “X”y a la columna que devuelve como “K”. Tras esto hacemos la select sobre la nueva tabla.
Multilínea
Son eficientes si se programan bien si no es así pueden llegar a tardar mucho más que las escalares. La diferencia con la anterior es que dentro del cuerpo de la función se pueden realizar múltiples operaciones (en la anterior tan solo una select mas o menos compleja pero una select). Ademas declaramos una tabla de salida que tenemos que rellenar.
solidq.com
70
Como puede ver la base es la select de la función anterior pero:
Añadimos una tabla @T
Insertamos sobre @T
Hacemos RETURN de @T
Es más flexible ya que podemos añadir más lógica y añadir condiciones, bucles, etc...
Rendimiento
En el siguiente ejemplo vamos a comparar el rendimiento de las tres sobre la misma funcionalidad. Para medir el rendimiento vamos utilizar SQL Server Profiler que es una herramienta que viene con SQL Server.
En los siguientes links se indica como utilizarla:
http://blogs.solidq.com/es/sql‐server/tutorial‐sql‐profiler‐2008‐i/
http://blogs.solidq.com/es/sql‐server/tutorial‐sql‐profiler‐2008‐ii‐administracion‐gestion‐de‐templates/
http://blogs.solidq.com/es/sql‐server/tutorial‐sql‐profiler‐2008‐iii‐sql‐profiler‐performance‐monitor‐replay‐traces/
solidq.com
71
Ver script “19_funciones.sql”. Vamos a lanzar 3 consultas, cada una con una función:
El resultado es el siguiente:
La función en línea consume tan solo tarda 250 milisegundos, realiza 143.000 lecturas y consume 235 milisegundos de CPU.
La función escalar tarda 9,5 segundos realiza 420.000 lecturas y 9,5 segundos de CPU.
La función multilínea mucho mas, pero es porque tal y como está realizada no tiene mucho sentido, si tienes una select que te hace la operacion no tiene sentido declarar una tabla insertar los datos de la select ahí y devolver el resultado.
La conclusión es que hay que dejar de lado las funciones escalares o por lo menos intentarlo y utilizar funciones de tabla (preferiblemente en línea).
Triggers Los triggers (desencadenadores o disparadores)son un tipo especial de procedimiento almacenado que tiene la pecualiaridad de que se ejecuta ante un evento. Un evento es una acción que sucede en SQL Server. Existen dos tipos generales de triggers:
De servidor o de base de datos. Estos triggers se ejecutan cuando sucede un evento en el servidor o en las bases de datos. Los utilizan los administradores por ejemplo para auditar logins, operaciones sobre estos, limitar accesos, auditar creación, modificación y borrado de objetos etc.
DML son los más habituales y los utilizan los programadores de TSQL. Se activan ante eventos de tablas insert, update y delete
Puesto que este libro está orientado a la programación no comentaremos los triggers de servidor o de base de datos.
Se suelen utilizar para aplicar reglas de negocio, realizar labores de integridad de datos complejas y de log. Los triggers son activados desde transacciones y pueden ser revocadas desde el mismo trigger.
solidq.com
72
Los triggers se apoyan en 2 pseudotablas que se llaman inserted y deleted. Ambas tienen la misma estructura que el objeto al que pertenecen:
La tabla inserted contiene la información de los registros insertados y de los nuevos valores en el caso de una modificación
La tabla deleted contine la información de los registro borrados y de los viejos valores en el caso de una modificación
Los triggers DML se pueden activar para inserciones, modificaciones y borrados.
Tipos de Triggers DML
Instead Of Al contrario que las after ignoran las acciones de la instrucción de desencadenamiento. Pueden realizar cambios en los datos originales que crean el evento. Un ejemplo útil es el que pusimos en la vista:
Imaginemos que tenemos una aplicación en produccion muy grande. Esta aplicacion tiene muchos módulos y muy antiguos que funcionan bien pero tenemos que desarrollar nuevas funcionalidades que implican modificiaciones en el modelo de datos existente. Imaginemos que tenemos una tabla “X” que vamos a dividir para esas nuevas funcionalidades en otras tres. Para evitar tener que retocar esos módulos antiguos podemos crear una vista “X”que relacione las tres nuevas tablas. Esto implica que la tabla vieja la tendremos que renombrar o borrar pero la aplicación ni se enterará ya que la vista tiene los mismos campos que la tabla original.
Vamos a verlo con un ejemplo. Ver script “20_triggers_instead_of.sql”.
1. Imaginemos que tenemos una tabla pedidos desnormalizada. Como se puede apreciar la tabla contiene el nombre del cliente y el nombre del producto.
solidq.com
73
2. Insertamos 3 pedidos:
3. Esta es una select de la tabla después de las inserciones:
solidq.com
74
4. Imaginad que nos indican que tenemos que normalizar la tabla. Esto implica:
Que la descripción del cliente tendrá que ir a una nueva tabla (CLIENTES)
La descripción del producto tendrá que ir a una nueva tabla (PRODUCTOS)
Cargar las dos nuevas tablas con los datos de la tabla pedidos
Añadir dos campos nuevos a la tabla pedidos que serán el id del cliente y el id del producto
Actualizar esos dos campos con los valores que le corresponda
Renombrar la tabla (PED)
Crear la vista pedidos que sustituirá a la tabla pedidos
Crear un trigger “instead of” para que el insert sobre la vista pedidos cargue la información en las tres tablas subyacentes (PED, CLIENTES, PRODUCTOS)
5. Creación tabla CLIENTES:
6. Creación tabla PRODUCTOS:
7. Cargamos la tabla CLIENTES, insertamos un registro por cada valor distinto:
8. Cargamos la tabla PRODUCTOS, insertarmos un registro por cada valor distinto:
9. Añadimos a la tabla pedidos el campo ID_CLI que enlazará el pedido con la descripción del cliente y el campo ID_PROD que enlazará el pedido con la descripción del producto:
solidq.com
75
10. Actualizamos la tabla de pedido cargando el código del cliente o del producto. Para ello unimos las tablas pedidos y clientes por el nombre del cliente y la tabla de productos por el nombre del producto:
11. Borramos las columnas de descripción:
12. Renombramos la tabla de PEDIDOS a PED:
13. Creamos la vista PEDIDOS:
La clausula SCHEMABINDING es para evitar que se puedan modificar las tablas. Si no existiera esa restricción y se borrara una columna que se utiliza en la tabla por ejemplo la vista daría error
solidq.com
76
14. Creamos el trigger INSTEAD OF
Cuando se trabaja sobre la vista si se inserta sobre ella nos van a llegar los mismos campos que expone. Lo que hace entonces es:
Verificamos si existe el cliente cruzando la tabla clientes con la tabla inserted utilizando como nexo de unión el nombre del cliente. La lógica es: “si no existe en la tabla clientes un cliente cuyo nombre sea el que está en la tabla inserted hay que crearlo ya que no existe”
A continuación cargamos en una variable el ID del cliente
Verificamos si existe el producto cruzando la tabla productos con la tabla inserted utilizando como nexo de unión el nombre del producto. La lógica es: “si no existe en la tabla productos un producto cuyo nombre sea el que está en la tabla inserted hay que crearlo ya que no existe”
A continuación cargamos en una variable el ID del producto
El último paso es insertar el pedido utilizando las variables con los códigos
solidq.com
77
15. Ahora vamos a probar el trigger insertando un registro en la vista:
Tanto el cliente (JUAN) como el producto (VASO) no existen por lo que tendran que insertarse en sus respectivas tablas
16. Para demostrar que hemos insertado correctamente realizaremos una consulta sobre todas las tablas implicadas
solidq.com
78
After Se ejecutan después de una acción de las instrucciones INSERT, UPDATE, MERGE o DELETE. Los desencadenadores AFTER no se ejecutan nunca si se produce una infracción de restricción; por tanto, no se puede usar estos desencadenadores para ningún procesamiento que pueda impedir infracciones de restricciones. Para cada acción INSERT, UPDATE o DELETE especificada en una instrucción MERGE, se activa el desencadenador correspondiente para cada operación DML.
Vamos a implementar ahora un ejemplo con triggers after para realizar la siguiente lógica de validación y log que es para lo que se suelen utilizar los triggers en los clientes que visitamos:
Tendremos 3 tablas: o Tabla de pedidos o Tablas de crédito de cliente. Si se inserta o se actualiza un pedido y el nuevo
importe es superior a 1000€ se incrementa el crédito en el 10% de ese importe
solidq.com
79
o Tabla de cambios. Se quieren auditar todos los cambios que se producen en un pedido, esto quiere decir que si se produce una modificación o un borrado estos deberán registrarse
Siguiendo esta lógica hemos implementado este codigo.
Creamos las tablas si no existen:
Insertamos el credito para el cliente 1 y 2:
Creamos 3 procedimientos, uno para insertar pedidos, otro para modificarlos y otro para borrarlos:
solidq.com
80
Creamos el trigger de inserción, este solo se activa en las inserciones. La lógica es que si el importe es mayor o igual a 100 se incrementa el credito del cliente en un 10% del importe:
solidq.com
81
La forma en que lo hace es:
Declara variables para recibir los campos del pedido insertado que están en la “pseudotabla inserted”
Si el importe es superior o igual a 1000 actualizamos el crédito del cliente con un update
Creamos el trigger de modificación, este solo se activa en las modificaciones. Lá lógica es :
Cargamos en variables los campos del nuevo importe de la tabla inserted
Cargamos en variables la cantidad y el precio antiguo de la tabla deleted
Si el importe antiguo superaba o era igual a 1000 tenemos que decrementarlo el 10% de ese importe en el credito del cliente con un update
Si el importe nuevo supera o es igual a 1000 incrementamos en un 10% el crédito de ese cliente
Como debemos registrar todos los cambios y es un update insertamos un nuevo registro en la tabla de cambios pedidos con los valores antiguos. Los nuevos estarán en la tabla pedidos2
solidq.com
82
solidq.com
83
Creamos el trigger de borrado, este solo se activa en los borrados. Lá lógica es :
Cargamos en variables la cantidad, el precio y el cliente antiguo de la tabla deleted
Si el importe antiguo superaba o era igual a 1000 tenemos que decrementarlo el 10% de ese importe en el credito del cliente con un update
Como debemos registrar todos los cambios y es un delete insertamos un nuevo registro en la tabla de cambios pedidos, de esta manera quedará registrado que se borro ya que no existe en la tabla de pedidos2
solidq.com
84
Ahora lo que hacemos es insertar un nuevo pedido:
Nos devolverá el id de pedido que se le ha asignado
Incrementará el crédito del cliente en 200 ya que el importe son 2000 y se incrementea el 10%. Como partiamos de 200 y añadimos 200 mas tenemos 400
Como es una inserción no se hace nada en la tabla de cambios
La siguiente operación es realizar una modificación sobre el pedido:
Cambiamos la cantidad de 20 a 30 por lo que habrá que quitar el 10% del crédito del importe viejo y añadir el 10% del nuevo. Tendremos 400 – 200 +300 = 500.
Insertamos el cambio del pedido en la tabla histórica
Nota, en los triggers de modificación podemos utilizar las funciones UPDATE(“Nombre de Columna”) y Columns_Updated() para saber que campo/s han cambiado.
Referencia:
https://msdn.microsoft.com/es‐es/library/ms187326(v=sql.120).aspx
https://msdn.microsoft.com/es‐es/library/ms186329(v=sql.90).aspx
solidq.com
85
Por último realizamos un borrado:
Se borra el pedido de la tabla pedidos2
Decrementamos el crédito decrementando el 10% del importe ( 500 ‐300 = 200)
Registramos el cambio en la tabla histórica
Ver script “21_triggers_after.sql”.
solidq.com
86
Clausula Output
La cláusula Output se aplica desde la versión SQL Server 2005 y nos va a permitir eliminar los triggers after. Esta cláusula devuelve los datos de una modificación DML y utiliza las “pseudotablas inserted y delete”, es decir actua como una select.
En el siguiente ejemplo insertamos en la tabla T_OUTPUT 10 nombres de tablas. Como tenemos un campo id que es un identity (autonumérico incremental) nos devolverá los números asignados a cada nombre de tabla insertado:
Tras el insert hay que poner los campos de la tabla inserted que deseemos. Recordad que las tablas inserted y deleted tienen los mismos campos que la tabla sobre la que se realiza la operación. La select simplemente coge 10 tablas de sys.tablas y las inserta en la tabla T_OUTPUT.
solidq.com
87
Con “INTO” pasa estos datos a una tabla temporal o variable #tabla. En este caso los datos devueltos por la cláusula output pasan a la variable tabla @T_OUTPUT. Fijemonos que se han creado nuevos ids.
El siguiente ejemplo es de OUTPUT sobre una operación update:
solidq.com
88
El siguiente ejemplo es de OUTPUT sobre una operación de borrado:
En ambos casos si utilizaramos la cláusula into pasariamos los datos a una tabla.
Ver script “22_output.sql”.
solidq.com
89
Por último y vamos a demostrar que se pueden prescindir de los triggers INSERT, UPATE y DELETE del ejemplo de “triggers after” con OUTPUT.
Como es hacer lo mismo pero sin triggers, las tablas base no cambian:
Vamos a utilizar TVPS por lo que declaramos el tipo tabla. Como estos tipos tabla se utilizan en un procedimiento para poder borrar el tipo hay que borrar el procedimiento primero para que no existan dependencias.
El tipo tiene los mismos campos que la tabla pedidos2.
A continuación creamos un único procedimiento para todas las operaciones (INSERT, DELETE y UPDATE). El procedimiento recibe dos variables de tipo tabla pedidos2, la primera para inserciones y modificaciones y la segunda para borrados.
solidq.com
90
Primero creamos una tabla temporal llamada #SALIDA donde guardaremos la accion que se ha produccido sobre el registro y los valores viejos y nuevos. Esto es así porque vamos a utilizar una operación merge. Como esta operación potencialmente puede realizar inserciones, borrados y modificaciones la cláusula output debe indicarnos que operación se ha realizado sobre cada registro. Esta operación la proporciona la palabla $action. Por otro lado:
Las inserciones guardan los datos en la tabla inserted ya que son nuevos.
Los updates guardan los datos nuevos en inserted y los viejos en en deleted
Los borrados guardan los datos en la tabla deletec ya que son viejos.
En este merge solo vamos a utilizar insert y update.
solidq.com
91
Si la variable @T_PEDIDOS tiene algún registro es que se va a modificar o insertar. Si el campo ID_PEDIDO es nulo es que es una inserción ya que se le tiene que asignar un nuevo número a ese pedido.
Lo que hacemos en el merge es:
Si de la tabla de pedidos que nos pasan como parametro hay registros cuyo ID_PEDIDO existe entonces se realiza un update
Si de la tabla de pedidos que nos pasan como parametro hay registros cuyo ID_PEDIDO no existe (aquellos que tienen valor NULL) entonces se realiza una inserción
Cada registro es devuelto por la clásula OUPUT y se indica aparte de los valores la acción realizada sobre cada uno de ellos. En este caso insertamos esos registros en la tabla #SALIDA.
Sí la variable @T_PEDIDOS_BORRAR tiene 1 o más registros es que esos pedidos se deben de borrar.
En este caso haremos un delete y como en el caso anterior con la cláusula OUTPUT pasaremos los datos a la tabla #SALIDA.
Importante, en el delete todos los registros de OUTPUT son borrados, eso implica que no hay variable $action ya que no tiene sentido por se una única operación. Esto lo solventamos añadiendo el literal ‘DELETE’ así en la tabla #SALIDA identificaremos los registros borrados.
Como en la tabla #SALIDA tenemos los valores cons los que hemos trabajado ahora podemos aplicar la logica de negocio que requiere nuestra aplicación.
En el caso de los borrados y modificaciones debemos de decrementar un 10% el credito del cliente si el importe pasó de 1000€.
solidq.com
92
La constante de tabla DATOS_ANTIGUOS actua como una tabla temporal que utilizamos para hacer la modificación sobre la tabla CREDITO_CLIENTES.
En el caso de las inserciones y modificaciones debemos incrementar un 10% el credito del cliente si el importe es mayor o igual a 1000€.
La constante de tabla DATOS_NUEVOS actua como una tabla temporal que utilizamos para hacer la modificación sobre la tabla CREDITO_CLIENTES.
solidq.com
93
Por último y para los borrados y modifaciones se almacenan los datos antiguos en la tabla histórica:
Comentar que en este ejemplo en lugar de trabajar con 1 registro al trabajar con tablas como parametros podemos realizar esas operaciones sobre muchos registros lo cual incrementará el rendimiento. Ver punto “Cursores vs Conjuntos”.
Como en el caso de los triggers establecemos el crédito de dos clientes:
solidq.com
94
En el primer test que vamos a realizar haremos una inserción, para ello cargamos un registro en la variable tabla y el ID_PEDIDO será nulo.
Incrementará el crédito del cliente en 200 ya que el importe son 2000 y se incrementea el 10%. Como partiamos de 200 y añadimos 200 mas tenemos 400
Como es una inserción no se hace nada en la tabla de cambios
La siguiente operación es realizar una modificación sobre el pedido:
Cambiamos la cantidad de 20 a 30 por lo que habrá que quitar el 10% del crédito del importe viejo y añadir el 10% del nuevo. Tendremos 400 – 200 +300 = 500.
Insertamos el cambio del pedido en la tabla histórica
Por último realizamos un borrado:
Se borra el pedido de la tabla pedidos2
Decrementamos el crédito decrementando el 10% del importe ( 500 ‐300 = 200)
Registramos el cambio en la tabla histórica
solidq.com
95
Ver script “23_output_merge.sql”.
En resumen, como se puede ver se realizan las mismas operaciones no se necesitan triggers y está todo el código centralizado en un único procedimiento almacenado.
Cursores vs Conjuntos El modelo relacional está basado en la teoría de conjuntos. Un conjunto, es una colección de objetos y a cada uno de estos objetos se le denomina elemento del conjunto. Un conjunto por defecto no está ordenado. Las tablas son los conjuntos. Este el motivo principal por el que SQL Server no garantiza ningún tipo de ordenación cuando se hace una select de una tabla a pesar de que nos devuelva los resultados en el mismo orden algunas veces. En SQL Server podemos trabajar de forma orientada a conjuntos utilizando consultas TSQL sobre tablas. Cuando hablamos de trabajar orientados a conjuntos estamos hablando de realizar inserciones, modificaciones, borrados o “merges8” de varias (pueden ser mucha o pocas) filas a la vez. Esta forma de trabajar es la más recomendada y eficiente, ya que SQL Server está pensado para ello en lugar tratar un conjunto de filas en lugar de una sola. Las ventajas de trabajar orientados a conjuntos son:
Escalabilidad, nuestra aplicación será más escalable, esto significa que podrá aumentar el rendimiento conforme se aumentan los recursos. Este principio está relacionado con el siguiente punto
Utiliza el hardware más eficientemente. Ejemplo, se pueden realizar operaciones en paralelo. Esto puede aplicar a selects, inserts, deletes, updates y merges. Las
8 La operación merge es una operación atómica que puede combinar inserts, updates y deletes ante múltiples filas de entrada.
solidq.com
96
operaciones orientadas a registro (bucles o cursores) solo utilizan un core ya que el bucle no es paralelizable. En este caso da igual cuantos cores tenga nuestro servidor ya que solo utilizaremos uno. Las operaciones en bucle sobrecargan más el sistema ya que:
o Hay un control del bucle o Por muy poco que tarde cada iteración generará un consumo constante en
lugar de un pico que es lo que generan los conjuntos. Imaginemos que tenemos que borrar 1.000.000 de registros de una tabla del 1 al 1.000.000. Si lo hacemos en bloque podemos generar un pico (azul) como el de la siguiente imagen. Si los borramos uno a 1 podríamos generar una línea que se prolonga en el tiempo como la naranja. Si muchas operaciones orientadas a registro se producen al mismo tiempo sus consumos constantes se sumarán generando una línea como la roja. Desde el punto de vista de consumo y eficiencia de procesos lo interesante es “exprimir” al máximo las capacidades de computo del sistema para que estas terminen lo más rápido posible.
Explotar SQL Server. o SQL Server es una base de datos relacional que cuyo principio es la lógica de
conjuntos o Para hacer operaciones en paralelo es necesario un software que explote esa
posibilidad. SQL Server crea operaciones en paralelo cuando considera que esta paralelización es una ventaja. No obstante esto no quiere decir que todas las operaciones sobre conjuntos de registros tengan que utilizar necesariamente paralelismo. SQL Server hace operaciones sobre conjuntos de registros también sobre un solo core.
o Pueden aprovecharse los índices de forma eficiente en operaciones de actualización y borrado. Los índices son estructuras que maneja SQL Server para incrementar la velocidad de las operaciones de lectura
solidq.com
97
El único motivo para utilizar cursores es que se necesite procesar algo de acuerdo a una lógica que no pueda ser implementada en la cláusula “order by”. Vamos a hacer un ejemplo que demuestra este tipo de prácticas de programación ineficientes que se utiliza habitualmente. Este proceso trata de borrar un conjunto de registros de una tabla haciéndolo de dos formas, la primera registro a registro y la segunda como si todos los registros fueran un conjunto.
1. El primer paso es crear las dos tablas de las que habrá que borrar los datos. Se llamarán TEST_DELETE y TEST_DELETE2. Crearemos una tercera tabla que contendrá información de los registros a borrar
2. Cargamos las tablas que contienen los datos a borrar con 15.000 registros iguales. La tabla que contiene información de los a borrar de las tablas destino contiene 10.000 registros tomados de una de estas tablas.
solidq.com
98
3. Abrimos una traza con SQL Server Profiler para almacenar los resultados de tiempo, cpu, lecturas y escrituras. A continuación lanzamos el proceso de borrado orientado a registro y orientado a conjuntos. Hacemos una select al final de cada proceso para comprobar que quedan 5.000 registros en cada tabla (hemos borrado 10.000).
solidq.com
99
4. A parte de que en el borrado orientado a conjuntos se escribe menos como podremos comprobar los resultados son bastante
mejores
La mejora tanto en CPU, lecturas y duración en milisegundos es del 99%. Esto se traduce en que hemos pasado de 41 segundos a 1 centésima de segundo. La mejora se debe a que trabaja con los 10.000 registros a la vez y a que utiliza el índice clúster de la tabla TEST_DELETE2 que es la manera más rápida de acceder a los datos para borrarlos. Lo que queremos remarcar en este caso es que este tipo de programación es capaz de utilizar eficientemente los índices.
Ver script ” 24_delete_masivo_cursor_vs_conjuntos.sql”.
SQL Server para Programadores Accidentales
© Solid Quality™ Press www.solidq.com Página 101
Otros ejemplos de programación orientada a conjuntos son los que hemos hecho con TVPS y Merges tanto en el apartado de procedimientos como en el de la cláusula OUPTUT.