Cinco formas de mejorar el rendimiento de SQL
Este artículo es un extracto del artículo de MSDN "Cinco formas de mejorar el rendimiento de SQL" y propone cómo mejorar la eficiencia operativa de las aplicaciones basadas en SQL Server. Es muy recomendable. Para algunos sistemas de aplicaciones con mucho tráfico, cómo mejorar y mejorar las instrucciones SQL es muy importante y un buen punto de avance.
*El artículo incluye principalmente los siguientes contenidos (si está interesado, visite directamente la siguiente URL para leer el documento completo en chino e inglés):
1. Devolver IDENTIDAD desde INSERTAR
SELECT @@IDENTITY
2, vista incrustada y tabla temporal
Tablas temporales: las tablas temporales en tempdb pueden provocar que las consultas realicen operaciones de E/S y accesos al disco intensos, y las tablas temporales pueden consumir muchos recursos.
Vistas en línea: utilice vistas en línea en lugar de tablas temporales. Una vista en línea es simplemente una consulta que se puede unir en la cláusula FROM. Si solo necesita unir datos a otras consultas, puede intentar utilizar vistas en línea para ahorrar recursos.
3. Evite LEFT JOIN y NULL
Los LEFT JOIN consumen muchos recursos porque contienen datos que coinciden con datos NULL (inexistentes). En algunos casos esto es inevitable, pero el coste puede ser muy elevado. LEFT JOIN consume más recursos que INNER JOIN, por lo que si puede reescribir la consulta para que no utilice ningún LEFT JOIN, obtendrá una muy buena recompensa.
Una técnica para acelerar las consultas que utilizan LEFT JOIN implica crear un tipo de datos TABLE, insertar todas las filas en la primera tabla (la tabla a la izquierda de LEFT JOIN) y luego actualizar el tipo de datos TABLE con los valores de la segunda mesa. Esta técnica es un proceso de dos pasos, pero puede ahorrar mucho tiempo en comparación con una UNIÓN IZQUIERDA estándar. Una buena regla es probar una variedad de técnicas diferentes y registrar el tiempo necesario para cada una hasta obtener la consulta que funcione mejor para su aplicación.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4, uso flexible del producto cartesiano
Entraré en gran detalle sobre esta técnica y recomendaré el uso de productos cartesianos en algunos casos. Por alguna razón, los productos cartesianos (CROSS JOIN) reciben muchas críticas y a menudo se advierte a los desarrolladores que no los utilicen en absoluto. En muchos casos, consumen demasiados recursos para utilizarlos de manera eficiente. Pero como cualquier herramienta en SQL, pueden resultar valiosas si se utilizan correctamente.
Vale la pena seguir uno de los códigos de ejemplo:
-- El producto cartesiano devuelve todos los clientes de todos los meses. El producto cartesiano básicamente multiplica la primera tabla por la segunda tabla, produciendo un conjunto de filas que contiene el número de filas de la primera tabla multiplicado por el número de filas de la segunda tabla. Por lo tanto, el producto cartesiano devuelve 12 (todos los meses) * 81 (todos los clientes) = 972 filas a la tabla @tblFinal. Los pasos finales son actualizar la tabla @tblFinal con los totales de ventas mensuales de cada cliente para este rango de fechas y seleccionar el conjunto final de filas.
DECLARAR @tblMonths TABLA (sMonth VARCHAR(7))
DECLARAR @tblCustomers TABLA (CustomerID CHAR(10),
Nombre de la empresa VARCHAR(50),
Nombre de contacto VARCHAR(50))
DECLARAR @tblFinal TABLA (sMes VARCHAR(7),
ID de cliente CHAR(10),
Nombre de la empresa VARCHAR(50),
Nombre de contacto VARCHAR(50),
mVentasDINERO)
DECLARAR @dtStartDate DATETIME,
@dtEndDate FECHA HORA,
@dtDate FECHA HORA,
@i INTEGER
SET @dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MES(@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + CAST(AÑO(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME))
ESTABLECER @dtStartDate = FECHAADD(MM, -1 * 12, @dtEndDate)
-- Coloca todos los meses en la primera tabla.
ESTABLECER @i = 0
MIENTRAS (@i < 12)
COMENZAR
ESTABLECER @dtDate = FECHAADD(mm, -1 * @i, @dtEndDate)
INSERTAR EN @tblMonths SELECCIONAR CAST(AÑO(@dtDate) AS VARCHAR(4)) + '-' +
CASO
CUANDO MES(@dtDate) < 10
ENTONCES '0' + CAST(MES(@dtDate) COMO VARCHAR(2))
ELSE CAST(MES(@dtDate) COMO VARCHAR(2))
FINALIZAR COMO mes
ESTABLECER @i = @i + 1
FINAL
: coloque todos los clientes que tuvieron ventas durante ese período en la tabla "y"
INSERTAR EN @tblCustomers
SELECCIONAR DISTINTO
c.ID de cliente,
c.Nombre de la empresa,
c.Nombre de contacto
DE Clientes c
Órdenes INNER JOIN o ON c.CustomerID = o.CustomerID
DONDE o.OrderDate ENTRE @dtStartDate Y @dtEndDate
INSERTAR EN @tblFinal
SELECCIONAR m.sMes,
c.ID de cliente,
c.Nombre de la empresa,
c.Nombre de contacto,
0
DESDE @tblMonths m UNIRSE CRUZADA @tblCustomers c
ACTUALIZAR @tblFinal SET
mVentas = misdatos.mVentas
DESDE @tblFinal f UNIÓN INTERNA
(
SELECCIONE c.ID de cliente,
CAST(AÑO(o.FechaPedido) AS VARCHAR(4)) + '-' +
CASO CUANDO MES(o.OrderDate) < 10
ENTONCES '0' + CAST(MES(o.OrderDate) COMO VARCHAR(2))
ELSE CAST(MES(o.OrderDate) COMO VARCHAR(2))
FINALIZAR COMO un mes,
SUMA(cantidad od. * precio unitario od.) COMO mVentas
DE Clientes c
Órdenes INNER JOIN o ON c.CustomerID = o.CustomerID
UNIÓN INTERNA [Detalles del pedido] od ON o.OrderID = od.OrderID
DONDE o.OrderDate ENTRE @dtStartDate Y @dtEndDate
Agrupar por
c.ID de cliente,
CAST(AÑO(o.FechaPedido) AS VARCHAR(4)) + '-' +
CASO CUANDO MES(o.OrderDate) < 10
ENTONCES '0' + CAST(MES(o.OrderDate) COMO VARCHAR(2))
ELSE CAST(MES(o.OrderDate) COMO VARCHAR(2))
FIN
) misdatos en f.CustomerID = mydata.CustomerID AND f.sMonth =
misdatos.sMes
SELECCIONAR f.sMes,
f.ID de cliente,
f.Nombre de la empresa,
f.Nombre de contacto,
f.mVentas
DE @tblFinal f
ORDEN POR
f.Nombre de la empresa,
f.sMes
5. Recoja las piezas que faltan y recupere las piezas que faltan.
A continuación se presentan algunas otras técnicas comunes que pueden ayudar a mejorar la eficiencia de las consultas SQL. Supongamos que desea agrupar a todos los vendedores por región y subtotalizar sus ventas, pero solo desea que esos vendedores estén marcados como activos en la base de datos. Puedes agrupar vendedores por región y eliminar aquellos que no están activos usando la cláusula HAVING, o puedes hacerlo en la cláusula WHERE. Hacer esto en la cláusula WHERE reduce la cantidad de filas que deben agruparse, por lo que es más eficiente que hacerlo en la cláusula HAVING. El filtrado basado en condiciones de fila en la cláusula HAVING obliga a la consulta a agrupar datos que se eliminarían en la cláusula WHERE.
Otro consejo de eficiencia es usar la palabra clave DISTINCT para buscar informes separados para filas de datos en lugar de usar la cláusula GROUP BY. En este caso, SQL usando la palabra clave DISTINCT es más eficiente. Utilice GROUP BY solo cuando necesite calcular funciones agregadas (SUM, COUNT, MAX, etc.). Además, si su consulta siempre devuelve una fila única, no utilice la palabra clave DISTINCT. En este caso, la palabra clave DISTINCT solo agrega sobrecarga al sistema.
－－－－－－－－－－－－－－－－－－－
URL china:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
URL en inglés:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/