sentencias - Rendimiento de SQL Server con gran consulta

sql server sentencia in

Question

Hola a todos Tengo un par de consultas para algunos informes en los que cada consulta extrae datos de más de 35 tablas. Cada tabla tiene casi 100K registros. Todas las consultas son Union ALL por ejemplo

;With CTE
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table3 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table4 WHERE Some_Condition
.
.
. And so on 
)
SELECT col1, col2, col3 FROM CTE
ORDER BY col3 DESC

Hasta ahora, solo he probado esta consulta en Dev Server y puedo ver que se necesita tiempo para obtener los resultados. Todas estas más de 35 tablas no están relacionadas entre sí y esta es la única forma en que se me ocurre obtener todos los datos deseados en el conjunto de resultados.

1. ¿Hay una mejor manera de hacer este tipo de consulta?
   

2. Si este es el único camino a seguir para este tipo de consulta, ¿cómo puedo mejorar el rendimiento de esta consulta si realizo algún cambio si es posible?
   

Mi opinión
No me importa tener algunas lecturas sucias en este informe. Estaba pensando en utilizar las sugerencias de consulta with nolock o Transaction Isolation Level establecido para READ UNCOMMITED .

¿Ayudará algo de esto?

Editar
Cada tabla tiene columnas de 5-10 bits y una columna de fecha correspondiente para cada columna de bits y mi condición para cada instrucción SELECT es algo así como

WHERE BitColumn = 1 AND DateColumn IS NULL 

Sugerencia por compañeros

Índice filtrado

CREATE NONCLUSTERED INDEX IX_Table_Column
ON TableName(BitColumn)
WHERE BitColum = 1

Índice filtrado con columna incluida

CREATE NONCLUSTERED INDEX fIX_IX_Table_Column
ON TableName(BitColumn)
INCLUDE (DateColumn)
WHERE DateColumn IS NULL

¿Es esta la mejor manera de ir? o alguna sugerencia por favor ???

Answer 1

bueno, no has dado ninguna estadística o tiempo de ejecución de muestra de ninguna ejecución, por lo que no es posible adivinar qué es lenta y si realmente es lenta. ¿Cuantos datos hay en el conjunto de resultados? podría ser solo recuperar 100K filas ya que el resultado es simplemente tomarse su tiempo. si el conjunto de resultados de 10000 filas tarda 5 minutos, sí definitivamente se puede mirar algo. por lo tanto, si tiene una consulta de muestra, el número de filas en el resultado y cuánto tiempo le tomó a un par de ejecuciones con diferentes condiciones, publíquelo. nos ayudará a comparar los resultados.

Por cierto, no use CTE simplemente use la selección de consulta interna y externa regular. asegúrese de que Temp DB esté configurado correctamente. LDF y MDF no están configurados por defecto para un aumento del 10%. por cierto intento y error, sabrá cuánto aumenta el registro y la DB temporal para la verdad de las consultas de rango y, en base a eso, debe establecer el tamaño inicial y el incremento del MDF y LDF de la base de datos temporal. para el índice de filtro cubierto, la columna de inclusión debe ser col1, col2 y co3, no la columna Fecha a menos que la fecha también esté en la lista de selección.

¿con qué frecuencia se actualizan los datos en las 35 tablas originales? si es máximo una vez al día o si todos reciben actualizaciones casi al mismo tiempo, Indexed-Views puede ser una solución posible. pero si las tablas originales obtienen actualizaciones más de una vez al día o si reciben actualizaciones en cualquier momento y no están en la misma línea, entonces no piense en Indexed-View.

si el espacio en el disco no es un problema como último recurso, pruebe y pruebe el rendimiento usando el activador en cada tabla. crea una nueva tabla para mantener los resultados finales como esperas de esta consulta de selección. crear inserción / actualización / eliminar desencadenador en cada 35 tabla donde se verifican las condiciones dentro del desencadenador y, en caso afirmativo, copiar la misma inserción / actualización / eliminar en la nueva tabla. Sí, necesitará una columna en la nueva tabla que identifique qué datos provienen de qué tabla. porque Date es una columna de Null-Able, no obtiene el máximo beneficio de Index en esa columna ya que "en su mayoría está buscando WHERE Date es NULL". en la nueva consulta Table only que siempre haces es donde Date es NULL, entonces ni siquiera te molestas en crear esa columna, simplemente creas columnas BIT y otras col1, col2, col3, etc ... si das un ejemplo real de tu consulta y explicas el real tablas, otros detalles pueden ser ejercicios posteriores.

Answer 2

Hay muchas cosas que se pueden hacer para hacerlo más rápido. Si asumo que necesita hacer estos UNION, entonces puede acelerar la consulta al:

1. Almacenamiento en caché de los resultados, por ejemplo,
   • ¿Puedes crear una vista indexada a partir de todo el enunciado? O hay muchas diferentes condiciones WHERE, entonces, ¿habría muchas vistas indexadas? Pero sepa que esto ralentizará las modificaciones (INSERTAR, etc.) para esas tablas
   • ¿Puedes guardarlo en caché de una manera diferente? Tal vez en la capa intermedia?
   • ¿Se puede recalcular por adelantado?
2. Haga un índice de cobertura. Las columnas principales son columnas de WHERE y luego todas las demás columnas de la consulta como columnas incluidas
   • Tenga en cuenta que un índice de cobertura también se puede filtrar, pero el índice filtrado no se usa si WHERE en la consulta tendrá variables / parámetros y potencialmente puede tener el valor que no está cubierto por el índice filtrado (es decir, la fila no es cubierto)
3. ORDER BY causará la clasificación
   • Si puedes almacenarlo en caché, está bien; no se necesitará ningún tipo (está clasificado en caché)
   • De lo contrario, la ordenación está unida a la CPU (y la E / S está unida si no está en la memoria). Para acelerarlo, ¿usas una intercalación rápida? La diferencia de rendimiento entre la colación más lenta y más rápida puede ser incluso 3 veces. Por ejemplo, SQL_EBCDIC280_CP1_CS_AS, SQL_Latin1_General_CP1251_CS_AS, SQL_Latin1_General_CP1_CI_AS son una de las intercalaciones más rápidas. Sin embargo, es difícil hacer recomendaciones si no conozco las características de clasificación que necesita
4. Red
   • ''tamaño de paquete de red'' para la conexión que hace SELECT debería ser el valor máximo posible: 32,767 bytes si el conjunto de resultados (número de filas) será grande. Esto puede establecerse en el lado del cliente, por ejemplo, si usa .NET y SqlConnection en la cadena de conexión. Esto minimizará la sobrecarga de la CPU al enviar datos desde SQL Server y mejorará el rendimiento tanto en el lado del cliente como en el del servidor. Esto puede aumentar el rendimiento incluso en decenas de porcentajes si la red fue el cuello de botella
   • Use punto final de memoria compartida si el cliente está en el Servidor SQL; de lo contrario TCP / IP para el mejor rendimiento
5. Cosas generales
   • Como dijiste, usar el nivel de aislamiento leído sin emitir mejorará el rendimiento

...

Probablemente no pueda hacer cambios más allá de reescribir la consulta, etc., pero por las dudas, agregar más memoria en caso de que no sea suficiente ahora, o usar SQL Server 2014 en funciones de memoria :-), ... seguramente ayudaría.

Hay demasiadas cosas que se pueden ajustar, pero es difícil señalar las más importantes si la pregunta no es muy específica.

Espero que esto ayude un poco

Answer 3

Las sugerencias de consulta o el Nivel de aislamiento solo lo ayudarán en caso de que se produzca algún bloqueo. Si no te importa leer sucio y hay bloqueos durante la ejecución, podría ser una buena idea.

La pregunta clave es cuántos datos se ajustan a la clausula Where que necesita usar (WHERE BitColumn = 1 AND DateColumn IS NULL) Si el subconjunto filtrado por eso es pequeño en comparación con el número total de filas, utilice un índice en ambas columnas, BitColum y DateColumn, incluidas las columnas en la cláusula de selección para evitar operaciones de "Búsqueda de página" en su plan de consulta.

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(BitColumn, DateColumn)
INCLUDE (col1, col2, col3)

Por supuesto, el espacio necesario para ese índice cubierto filtrado depende del tipo de datos de los campos implicados y del número de filas que satisfacen WHERE BitColumn = 1 AND DateColumn IS NULL.

Después de eso, recomiendo usar una vista en lugar de un CTE:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
.
.
.
)

Al hacer eso, su plan de consulta debería parecerse a 35 escaneos de índice pequeños, pero si la mayoría de los datos satisfacen la sección de su índice, el rendimiento será similar al escaneo de las 35 tablas fuente y la solución no lo valdrá. .

Pero usted dice "Cada tabla tiene columnas de 5-10 bits y una columna de fecha correspondiente ...", entonces creo que no será una buena idea hacer un índice por columna de bit. Si necesita filtrar utilizando diferentes BitColums y diferentes DateColums, use una columna de cálculo en su tabla:

ALTER TABLE Table1 ADD ComputedFilterFlag AS
CAST(
    CASE WHEN BitColum1 = 1 AND DateColumn1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN BitColum2 = 1 AND DateColumn2 IS NULL THEN 2 ELSE 0 END +
    CASE WHEN BitColum3 = 1 AND DateColumn3 IS NULL THEN 4 ELSE 0 END
AS tinyint)

Recomiendo usar el valor 2 ^ (X-1) para conditionX (BitColumnX = 1 y DateColumnX IS NOT NULL). Le permitirá filtrar usando cualquier combinación de ese criterio. Al usar el valor 3, puede ubicar todas las filas que logren: Bit1, Date1 y Bit2, condición de Date2. Cualquier combinación de condición tiene su valor correspondiente de ComputedFilterFlag porque ComputedFilterFlag actúa como un mapa de bits de condiciones. Si tiene menos de 8 filtros diferentes, debe usar tinyint para ahorrar espacio en el índice y disminuir las operaciones de E / S necesarias.

A continuación, utilice un índice sobre la columna ComputerFilterFlag:

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(ComputedFilterFlag)
INCLUDE (col1, col2, col3)

Y crea la vista:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
.
.
.
)

Al hacer eso, su índice abarca todas las condiciones y su plan de consulta debe parecerse a 35 búsquedas de índices pequeños.

Pero esta es una solución difícil, puede ser una refactorización en el esquema de su tabla que podría producir resultados más simples y rápidos.

Answer 4

Nunca obtendrás resultados en tiempo real de una consulta de unión en muchas tablas, pero puedo decirte cómo obtuve un poco de velocidad en una situación similar. Espero que esto te ayude.

En realidad, puedes ejecutarlos todos a la vez con un poco de codificación e ingenio.

Si creas una tabla temporal global en lugar de una expresión de tabla común y no colocas ninguna tecla en la tabla temporal global, ralentizará las cosas. A continuación, inicia todas las consultas individuales que se insertan en la tabla temporal global. Lo hice cien veces o más manualmente y es más rápido que una consulta de unión porque se ejecuta una consulta en cada núcleo de la CPU. La parte difícil es el mecanismo para determinar cuándo las consultas individuales han terminado por su cuenta para esa pieza, por lo tanto, hago esto manualmente.