operaciones - ¿Cómo mejorar el rendimiento del filtrado de fecha y hora en SQL Server?

operaciones con fechas sql

Question

Tengo un problema con el filtrado por columnas de datetime .

Probé estos dos métodos:

datefield < ''2013-03-15 17:17:55.179''
datefield < CAST(''2013-03-15 17:17:55.179'' AS datetime)

Tengo una base de datos grande con más de 3.000.000 objetos principales.

Así que necesito mejorar el rendimiento de mi filtro de datetime . Estaba leyendo sobre la marca de tiempo de UNIX (convertir todas las fechas y datetime a la marca de tiempo de UNIX y luego filtrar por este campo de UNIX).

Creo que es una forma mejor que filtrar por datetime y datetime . Pero si alguien sabe de otra manera, lo apreciaría.

Mi consulta es:

SELECT TOP (100)  ev.Title as Event_name, po.Name as POI_name, 
po.Address, po.City, po.Region, po.Country, po.Latitude, po.Longitude, ev.Start_time, 
(Select ID_Category FROM SubCategory s where ev.ID_SubCategory = s.ID_SubCategory) as ID_Category, 
ev.ID_SubCategory, ev.ID_Event, ev.ID_Channel, IDChanelEvent, 
ev.FavoriteCount, po.gmtOffset, v.IsFavorite, v1.IsFavorite  
FROM Events ev 
JOIN POI po ON ev.ID_POI = po.ID_POI 
JOIN (SELECT et.id_event as joinIdEv FROM EventTagLink et, tags t 
 WHERE t.id_tag = et.id_tag 
 AND ( t.Title = N''music'' ) 
 ) as joinEvents 
 ON joinEvents.joinIdEv = ev.ID_Event 
LEFT JOIN Viewed v ON v.ID_Event = ev.ID_Event AND v.ID_User = 1 AND v.IsFavorite = 1 LEFT join Viewed v1 ON v1.ID_Event = ev.ID_Event AND v1.ID_User = 1 AND v1.IsFavorite = 0
WHERE 
--ev.GmtStop_time > ''2013-03-15 14:17:55.188'' AND 
po.Latitude > 41.31423 AND po.Latitude < 61.60511 
AND  po.Longitude > -6.676602 AND po.Longitude < 17.04498  
AND ev.ID_SubCategory in (3, 12, 21, 4, 30, 13, 22, 6, 14, 40, 23, 7, 32, 15, 41, 8, 50, 33, 16, 42, 25, 9, 34, 17, 35, 18, 44, 27, 36, 19, 45, 28, 37, 46, 29, 38, 47, 39, 48, 49, 10, 1, 11, 2, 20) 
--AND ev.GmtStart_time< ''2013-03-15 17:17:55.179''
AND v1.IsFavorite is null

Filtrado por el tiempo que comento.

Si desactivo estos filtros, la duración de la solicitud es de varios segundos. Si los enciendo, la duración de la solicitud es de más de 25 segundos.

• Plan de ejecución con filtrado de fecha y hora.
• Plan de ejecución sin filtro de fecha y hora.

Así que hay mucha discusión sobre planes de ejecución, índices y demás. Pero ¿qué pasa con la marca de tiempo de UNIX , que es la razón principal por la que he puesto la pregunta allí? ¿Mejoraría el rendimiento del filtrado de datetime y datetime ?

Gracias por adelantado.

Answer 1

Crear índice de clúster en el campo de fecha y hora definitivamente ayudará. nos enfrentamos al mismo problema antes. Lo resolvimos creando un índice en la columna datetime.

Answer 2

Para un mejor rendimiento le sugiero que cree nuevos índices:

CREATE INDEX x1 ON LiveCity.dbo.Tags(Title) INCLUDE(ID_Tag)
CREATE INDEX x2 ON LiveCity.dbo.Tags(ID_Event, GmtStart_time, GmtStop_time) 
  INCLUDE(
          FavoriteCount, 
          ID_Channel, 
          ID_POI, 
          ID_SubCategory, 
          IDChanelEvent, 
          Start_time, 
          Title
          )
CREATE INDEX x ON LiveCity.dbo.POI(ID_POI, Latitude, Longitude) 
  INCLUDE(
          Address, 
          City, 
          Country, 
          gmtOffset, 
          Name, 
          Region
          )

Esto le ayudará a evitar la operación de búsqueda de RID y mejorar el rendimiento general de la consulta.

Answer 3

Primero debe ver su plan de ejecución para ver qué está haciendo SQL Server. Más que probable, solo necesitas agregar un índice. Pequeñas conversiones como esta casi nunca son la razón por la cual su consulta es lenta. Los índices son una buena primera parada para arreglar consultas.

No es necesario que haga de este el índice agrupado. Convertirlo en el índice agrupado significa que no necesita hacer una búsqueda, pero para solo 100 filas, la búsqueda es muy rápida. Yo pondría datetime y subcategoría en un índice no agrupado, en ese orden.

Si realiza el pedido, también debe asegurarse de que esté en un índice. Dado que solo tiene sentido usar un índice por tabla, deberá asegurarse de que todas las columnas relevantes estén en el mismo índice, en el orden correcto.

Pero primero, obtenga su plan de ejecución real!

Answer 4

Prueba este

;WITH cte AS (
     SELECT IsFavorite, ID_Event  
     FROM Viewed
     WHERE ID_User = 1 
)
SELECT TOP (100)
      Event_name = ev.Title 
    , POI_name = po.Name 
    , po.[address]
    , po.City
    , po.Region
    , po.Country
    , po.Latitude
    , po.Longitude
    , ev.start_time
    , s.ID_Category
    , ev.ID_SubCategory
    , ev.ID_Event
    , ev.ID_Channel
    , IDChanelEvent
    , ev.FavoriteCount
    , po.gmtOffset
    , v.IsFavorite
    , IsFavorite = NULL
FROM [events] ev
JOIN POI po ON ev.ID_POI = po.ID_POI
LEFT JOIN SubCategory s ON ev.ID_SubCategory = s.ID_SubCategory
LEFT JOIN cte v ON v.ID_Event = ev.ID_Event AND v.IsFavorite = 1
WHERE po.Latitude BETWEEN 41.31423 AND 61.60511
     AND po.Longitude BETWEEN -6.676602 AND 17.04498
     AND ev.ID_SubCategory IN (3, 12, 21, 4, 30, 13, 22, 6, 14, 40, 23, 7, 32, 15, 41, 8, 50, 33, 16, 42, 25, 9, 34, 17, 35, 18, 44, 27, 36, 19, 45, 28, 37, 46, 29, 38, 47, 39, 48, 49, 10, 1, 11, 2, 20)
     AND v1.IsFavorite IS NULL
     AND EXISTS(
          SELECT 1 
          FROM EventTagLink et
          WHERE t.Title = ''music''
               AND et.joinIdEv = ev.ID_Event
     )
     AND NOT EXISTS (
          SELECT * 
          FROM cte v1 
          WHERE v1.ID_Event = ev.ID_Event AND v1.IsFavorite = 0
     )

Answer 5

Solo una sugerencia cuando se trata de índices en datetime en msql es que la huella del índice afecta los tiempos de búsqueda (Sí, esto parece obvio ... pero lea hacia adelante).

La importancia de esto cuando se indexa en la fecha y hora dice, por ejemplo, ''2015-06-05 22: 47: 20.102'' el índice debe tener en cuenta cada lugar dentro de la fecha y hora. Esto se vuelve muy grande espacialmente y voluminoso. Un enfoque exitoso que he aprovechado es crear una nueva columna de fecha y hora y completar los datos al redondear la hora a la hora y luego construir el índice en esta nueva columna. El ejemplo ''2015-06-05 22: 47: 20.102'' se traduce como ''2015-06-05 22: 00: 00.000''. Al adoptar este enfoque, dejamos los datos detallados solos y podemos mostrarlos o usarlos mediante la búsqueda en esta nueva columna que nos brinda un retorno de aproximadamente 10x (como mínimo) sobre la rapidez con la que se obtienen los resultados. Esto se debe al hecho de que el índice no tiene que tener en cuenta los campos de minutos, segundos y milisegundos.