如何提高SQL Server中日期时间过滤的性能？

I have a problem with filtering by datetime columns.

我有按日期时间列过滤的问题。

I tried these two methods:

我尝试了这两种方法：

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

I have a large database with over 3.000.000 main objects.

我有一个超过3.000.000主要对象的大型数据库。

So I need to improve performance for my datetime filtering. I was reading about UNIX timestamp (convert all datetime to UNIX timestamp and then filter by this UNIX field).

所以我需要提高日期时间过滤的性能。我正在阅读有关UNIX时间戳的信息（将所有日期时间转换为UNIX时间戳，然后按此UNIX字段进行过滤）。

I think it's a better way than filtering by datetime. But if anyone knows some other way, I would appreciate it.

我认为这比通过datetime过滤更好。但如果有人知道其他方式，我会很感激。

My query is:

我的查询是：

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

filtering by the time I commented.

过滤我评论的时间。

If I turn off these filters, request duration is several seconds. If I turn them on then request duration is over 25 seconds.

如果我关闭这些过滤器，请求持续时间是几秒钟。如果我打开它们，则请求持续时间超过25秒。

Execution plan with filtering datetime
具有过滤日期时间的执行计划
Execution plan without datetime filter
没有日期时间过滤器的执行计划

So there is a lot of discussion about execute plans, indexes and so on. But what about UNIX timestamp, which is the main reason why I've put the question there. Would it improve performance for datetime filtering?

因此，有很多关于执行计划，索引等的讨论。但是UNIX时间戳呢，这是我把问题放在那里的主要原因。它会改善日期时间过滤的性能吗？

Thanks in advance.

提前致谢。

5 个解决方案

#1

Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious...but please read onward).

关于msql中datetime上的索引的一个建议是索引占用空间会影响搜索时间（是的，这似乎很明显......但请继续阅读）。

The importances to this when indexing on the datetime say for instance '2015-06-05 22:47:20.102' the index has to account for every place within the datetime. This becomes very large spatially and bulky. A successful approach that I've leveraged is create a new datetime column and populate the data by rounding the time to the hour and then building the index upon this new column. Example '2015-06-05 22:47:20.102' translates to '2015-06-05 22:00:00.000'. By taking this approach we leave the detailed data alone and can display it or use it by search on this new column which gives us approximately a 10x (at minimum) return on how fast results are returned. This is due to the fact that the index doesn't have to account for the minutes, seconds and millisecond fields.

在对日期时间进行索引时对此的重要性例如说“2015-06-05 22：47：20.102”索引必须考虑日期时间内的每个地方。这在空间和体积上变得非常大。我利用的一个成功方法是创建一个新的日期时间列，并通过将时间四舍五入到该小时来填充数据，然后在此新列上构建索引。示例'2015-06-05 22：47：20.102'转换为'2015-06-05 22：00：00.000'。通过采用这种方法，我们可以单独保留详细数据，并可以通过搜索这个新列来显示或使用它，这使得我们返回结果的速度大约为10倍（至少）。这是因为索引不必考虑分钟，秒和毫秒字段。

#2

You need to look at your execution plan first to see what SQL Server is doing. More than likely, you just need add an index. Little conversions like this are almost never the reason why your query is slow. Indices are a good first stop for fixing queries.

您需要先查看执行计划，看看SQL Server正在做什么。更有可能的是，您只需要添加一个索引。像这样的少量转换几乎不是你的查询速度慢的原因。指数是修复查询的良好第一站。

You don't need to make this the clustered index. Making it the clustered index means that you don't need to do a lookup, but for only 100 rows, lookup is very fast. I would put datetime and subcategory into a nonclustered index, in that order.

您不需要将其设为聚簇索引。使其成为聚簇索引意味着您不需要进行查找，但只有100行，查找速度非常快。我会按日程将datetime和subcategory放入非聚集索引中。

If you are ordering, you should also make sure that's in an index. Since it only makes sense to use one index per table, you'll need to make sure all the relevant columns are in the same index, in the right order.

如果您要订购，您还应该确保它在索引中。由于每个表使用一个索引才有意义，因此您需要确保所有相关列按正确的顺序位于同一索引中。

But first, get your actual execution plan!

但首先，得到你的实际执行计划！

#3

For better performance I suggest you create new indexes:

为了获得更好的性能，我建议您创建新索引：

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
          )

This will help you avoid RID lookup operation and improve the overall performance of the query.

这将帮助您避免RID查找操作并提高查询的整体性能。

#4

Try this one -

试试这个 -

;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
     )

#5

-5

Create cluster index on datetime field it will definitely help . we faced same problem earlier . we solved it by creating index on datetime column .

在datetime字段上创建集群索引肯定会有所帮助。我们之前遇到过同样的问题我们通过在datetime列上创建索引来解决它。

#1