sql-server - rango - obtener mes y año de una fecha sql
La forma más rápida para esta consulta(¿Cuál es la mejor estrategia?) Dado un rango de fechas (10)
La siguiente secuencia de comandos enumerará los posibles índices que faltan (puede filtrar la declaración por t.nombre).
SELECT t.name AS ''affected_table'',
''Create NonClustered Index IX_'' + t.name + ''_missing_'' + CAST(ddmid.index_handle AS VARCHAR(10)) + '' On '' + ddmid.STATEMENT + '' ('' + ISNULL(ddmid.equality_columns, '''') +
CASE
WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL
THEN '',''
ELSE ''''
END + ISNULL(ddmid.inequality_columns, '''') + '')'' + ISNULL('' Include ('' + ddmid.included_columns + '');'', '';'') AS sql_statement,
ddmigs.user_seeks,
ddmigs.user_scans,
CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) AS ''est_impact'',
ddmigs.last_user_seek
FROM sys.dm_db_missing_index_groups AS ddmig
INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.tables AS t
ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
AND CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) > 100
ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) DESC;
Tengo una tabla A que tiene fecha de inicio y fecha de finalización como 2 columnas de fecha y hora, además de algunas otras columnas más. Tengo otra tabla B que tiene una columna de fecha y hora llamada columna de fechas. Esto está en SQL Server 2005.
Aquí la pregunta: cómo configurar mejor los índices, etc. para obtener lo siguiente:
select ....
from A , B
where A.startDate >= B.dates
and A.endDate < B.dates
Ambas tablas tienen varios miles de registros.
Necesita 3 índices A.startDate, B.dates y A.endDate, puede ser índice (A.endDate + A.startDate) también es bueno. No tengo detalles sobre otras columnas y propósitos para estas tablas, pero reviso la posibilidad de usar el índice agrupado.
En cualquier caso, utilice la opción "Plan de ejecución" para tomar una decisión entre todas estas variantes, porque mi sugerencia es demasiado general.
Se necesita un poco más de información. ¿Cuántas otras columnas hay en las tablas? ¿Estas tablas existentes con muchas consultas ya van en contra de ellas, o todas las tablas nuevas? ¿Qué tipo de problema de rendimiento estás viendo que te lleve a hacer la pregunta?
Supongo que las tres columnas NO son NULL (no solo para la sintaxis de la consulta, sino también para la utilidad del índice).
Comenzaría con un índice compuesto en A.startDate + A.endDate, y otro índice en B.dates (pero es probable que esto no sea necesario). A menos que estas fechas sean el objetivo principal de las tablas, evitaría crear índices agrupados en estas columnas. Esto es doblemente cierto si estas tablas son tablas existentes con otras consultas ejecutándose contra ellas. Las consultas anteriores pueden escribirse esperando los índices agrupados existentes.
Si necesita optimizar intente ejecutar esta consulta en el Analizador de consultas.
Solo agregaría un índice agrupado en B.dates. Si agrega índices en startDate y endDate, no comprará nada porque de todos modos obtendrá escaneos de índice en A. El índice agrupado en B te da una búsqueda de índice en B al menos. Un escaneo de tabla y un escaneo de índice son lo mismo, así que no tiene sentido agregar índices para sacar la palabra escaneo de tabla de su plan de ejecución :)
Me burlaría de algunas maneras o vería si puedes volver a hacer tu consulta para no requerir un escaneo de tabla en A, que supongo que no es realmente posible.
Yo iría con esto
CREATE CLUSTERED INDEX IX_DateRange ON dbo.A
(
StartDate,
EndDate DESC
)
GO
cada versión de sql server 2000, 2005, 2008 tiene un programa llamado DataBase tuning advisor cuando ejecuta alguna consulta, le dice qué índices necesita agregar para obtener la consulta más rápido. Saludos cordiales, Iordan
Actualizar:
Vea este artículo en mi blog para una estrategia de indexación eficiente para su consulta usando columnas calculadas:
La idea principal es que solo calculamos la length redondeada y startDate para usted rangos y luego buscarlos usando condiciones de igualdad (que son buenos para B-Tree índices B-Tree )
En MySQL y en SQL Server 2008 puede usar índices SPATIAL ( R-Tree ).
Son especialmente buenos para las condiciones como "seleccionar todos los registros con un punto determinado dentro del rango del registro", que es solo su caso.
Almacena el start_date y el end_date como el principio y el final de LineString (convirtiéndolos en marcas de tiempo UNIX de otro valor numérico), los indexa con un índice LineString y busca todos los LineString s cuyo cuadro delimitador mínimo ( MBR ) contiene la fecha valor en cuestión, usando MBRContains .
Vea esta entrada en mi blog sobre cómo hacer esto en MySQL :
y una breve descripción general del rendimiento para SQL Server :
La misma solución se puede aplicar para buscar una IP dada en rangos de red almacenados en la base de datos.
Esta tarea, junto con su consulta, es otro ejemplo frecuente de dicha condición.
Los índices simples de B-Tree no son buenos si los rangos se pueden superponer.
Si no pueden (y usted lo sabe), puede usar la brillante solución propuesta por @AlexKuznetsov
También tenga en cuenta que este rendimiento de consulta depende totalmente de su distribución de datos.
Si tiene muchos registros en B y pocos registros en A , puede simplemente generar un índice en B.dates y dejar que el TS/CIS en A vaya.
Esta consulta siempre leerá todas las filas desde A y usará Index Seek en B.dates en un bucle anidado.
Si sus datos se distribuyen de otra forma, es decir, tiene muchas filas en A pero pocas en B , y los intervalos son generalmente cortos, entonces podría rediseñar un poco sus tablas:
A
start_date interval_length
, crea un índice compuesto en A (interval_length, start_date)
y usa esta consulta:
SELECT *
FROM (
SELECT DISTINCT interval_length
FROM a
) ai
CROSS JOIN
b
JOIN a
ON a.interval_length = ai.interval_length
AND a.start_date BETWEEN b.date - ai.interval_length AND b.date
He trabajado en dos empresas (ambas con sistemas de administración de tiempo y asistencia) que tienen muchas veces con las columnas startDate y endDate. En mi experiencia, no hay buenos índices que siempre funcionen con intervalos de fechas .
Pruebe con índices como (fecha de inicio, fecha de finalización) y (fecha de finalización, fecha de inicio) para ver si ayudan. Mucho depende de cómo sean los datos en la tabla . Por ejemplo, si tiende a tener muchas filas antiguas con un endDate antes de las fechas que está buscando, puede ser útil forzar a Sql a usar un índice basado en (endDate, startDate).
También intente usar un índice que cubra todas las columnas que se encuentran en su instrucción "where", por lo que sql no necesita leer la tabla principal hasta que no haya resuelto qué filas debe devolver.
Puede que tenga que usar sugerencias de índice, ya que es poco probable que el procesador de consultas sepa lo suficiente sobre los datos para hacer una buena selección de índices; este es uno de los pocos casos en los que tuve que considerar sugerencias de índice.
Ampliando los datos, para que tenga una tabla que contiene (fecha, remado) con una fila para cada fecha dentro del rango de fechas puede ser necesaria. Sin embargo, mantener actualizada la tabla de "índice" es un problema.
Si sabe que algunos de sus intervalos de fechas no se superponen, eche un vistazo al uso de CROSS APPLY para optimizar las uniones entre las condiciones BETWEEN (por ejemplo, los registros de enfermedad de un empleado no se pueden solapar)
Al final del día, si solo tiene varios miles de registros, una exploración de tabla completa no es tan mala.
Las personas de Quassnoi que usan índices SPATIAL , no tengo experiencia con "abusar" de los índices espaciales de esta manera, pero creo que vale la pena intentarlo. Sin embargo, tenga mucho cuidado si debe multiplicar los proveedores de bases de datos múltiples, ya que el índice espacial es bastante nuevo. También es posible que aún necesite las columnas de fecha para herramientas de informes, etc.
(Tarde o temprano tendrá que ser capaz de encontrar todas las filas que se superponen a un rango de fechas, entonces se vuelve aún más difícil obtener índices que arrojen buenos resultados).