sql - tools - Para campos de autoincremento: MAX(ID) vs TOP 1 ID ORDER BY ID DESC
sql server tuning queries (7)
Sí, en este caso, Id es el campo en el que he definido el índice agrupado. Si el índice es ID DESC, entonces qué ... Y sí, sería bueno saber cómo se vería afectado el rendimiento si
- Id es un índice agrupado + clave principal.
- Id es un índice agrupado y no una clave principal.
- Id es una clave primaria ASC + de índice no agrupado.
- Id es un índice no agrupado ASC y no una clave principal.
- Id es un índice no agrupado DESC + clave principal.
- Id es un DESC de índice no agrupado y no una clave principal.
- La identificación es sólo AutoIncrement
Para los casos 1 y 2, ambos realizarán una exploración de índice agrupado que devuelve un solo registro. No hay diferencia de IO entre las dos consultas.
Para los casos 3, 4, 5 y 6, ambos realizarán una exploración de índice que devuelve un solo registro. No hay diferencia de IO entre las dos consultas.
Para el caso 7, ambos realizarán un escaneo de tabla. No hay diferencia en el costo de IO.
Resumen: Caso 1-6 están hechos de ganar! Si estás en el caso 7, entonces ya has perdido desde un punto de vista de IO.
Puede medir la IO utilizando el analizador de consultas de SQL. Ejecute esto antes de su consulta.
SET STATISTICS IO ON
Quiero encontrar el valor AutoIncremented más alto de un campo. (no se puede buscar después de una inserción donde puedo usar @@SCOPE_IDENTITY etc.) ¿Cuál de estas dos consultas se ejecutará más rápido o dará un mejor rendimiento? Id es la clave principal y el campo de autoincrement para autoincrement . Y esto es para Sql Server 2005.
SELECT MAX(Id) FROM Table1
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
[Editar]
Sí, en este caso, Id es el campo en el que he definido el índice agrupado.
Si el índice es ID DESC entonces qué ...
Y sí, sería bueno saber cómo se vería afectado el rendimiento si
1. Id es un índice agrupado + clave principal.
2. Id es un índice agrupado y no una clave principal.
3. Id es una clave primaria ASC + de índice no agrupado.
4. Id es un índice ASC no agrupado y no una clave principal.
5. Id es una clave primaria DESC + de índice no agrupado.
6. Id es un DESC de índice no agrupado y no una clave principal.
7. La identificación es solo AutoIncrement
Espero que no sea una tarea difícil!
Acabo de probar las dos sentencias de SQL que proporcionó contra un conjunto de datos típico:
SELECT MAX(Id) FROM Table1
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
Y SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC es ligeramente más rápido porque tiene un último paso en el plan de ejecución. Aquí están los planes de ejecución que realiza cada consulta:
SELECCIONA MAX (Id) DE Table1
Exploración de índice agrupado >> Arriba >> Agregado de transmisión >> Seleccionar
SELECCIONE LA IDENTIFICACIÓN DEL TOP 1 DE LA TABLA1 ORDER BY Id DESC
Exploración de índice agrupado >> Arriba >> Seleccionar
En teoría, usarán los mismos planes y se ejecutarán casi al mismo tiempo.
En la práctica,
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
probablemente utilizará un PRIMARY KEY INDEX .
Además, este es más extensible si decide seleccionar otra columna junto con id .
Un plan real en MAX() dice:
SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN
, mientras que el plan para TOP 1 dice:
SELECT <- TOP <- CLUSTERED INDEX SCAN
, es decir aggregate se omite el aggregate .
El agregado en realidad no hará nada aquí, ya que solo hay una fila.
Como @Mehrdad Afshari y @John Sansom , en un campo no indexado, MAX es ligeramente más rápido (por supuesto, no 20 veces como dice el optimizador):
-- 18,874,368 rows SET LANGUAGE ENGLISH SET STATISTICS TIME ON SET STATISTICS IO ON PRINT ''MAX'' SELECT MAX(id) FROM master PRINT ''TOP 1'' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT ''MAX'' SELECT MAX(id) FROM master PRINT ''TOP 1'' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT ''MAX'' SELECT MAX(id) FROM master PRINT ''TOP 1'' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT ''MAX'' SELECT MAX(id) FROM master PRINT ''TOP 1'' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT ''MAX'' SELECT MAX(id) FROM master PRINT ''TOP 1'' SELECT TOP 1 id FROM master ORDER BY id DESC Changed language setting to us_english. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 20 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 447, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5452 ms, elapsed time = 2766 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6813 ms, elapsed time = 3449 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 44, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5359 ms, elapsed time = 2714 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6766 ms, elapsed time = 3379 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5406 ms, elapsed time = 2726 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6780 ms, elapsed time = 3415 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5392 ms, elapsed time = 2709 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6766 ms, elapsed time = 3387 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5374 ms, elapsed time = 2708 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table ''master''. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6797 ms, elapsed time = 3494 ms.
Nadie mencionó IDENT_CURRENT (''Table1'') - los deja a todos a la basura - por supuesto, solo funciona en columnas de identidad, pero esa era la pregunta ...
Simplemente compare los planes de ejecución y verá (presione Ctrl+M en Management Studio cuando edite una consulta). Mi conjetura será que estas consultas tienen el mismo desempeño siempre que haya un índice (agrupado) en la columna Id .
Sin embargo, esto en conjunto es una muy mala idea.
MAX es generalmente más rápido.
Ambas consultas utilizarán el índice en la columna si existe.
Si no existe un índice en la columna, la consulta TOP 1 utilizará un operador Top N Sort para ordenar la tabla en lugar de la agregación de flujos , lo que la hace más lenta.
MAX también proporciona una mejor legibilidad.
Nota al MAX : si bien MAX utilizará un operador de agregación de flujo en el plan de ejecución en el caso indexado, no tiene ningún costo específico, ya que solo procesa una fila ( Actual Rows = 1 ). Puede comparar consultas ejecutándolas en un solo lote y ver el costo relativo. En el caso indexado, ambas consultas costarán el 50%. Probé el caso no indexado en una tabla con alrededor de 7000 filas y TOP costará 65% en comparación con MAX que cuesta 35%.
Si hay un índice agrupado, virtualmente no hay diferencia en el rendimiento entre las dos consultas.
Esto se debe a que ambos realizarán una exploración de índice agrupado que asumirá el 100% del costo de la consulta.
Al realizar las dos consultas en una columna que no tiene un índice, se utilizan 3 operadores en ambos planes de ejecución.
La cláusula superior utiliza el operador Ordenar y la función Max utiliza un operador de agregación de flujos.
Cuando no hay índice, la función MAX () proporciona un mejor rendimiento.
Se puede encontrar una prueba de concepto y se puede encontrar un tutorial completo de un escenario de prueba here