¿Cuál es la forma más rápida de calcular un registro COUNT?

Los planes de ejecución de consultas revelan diferencias

Siempre me han gustado las preguntas simples con muchas trampas. Aquí hay uno: ¿cómo calcular el número total de registros en una tabla? A primera vista, es simple, pero si profundiza, puede encontrar muchos matices peculiares.

Entonces, comencemos con lo simple. ¿Las siguientes consultas son diferentes en términos del resultado final?

SELECT COUNT(*) FROM Sales.SalesOrderDetail
SELECT COUNT_BIG(*) FROM Sales.SalesOrderDetail

La mayoría de ustedes dirá que no hay diferencia. Estas consultas devuelven un resultado idéntico, sin embargo CONTAR devolverá el valor EN T escribir mientras COUNT_BIG devolverá el valor EMPEZANDO escribe.

Si analizamos el plan de actuación, notaremos diferencias que muchas veces no se notan. Cuando usas CONTARel plan mostrará Calcular el escalar operación.

Los planes de ejecución de consultas revelan diferencias

Si nos fijamos en las propiedades del operador, veremos lo siguiente:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(int,[Expr1004],0))

Esto es porque COUNT_BIG utilizado implícitamente en la llamada CONTARy luego el resultado se convierte en EN T.

Recuerde que la conversión de tipos de datos aumenta la carga de la CPU. Muchos de ustedes pueden decir que este operador no es un gran problema en términos de rendimiento. Sin embargo, hay una cosa que vale la pena mencionar: servidor SQL tiende a subestimar Calcular el escalar operadores. Sin embargo, el ejemplo anterior no requiere preocuparse por el rendimiento: recortar Int64 oficina Int32 no requiere grandes recursos.

También conozco gente que me gusta disfrutar. CIM en lugar de CONTAR:

SELECT SUM(1) FROM Sales.SalesOrderDetail

Esta opción es aproximadamente idéntica CONTAR - también nos pondremos excesivos Calcular el escalar en términos de rendimiento:

[Expr1003] = Scalar Operator(CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END)

Rompamos un mito. Si especifica un valor constante en CONTARla consulta no será más rápida, ya que el optimizador crea un plan de ejecución idéntico para estas consultas:

SELECT COUNT_BIG(*) FROM Sales.SalesOrderDetail
SELECT COUNT_BIG(1) FROM Sales.SalesOrderDetail

Ahora centrémonos en los problemas de rendimiento.

Si usamos las consultas anteriores, deberíamos usar Escaneo de índice completo (o Escaneo completo de la tabla si es una tabla de montón) para contar servidor SQL registros. De todos modos, estas operaciones están lejos de ser rápidas. La mejor manera de obtener registros es usar sys.dm_db_partition_stats o particiones del sistema tipos de sistema (también hay índices del sistemapero se ha dejado por compatibilidad con versiones anteriores Servidor SQL 2000).

USE AdventureWorks2012
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT COUNT_BIG(*)
FROM Sales.SalesOrderDetail

SELECT SUM(p.[rows])
FROM sys.partitions p
WHERE p.[object_id] = OBJECT_ID('Sales.SalesOrderDetail')
    AND p.index_id < 2

SELECT SUM(s.row_count)
FROM sys.dm_db_partition_stats s
WHERE s.[object_id] = OBJECT_ID('Sales.SalesOrderDetail')
    AND s.index_id < 2

Cuando comparamos los planes de rendimiento, el acceso a las vistas del sistema tiene menos demanda:

Comparación de planes de desempeño

Cuando probamos en Trabajo de aventuralos beneficios de las especies sistémicas no son tan obvios:

Table 'SalesOrderDetail'. Scan count 1, logical reads 276, ...
 SQL Server Execution Times:
   CPU time = 12 ms,  elapsed time = 26 ms.

Table 'sysrowsets'. Scan count 1, logical reads 5, ...
 SQL Server Execution Times:
   CPU time = 4 ms,  elapsed time = 4 ms.

Table 'sysidxstats'. Scan count 1, logical reads 2, ...
 SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 1 ms.

Tiempo de ejecución para una tabla dividida con 30 millones de registros:

Table 'big_test'. Scan count 6, logical reads 114911, ...
 SQL Server Execution Times:
   CPU time = 4859 ms,  elapsed time = 5079 ms.

Table 'sysrowsets'. Scan count 1, logical reads 25, ...
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

Table 'sysidxstats'. Scan count 1, logical reads 2, ...
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

En caso de que necesite verificar los registros en la tabla, el uso de metadatos no proporciona mucha ventaja (como se mencionó anteriormente).

IF EXISTS(SELECT * FROM Sales.SalesOrderDetail)
    PRINT 1

IF EXISTS(
    SELECT * FROM sys.dm_db_partition_stats
    WHERE [object_id] = OBJECT_ID('Sales.SalesOrderDetail')
        AND row_count > 0
) PRINT 1
Table 'SalesOrderDetail'. Scan count 1, logical reads 2,...
 SQL Server Execution Times:
   CPU time = 1 ms,  elapsed time = 3 ms.

Table 'sysidxstats'. Scan count 1, logical reads 2,...
 SQL Server Execution Times:
   CPU time = 4 ms,  elapsed time = 5 ms.

En términos prácticos, será un poco más lento, porque servidor SQL genera un plan de ejecución más sofisticado para seleccionar de los metadatos.

Planes de desempeño comparados

Aquí hay otro caso que encontré:

IF (SELECT COUNT(*) FROM Sales.SalesOrderHeader) > 0
    PRINT 1

Con la ayuda del optimizador, este caso se puede simplificar al plan que recibimos EXISTE.

Esto se vuelve más interesante cuando necesitamos contar la cantidad de registros para todas las tablas a la vez. En mi práctica me he encontrado con varias opciones.

Opción 1 utilizando un procedimiento no documentado que pasa por alto todas las tablas de usuario:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp
GO
CREATE TABLE #temp (obj SYSNAME, row_count BIGINT)
GO

EXEC sys.sp_MSForEachTable @command1 = 'INSERT #temp SELECT ''?'', COUNT_BIG(*) FROM ?'

SELECT *
FROM #temp
ORDER BY row_count DESC

Opcion 2 - dinámica sql que genera SELECCIONAR CONTEO

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT 'UNION ALL SELECT ''' + SCHEMA_NAME(o.[schema_id]) + '.' + o.name + ''', COUNT_BIG(*)
    FROM [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']'
    FROM sys.objects o
    WHERE o.[type] = 'U'
        AND o.is_ms_shipped = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 10, '') + ' ORDER BY 2 DESC'

PRINT @SQL
EXEC sys.sp_executesql @SQL

peticiones: Opción # 3

SELECT SCHEMA_NAME(o.[schema_id]), o.name, t.row_count
FROM sys.objects o
JOIN (
    SELECT p.[object_id], row_count = SUM(p.row_count)
    FROM sys.dm_db_partition_stats p
    WHERE p.index_id < 2
    GROUP BY p.[object_id]
) t ON t.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
    AND o.is_ms_shipped = 0
ORDER BY t.row_count DESC

- una opción rápida para el uso diario:

A pesar de todos los elogios que he acumulado con una mirada sistemática, hay algunos "placeres" inesperados que puedes experimentar mientras trabajas con ellos. Recuerdo un error divertido: las vistas del sistema se actualizaron incorrectamente durante la migración desde Servidor SQL 2000 oficina2005 . Los más "afortunados" obtuvieron valores incorrectos para el número de filas en las tablas de metadatos. la creencia eraACTUALIZAR DBCC

. ÇServidor SQL 2005 SP1 , este error se solucionó y todo parecía estar bien. Sin embargo, nuevamente enfrenté el mismo problema al restaurar la copia de seguridad desde Servidor SQL 2005 SP4 oficinaServidor SQL 2012 SP2

UPDATE STATISTICS Person.Person WITH ROWCOUNT = 1000000000000000000

. No pude reproducir esto en un entorno real, así que engañé un poco al optimizador:

Veamos un ejemplo sencillo.

SELECT FirstName, COUNT(*)
FROM Person.Person
GROUP BY FirstName

La ejecución de la solicitud manual en sí tomó más tiempo de lo habitual: Una revisión del plan de solicitud reveló un valor completamente inadecuadoNúmeroEstimadoDeFilas

:

El número estimado de líneas es insuficiente

DECLARE @SQL NVARCHAR(MAX)
DECLARE @obj SYSNAME = 'Person.Person'
SELECT @SQL = 'DBCC SHOW_STATISTICS(''' + @obj + ''', ' + name + ') WITH STAT_HEADER'
FROM sys.stats
WHERE [object_id] = OBJECT_ID(@obj)
    AND stats_id < 2

EXEC sys.sp_executesql @SQL

Luego revisé las estadísticas del índice de clúster:

Todo estuvo bien.

El número de filas es adecuado.

SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID('Person.Person')
    AND indid < 2

SELECT SUM([rows])
FROM sys.partitions p
WHERE p.[object_id] = OBJECT_ID('Person.Person')
    AND p.index_id < 2

En cuanto a los puntos de vista sistémicos antes mencionados,

bueno, estaban lejos de la razón:

El resultado es el número incorrecto de filas. La consulta no contenía predicados para filtrar y el optimizador eligióEscaneo de índice completo . DurantePuntero completo / tabla de escaneo

el optimizador toma la cantidad esperada de filas de metadatos en lugar de estadísticas (no estoy muy seguro de si esto sucede siempre). No es ningún secreto que servidor SQL

genera un plan de ejecución basado en el número estimado de filas y calcula la memoria requerida para ejecutarlo. Una evaluación incorrecta puede conducir a una ocupación de la memoria excesiva.

session_id query_cost       requested_memory_kb  granted_memory_kb    required_memory_kb   used_memory_kb
---------- ---------------- -------------------- -------------------- -------------------- --------------------
56         11331568390567   769552               769552               6504                 6026

Este es el resultado de una estimación incorrecta del número de filas:

DBCC UPDATEUSAGE(AdventureWorks2012, 'Person.Person') WITH COUNT_ROWS
DBCC FREEPROCCACHE

El problema se resolvió de una manera bastante simple:

Después de volver a compilar la consulta, todo estaba bien:

session_id query_cost          requested_memory_kb  granted_memory_kb    required_memory_kb   used_memory_kb
---------- ------------------- -------------------- -------------------- -------------------- --------------------
52         0,291925808638711   1168                 1168                 1024                 952

El número estimado de líneas es correcto.

SELECT COUNT_BIG(*) FROM ...

Si las visiones sistémicas ya no sirven como una varita mágica, ¿qué más podemos hacer? Bueno, podemos volver a la práctica de la vieja escuela: Pero no esperaría un resultado durante la intensa inserción en la mesa. Mucho menos, "magia" NOLOCK

SELECT COUNT_BIG(*) FROM ... WITH(NOLOCK)

la sugerencia aún no garantiza el valor correcto: De hecho, necesitamos completar la consulta bajo PUBLICACIÓN POR ENTREGAS nivel de aislamiento para obtener el número correcto de registros en la tabla. Alternativamente, podemos usar TABLOCKX

SELECT COUNT_BIG(*) FROM ... WITH(TABLOCKX)

insinuación.
Como resultado, obtenemos un bloqueo de tabla exclusivo en el momento de la consulta. ¿Que es mejor? La respuesta: decide por ti mismo. Mi elección son los metadatos.

SELECT City, COUNT_BIG(*)
FROM Person.[Address]
--WHERE City = N'London'
GROUP BY City

Esto se vuelve aún más interesante si necesita contar la cantidad de líneas provistas:

IF OBJECT_ID('dbo.CityAddress', 'V') IS NOT NULL
    DROP VIEW dbo.CityAddress
GO

CREATE VIEW dbo.CityAddress
WITH SCHEMABINDING
AS
    SELECT City, [Rows] = COUNT_BIG(*)
    FROM Person.[Address]
    GROUP BY City
GO

CREATE UNIQUE CLUSTERED INDEX IX ON dbo.CityAddress (City)

Si no hay operaciones frecuentes de inserción y eliminación en la tabla, podemos crear una vista indexada:

SELECT City, COUNT_BIG(*)
FROM Person.[Address]
WHERE City = N'London'
GROUP BY City

SELECT *
FROM dbo.CityAddress
WHERE City = N'London'

Para estas consultas, el optimizador generará un plan idéntico basado en el índice de vista agrupada:

Aquí están los planes de ejecución con y sin vista indexada:

Planes de ejecución con y sin vista indexada

En esta publicación, quería mostrar que no existen soluciones perfectas para todos los días de tu vida. En cada caso, debe actuar según lo requiera la ocasión. Todas las pruebas se realizaronServidor SQL 2012 SP3 (11.00.6020)

. Los planes de ejecución se toman de SSMS 2014 ydbForge Studio para SQL Server

.

Conclusión

Si necesito contar el número de filas en una tabla, uso metadatos; esta es la forma más rápida. No tengas miedo del viejo error que describí.

Si es necesario contar rápidamente el número de filas en términos de algún campo o condición, trato de usar tipos indexados o índices filtrados. Todo depende de la situación. Si la mesa es pequeña o si no está en juego el tema del rendimiento, la vieja escuela SELECCIONAR CONTEO

… Sería la mejor opción.

Artículos de interés

Subir