separar - obtener hora sql server

Grupo SQL por año, mes, semana, día, hora SQL vs rendimiento de procedimiento

Question

Necesito escribir una consulta que agrupe una gran cantidad de registros por períodos de un año a otro.

Mi enfoque inicial ha sido decidir los periodos de procedimiento en C #, recorrer cada uno de ellos y ejecutar el SQL para obtener los datos para ese período, construyendo el conjunto de datos a medida que avanzo.

SELECT Sum(someValues)
FROM table1
WHERE deliveryDate BETWEEN @fromDate AND @ toDate

Posteriormente descubrí que puedo agrupar los registros usando Year (), Month () Day () y datepart (week, date) y datepart (hh, date).

SELECT Sum(someValues)
FROM table1
GROUP BY Year(deliveryDate), Month(deliveryDate), Day(deliveryDate)

Mi preocupación es que el uso de datepart en un grupo produzca un rendimiento peor que ejecutar la consulta varias veces durante un período de tiempo determinado debido a que no se puede usar el índice en el campo de fecha y hora de manera eficiente; ¿Alguna idea de si esto es cierto?

Gracias.

Answer 1

Como con cualquier cosa relacionada con el rendimiento Medida

Verificar el plan de consulta para el segundo enfoque le indicará cualquier problema obvio por adelantado (un escaneo completo de la tabla cuando usted sabe que no se necesita) pero no hay sustituto para la medición. En las pruebas de rendimiento SQL, la medición debe realizarse con los tamaños de datos de prueba adecuados.

Como se trata de un caso complejo, no solo se comparan dos formas diferentes de hacer una sola consulta, sino que se compara un único enfoque de consulta con uno iterativo, los aspectos de su entorno pueden desempeñar un papel importante en el rendimiento real.

Específicamente

1. la "distancia" entre su aplicación y la base de datos ya que la latencia de cada llamada será un desperdicio de tiempo en comparación con el enfoque de una gran consulta
2. Si está usando declaraciones preparadas o no (causando un esfuerzo de análisis adicional para el motor de la base de datos en cada consulta)
3. si la construcción de las gamas se pregunta por sí misma es costosa (muy influenciada por 2)

Answer 2

Creo que debería compararlo para obtener resultados confiables, pero, en mi humilde opinión, lo primero que pensé sería que dejar que el DB se ocupara de ello (su segundo enfoque) sería mucho más rápido que cuando lo hace en su código de cliente. Con su primer enfoque, tiene múltiples viajes de ida y vuelta a la base de datos, que creo que serán mucho más caros. :)

Answer 3

Es posible que desee ver un enfoque dimensional (esto es similar a lo sugerido por Walter Mitty), donde cada fila tiene una clave externa para una dimensión de fecha y / o tiempo. Esto permite sumas muy flexibles a través de la unión a esta tabla donde estas partes son precalculadas. En estos casos, la clave suele ser una clave entera natural de la forma YYYYMMDD y HHMMSS que es relativamente eficaz y también legible para el ser humano.

Otra alternativa podrían ser las vistas indizadas, donde hay expresiones separadas para cada una de las partes de la fecha.

O columnas calculadas.

Pero el rendimiento debe ser probado y los planes de ejecución examinados ...

Answer 4

Si coloca una fórmula en el campo parte de una comparación, obtendrá un escaneo de tabla .

El índice está en el campo, no en la fecha (campo), por lo que TODOS los campos deben calcularse , por lo que creo que su corazonada es correcta.

Answer 5

Si puedes tolerar el golpe de rendimiento de unirte a una tabla más, tengo una sugerencia que parece extraña pero que funciona muy bien.

Crea una tabla a la que llamaré ALMANAC con columnas como día de la semana, mes, año. Incluso puede agregar columnas para funciones específicas de la compañía de una fecha, como si la fecha es feriado de la compañía o no. Es posible que desee agregar una marca de tiempo inicial y final, como se indica a continuación.

Aunque podría salir adelante con una fila por día, cuando hice esto, me pareció conveniente ir con una fila por turno, donde hay tres turnos al día. Incluso a ese ritmo, un período de diez años fue solo un poco más de 10.000 filas.

Cuando escribe el SQL para llenar esta tabla, puede hacer uso de todas las funciones integradas orientadas a la fecha para facilitar el trabajo. Cuando vaya a hacer consultas, puede usar la columna de fecha como condición de unión, o puede necesitar dos marcas de tiempo para proporcionar un rango para capturar marcas de tiempo dentro del rango. El resto es tan fácil como trabajar con cualquier otro tipo de datos.

Answer 6

podrías hacer algo similar a esto:

SELECT Sum(someValues)
FROM 
(
    SELECT *, Year(deliveryDate) as Y, Month(deliveryDate) as M, Day(deliveryDate) as D
    FROM table1
    WHERE deliveryDate BETWEEN @fromDate AND @ toDate
) t
GROUP BY Y, M, D

Answer 7

Estaba buscando una solución similar para los informes, y me encontré con este artículo llamado Grupo por Mes (y otros períodos de tiempo) . Muestra varias formas, buenas y malas, para agrupar por el campo de fecha y hora. Definitivamente vale la pena mirar.