Ajuste del rendimiento de la base de datos SQL para desarrolladores

El ajuste del rendimiento de SQL puede ser una tarea increíblemente difícil, especialmente cuando se trabaja con datos a gran escala, donde incluso el cambio más pequeño puede tener un impacto dramático (positivo o negativo) en el rendimiento.

En empresas medianas y grandes, la mayoría de los ajustes de rendimiento de SQL estarán a cargo de un administrador de base de datos (DBA). Pero créanme, hay muchos desarrolladores que tienen que realizar tareas similares a las de los administradores de bases de datos. Además, en muchas de las empresas que he visto que tienen DBA, a menudo tienen dificultades para trabajar bien con los desarrolladores; los puestos simplemente requieren diferentes modos de resolución de problemas, lo que puede generar desacuerdos entre los compañeros de trabajo.

Además de eso, la estructura corporativa también puede desempeñar un papel. Digamos que el equipo de DBA está ubicado en el piso 10 con todas sus bases de datos, mientras que los desarrolladores están en el piso 15, o incluso en un edificio diferente bajo una estructura de informes completamente separada; ciertamente es difícil trabajar juntos sin problemas en estas condiciones.

En este artículo, me gustaría lograr dos cosas:

1. Proporcione a los desarrolladores algunas técnicas de ajuste del rendimiento de SQL del lado del desarrollador.
2. Explique cómo los desarrolladores y los DBA pueden trabajar juntos de manera efectiva.

Ajuste del rendimiento de SQL (en la base de código): índices

Si es un novato en las bases de datos e incluso se pregunta "¿Qué es el ajuste del rendimiento de SQL?", Debe saber que la indexación es una forma efectiva de ajustar su base de datos SQL que a menudo se descuida durante el desarrollo. En términos básicos, un índice es una estructura de datos que mejora la velocidad de las operaciones de recuperación de datos en una tabla de base de datos al proporcionar búsquedas aleatorias rápidas y un acceso eficiente a los registros ordenados. Esto significa que una vez que haya creado un índice, puede seleccionar u ordenar sus filas más rápido que antes.

Los índices también se utilizan para definir una clave principal o un índice único que garantizará que ninguna otra columna tenga los mismos valores. Por supuesto, la indexación de bases de datos es un tema muy interesante al que no puedo hacer justicia con esta breve descripción (pero aquí hay una descripción más detallada).

Si es nuevo en los índices, le recomiendo usar este diagrama al estructurar sus consultas:

Básicamente, el objetivo es indexar las principales columnas de búsqueda y ordenación.

Tenga en cuenta que si INSERT , UPDATE y DELETE manipulan constantemente sus tablas, debe tener cuidado al indexar; podría terminar disminuyendo el rendimiento, ya que todos los índices deben modificarse después de estas operaciones.

Además, los DBA a menudo eliminan sus índices SQL antes de realizar inserciones por lotes de más de un millón de filas para acelerar el proceso de inserción. Después de insertar el lote, vuelven a crear los índices. Recuerde, sin embargo, que eliminar índices afectará todas las consultas que se ejecuten en esa tabla; por lo tanto, este enfoque solo se recomienda cuando se trabaja con una sola inserción grande.

Ajuste de SQL: planes de ejecución en SQL Server

Por cierto: la herramienta Plan de ejecución en SQL Server puede ser útil para crear índices.

Su función principal es mostrar gráficamente los métodos de recuperación de datos elegidos por el optimizador de consultas de SQL Server. Si nunca los ha visto antes, hay un recorrido detallado.

Para recuperar el plan de ejecución (en SQL Server Management Studio), simplemente haga clic en "Incluir plan de ejecución real" (CTRL + M) antes de ejecutar su consulta.

Posteriormente, aparecerá una tercera pestaña denominada “Plan de Ejecución”. Es posible que vea un índice faltante detectado. Para crearlo, simplemente haga clic derecho en el plan de ejecución y elija "Detalles de índice faltantes...". ¡Es tan simple como eso!

( Click para ampliar )

Ajuste de SQL: evite los bucles de codificación

Imagine un escenario en el que 1000 consultas golpean su base de datos en secuencia. Algo como:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Debe evitar tales bucles en su código. Por ejemplo, podríamos transformar el fragmento de código anterior mediante el uso de una INSERT o UPDATE única con varias filas y valores:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Asegúrese de que su cláusula WHERE evite actualizar el valor almacenado si coincide con el valor existente. Una optimización tan trivial puede aumentar drásticamente el rendimiento de las consultas SQL al actualizar solo cientos de filas en lugar de miles. Por ejemplo:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Ajuste de SQL: evite las subconsultas de SQL correlacionadas

Una subconsulta correlacionada es aquella que utiliza valores de la consulta principal. Este tipo de consulta SQL tiende a ejecutarse fila por fila, una vez por cada fila devuelta por la consulta externa y, por lo tanto, disminuye el rendimiento de la consulta SQL. Los nuevos desarrolladores de SQL a menudo se ven atrapados estructurando sus consultas de esta manera, porque generalmente es la ruta más fácil.

Aquí hay un ejemplo de una subconsulta correlacionada:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

En particular, el problema es que la consulta interna ( SELECT CompanyName… ) se ejecuta para cada fila devuelta por la consulta externa ( SELECT c.Name… ). Pero, ¿por qué revisar la Company una y otra vez para cada fila procesada por la consulta externa?

Una técnica de ajuste de rendimiento de SQL más eficiente sería refactorizar la subconsulta correlacionada como una combinación:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

En este caso, repasamos la tabla Company solo una vez, al principio, y la UNIMOS con la tabla Customer . A partir de ahí, podemos seleccionar los valores que necesitamos ( co.CompanyName ) de manera más eficiente.

Ajuste de SQL: seleccione con moderación

¡Uno de mis consejos favoritos de optimización de SQL es evitar SELECT * ! En su lugar, debe incluir individualmente las columnas específicas que necesita. Nuevamente, esto suena simple, pero veo este error por todas partes. Considere una tabla con cientos de columnas y millones de filas: si su aplicación realmente solo necesita unas pocas columnas, no tiene sentido consultar todos los datos. Es un desperdicio masivo de recursos. ( Para más problemas, ver aquí. )

Por ejemplo:

 SELECT * FROM Employees

contra

 SELECT FirstName, City, Country FROM Employees

Si realmente necesita cada columna, enumere explícitamente cada columna. Esto no es tanto una regla, sino un medio para prevenir futuros errores del sistema y un ajuste adicional del rendimiento de SQL. Por ejemplo, si está utilizando INSERT... SELECT... y la tabla de origen ha cambiado mediante la adición de una nueva columna, es posible que tenga problemas, incluso si la tabla de destino no necesita esa columna. p.ej:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Para evitar este tipo de error de SQL Server, debe declarar cada columna individualmente:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Tenga en cuenta, sin embargo, que hay algunas situaciones en las que el uso de SELECT * podría ser apropiado. Por ejemplo, con tablas temporales, lo que nos lleva a nuestro siguiente tema.

Ajuste de SQL: el uso inteligente de las tablas temporales (#Temp)

Las tablas temporales suelen aumentar la complejidad de una consulta. Si su código se puede escribir de una manera simple y directa, le sugiero que evite las tablas temporales.

Pero si tiene un procedimiento almacenado con alguna manipulación de datos que no se puede manejar con una sola consulta, puede usar tablas temporales como intermediarios para ayudarlo a generar un resultado final.

Cuando tiene que unirse a una tabla grande y hay condiciones en dicha tabla, puede aumentar el rendimiento de la base de datos transfiriendo sus datos en una tabla temporal y luego haciendo una unión en eso . Su tabla temporal tendrá menos filas que la tabla original (grande), ¡así que la unión terminará más rápido!

La decisión no siempre es sencilla, pero este ejemplo le dará una idea de las situaciones en las que podría querer usar tablas temporales:

Imagine una tabla de clientes con millones de registros. Tienes que hacer una unión en una región específica. Puede lograr esto usando una declaración SELECT INTO y luego uniéndose a la tabla temporal:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Nota: algunos desarrolladores de SQL también evitan usar SELECT INTO para crear tablas temporales, diciendo que este comando bloquea la base de datos tempdb, impidiendo que otros usuarios creen tablas temporales. Afortunadamente, esto se solucionó en 7.0 y versiones posteriores ).

Como alternativa a las tablas temporales, podría considerar usar una subconsulta como tabla:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

¡Pero espera! Hay un problema con esta segunda consulta. Como se describió anteriormente, solo deberíamos incluir las columnas que necesitamos en nuestra subconsulta (es decir, no usar SELECT * ). Teniendo eso en cuenta:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Todos estos fragmentos de SQL devolverán los mismos datos. Pero con las tablas temporales, podríamos, por ejemplo, crear un índice en la tabla temporal para mejorar el rendimiento. Aquí hay una buena discusión sobre las diferencias entre las tablas temporales y las subconsultas.

Finalmente, cuando haya terminado con su tabla temporal, elimínela para borrar los recursos de tempdb, en lugar de simplemente esperar a que se elimine automáticamente (como sucederá cuando finalice su conexión a la base de datos):

 DROP TABLE #temp

Ajuste de SQL: "¿Existe mi registro?"

Esta técnica de optimización de SQL se refiere al uso de EXISTS() . Si desea verificar si existe un registro, use EXISTS() en lugar de COUNT() . Mientras COUNT() escanea toda la tabla, contando todas las entradas que coinciden con su condición, EXISTS() se cerrará tan pronto como vea el resultado que necesita. Esto le dará un mejor rendimiento y un código más claro.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

contra

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Ajuste del rendimiento de SQL con SQL Server 2016

Como probablemente saben los DBA que trabajan con SQL Server 2016, la versión marcó un cambio importante en los valores predeterminados y la gestión de la compatibilidad. Como versión principal, por supuesto, viene con nuevas optimizaciones de consultas, pero el control sobre si se usan ahora se simplifica a través de sys.databases.compatibility_level .

Ajuste del rendimiento de SQL (en la oficina)

Los administradores de bases de datos SQL (DBA) y los desarrolladores a menudo se enfrentan por cuestiones relacionadas con datos y no relacionadas con datos. A partir de mi experiencia, aquí hay algunos consejos (para ambas partes) sobre cómo llevarse bien y trabajar juntos de manera efectiva.

Pío

Optimización de base de datos para desarrolladores:

1. Si su aplicación deja de funcionar repentinamente, es posible que no sea un problema de la base de datos. Por ejemplo, tal vez tenga un problema de red. ¡Investigue un poco antes de acusar a un DBA!

2. Incluso si es un modelador de datos ninja SQL, pídale a un DBA que lo ayude con su diagrama relacional. Tienen mucho que compartir y ofrecer.

3. A los DBA no les gustan los cambios rápidos. Esto es natural: necesitan analizar la base de datos como un todo y examinar el impacto de cualquier cambio desde todos los ángulos. Un simple cambio en una columna puede tardar una semana en implementarse, pero eso se debe a que un error podría materializarse en enormes pérdidas para la empresa. ¡Se paciente!

4. No solicite a los DBA de SQL que realicen cambios en los datos en un entorno de producción. Si desea acceder a la base de datos de producción, debe ser responsable de todos sus propios cambios.

Optimización de base de datos para DBA de SQL Server:

1. Si no le gusta que la gente le pregunte sobre la base de datos, ofrézcales un panel de estado en tiempo real. Los desarrolladores siempre sospechan del estado de una base de datos, y dicho panel podría ahorrarles a todos tiempo y energía.

2. Ayudar a los desarrolladores en un entorno de prueba/control de calidad. Facilite la simulación de un servidor de producción con pruebas simples en datos del mundo real. Esto será un importante ahorro de tiempo para los demás, así como para usted mismo.

3. Los desarrolladores pasan todo el día en sistemas con lógica empresarial que cambia con frecuencia. Intenta entender este mundo siendo más flexible y sé capaz de romper algunas reglas en un momento crítico.

4. Las bases de datos SQL evolucionan. Llegará el día en que tengas que migrar tus datos a una nueva versión. Los desarrolladores cuentan con nuevas funcionalidades significativas con cada nueva versión. En lugar de negarse a aceptar sus cambios, planee con anticipación y esté listo para la migración.