La parte más interesante de mi trabajo es la optimización y optimización del rendimiento en T-SQL. El corazón del ajuste de rendimiento en un servidor SQL es la indexación adecuada y utilizable en las tablas mediante el uso de consultas de Sargable.
A veces, el Senior Database Developer en el trabajo me dice que solo agregue un índice a una columna xyz porque se está usando en múltiples cláusulas Where dentro de múltiples consultas SQL. Ahí es cuando necesito calmar mi mente. Si agregar un índice en la columna xyz pudiera resolver todos los problemas de rendimiento, entonces miles de libros no se habrían publicado en el ajuste de rendimiento de T-SQL, y habría ido a buscar anaconda en la selva amazónica.
Consultas Sargable (Búsqueda Argumentable)
En términos simples, las consultas de Sargable son aquellas que pueden usar índices creados en ellas para búsquedas más rápidas y la ejecución de una consulta.
Una búsqueda más rápida significa hacer que un índice efectivo busque grandes cantidades de filas y evite los costosos escaneos de índices.
Búsqueda de índice - Las consultas pueden usar índices de manera efectiva y localizar filas con menos esfuerzo del optimizador de consultas.
Escaneo índice - Escanear toda la tabla para localizar filas para satisfacer los criterios de búsqueda
¿Qué hace que una consulta no sea Sargable (no puede usar los índices creados de manera efectiva)?
1. usar funciones en las condiciones de la cláusula Where (porque una función se evalúa contra cada fila, lo que obliga al optimizador de consultas a no usar el índice)
2. usando LIKE '% Proposal%' en consultas de búsqueda de comodines
3. realizar cálculos aritméticos en una columna de índice en una cláusula Where
Vamos a crear y rellenar una tabla con 0.1 millones de filas para ver cómo hacer consultas Sargable.
Esta secuencia de comandos tomaría tiempo para crear datos de muestra basados en su configuración de hardware (3-5 minutos).
- Cree una tabla con clave principal CREATE TABLE EmployeeTest (id INT IDENTITY (1, 1) CLAVE PRIMARIA, Salario INT, DateOfBirth DATETIME, EmployeeName VARCHAR (80)); GO - Insertar filas con valores aleatorios DECLARAR @row INT; DECLARE @string VARCHAR (80), @length INT, @code INT; SET @row = 0; MIENTRAS que @row <100000 BEGIN SET @row = @row + 1; IF @row = 10000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 20000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 30000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 40000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 50000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 60000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 70000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 80000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 90000 PRINT 'Filas insertadas:' + CONVERT (VARCHAR (20), @ row); IF @row = 100000 PRINT 'Hecho, filas insertadas:' + CONVERT (VARCHAR (20), @ row); - Construye la cadena aleatoria SET @length = ROUND (80 * RAND (), 0); SET @string = "; MIENTRAS que @length> 0 BEGIN SET @length = @length - 1; SET @code = ROUND (32 * RAND (), 0) - 6; IF @code BETWEEN 1 AND 26 SET @string = @ string + CHAR (ASCII ('a') + @ code-1); ELSE SET @string = @string + "; FIN - Listo para el registro SET NOCOUNT ON; INSERTE EN LOS VALORES DE LA PRUEBA DEL EMPLEADO (REDONDO (2000000 * RAND () + 10000,0), CONVERTIR (DATETIME, REDONDA (60000 * RAND () - 30000, 9)), @string) FIN IR Vamos a crear un índice no agrupado en cada columna.
CREAR ÍNDICE NO CLUSIFICADO [NCI_EmployeeTest_Salary] ON [dbo]. [EmployeeTest] ([Salary] ASC) GO CREATE NO INDICADO ÍNDICE [NCI_EmployeeTest_DateOfBirth] ON [dbo]. [EmployeeTest] ([DateOfBirth] ASC) GO CREATE NEXCLUSTERED INDEX [NCI_EmployeeTest_EmployeeName] ON [dbo]. [EmployeeTest] ([EmployeeName] ASC) GO
Veamos algunas consultas de muestra para ver la diferencia entre consultas sargable y no sargable.
1. Filtrar el resultado en función de los nombres de los empleados que comienzan con A
CONFIGURAR ESTADÍSTICAS EN LA IO - Consulta no programable debido a la función utilizada en la cláusula Where SELECCIONE EmployeeName FROM EmployeeTest WHERE LEFT (EmployeeName, 1) = 'A'; --Seguable Query SELECCIONE EmployeeName DE EmployeeTest DONDE EmployeeName ME GUSTA 'A%'; PONER ESTADISTICAS DESACTIVADAS
Las estadísticas a continuación muestran que la primera consulta no sargable tomó 680 lecturas lógicas, mientras que la consulta sargable con una búsqueda de comodines hizo solo 25 lecturas lógicas.
(3115 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 680, lecturas físicas 1, lecturas previas 688, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0. (3115 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 25, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0.Los planes de ejecución que se muestran a continuación muestran las primeras consultas no realizadas por Sargable. Costo del 97%, mientras que la consulta de Sargable lleva 3% con búsqueda de índice.
2. Filtrar los resultados por un año específico
CONFIGURAR ESTADÍSTICAS IO ENCENDIDO - No-Sargable Query debido a la función utilizada en la cláusula Where SELECT DateOfBirth FROM EmployeeTest WHERE YEAR (DateOfBirth) = '1952'; --Subable Query SELECT DateOfBirth FROM EmployeeTest WHERE DateOfBirth> = '19520101' AND DateOfBirth <'19530101'; PONER ESTADISTICAS DESACTIVADAS
Las estadísticas a continuación muestran que la primera consulta no Sargable tomó 226 lecturas lógicas, mientras que la consulta Sargable completó solo cuatro lecturas lógicas.
(628 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 226, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0.(628 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 4, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0.Los Planes de Ejecución que se muestran a continuación muestran las primeras consultas no realizadas por Sargable. 98% del costo del lote con Index Scan, mientras que la consulta Sargable toma 2% con búsqueda de índice.
3. Cálculos en una columna de índice en una cláusula donde
CONFIGURAR ESTADÍSTICAS ENTRE IO - No-Sargable Query debido al cálculo realizado en la columna de índice --en donde cláusula SELECCIONE Salario DE EmployeeTest DONDE Salary / 2 = 50147; - Consulta de consulta SELECCIONE Salario DE EmployeeTest WHERE Salary = (50147 * 2); PONER ESTADISTICAS DESACTIVADAS
Las estadísticas a continuación muestran que la primera consulta no sargable tomó 178 lecturas lógicas, mientras que la consulta sargable completó solo dos lecturas lógicas.
(3 filas afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 178, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0 (Tabla (s) fila (s) afectada (s)) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 2, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0.Los siguientes planes de ejecución muestran la primera consulta no sargable. Costo de lote del 99% con escaneo de índice, mientras que la consulta de Sargable lleva 1% con búsqueda de índice.
4. Usando la función ISNULL en una cláusula donde
CONFIGURAR ESTADÍSTICAS EN LA IO - No-Sargable Query debido a la función ISNULL en la columna de índice --en la cláusula donde se selecciona EmployeeName FROM EmployeeTest donde ISNULL (EmployeeName, 'Vru') = 'Vru'; --Subable Query seleccione EmployeeName FROM EmployeeTest donde EmployeeName = 'Vru' O EmployeeName IS NULL; PONER ESTADISTICAS DESACTIVADAS
Las estadísticas a continuación muestran que la primera consulta no sargable tomó 680 lecturas lógicas, mientras que la consulta sargable completó solo seis lecturas lógicas.
(1 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el número 1, lecturas lógicas 680, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0 (1 fila (s) afectadas) Tabla 'EmployeeTest'. Escanear el recuento 2, lecturas lógicas 6, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas 0, lecturas físicas lob 0, lecturas previas lob 0.Los siguientes planes de ejecución muestran la primera consulta no sargable. Costo de lote del 99% con escaneo de índice, mientras que la consulta de Sargable lleva 1% con búsqueda de índice.
