如何提高SQL Server中日期时间筛选的性能？

hs1rzwqc 于 2022-12-26 发布在 SQL Server

关注(0)|答案(5)|浏览(208)

按datetime列筛选时遇到问题。
我尝试了这两种方法：

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

我有一个大型的数据库，有超过3.000.000个主要对象。
所以我需要提高我的datetime过滤的性能。我阅读到了UNIX时间戳（将所有datetime转换为UNIX时间戳，然后通过这个UNIX字段过滤）。
我认为这是一个比datetime过滤更好的方法，但是如果有人知道其他方法，我会很感激。
我的疑问是：

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

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

因此，关于执行计划、索引等有很多讨论。但是，UNIX时间戳怎么样呢？这是我提出这个问题的主要原因。它会提高datetime过滤的性能吗？

sql-server

来源：https://stackoverflow.com/questions/17381875/how-to-improve-performance-for-datetime-filtering-in-sql-server

5条答案

按热度按时间

c2e8gylq1#

当谈到msql中datetime上的索引时，一个建议是索引占用量会影响搜索时间（是的，这看起来很明显......但请继续阅读）。
当在日期时间上建立索引时，这一点很重要，例如'2015-06-05 22：47：20.102'，索引必须考虑到日期时间内的每一个位置。这会使空间变得非常大，非常笨重。我利用的一个成功方法是创建一个新的日期时间列，并通过将时间舍入到小时来填充数据，然后在这个新列上构建索引。例如'2015 - 06 - 05 22：47：20.102'转换为'2015-06-05 22：00：00.000'。通过采用这种方法，我们可以不考虑详细数据，并可以显示它或通过在这个新列上搜索来使用它，这给我们提供了大约10倍（至少）的返回结果的速度。这是由于索引不必考虑分钟、秒和毫秒字段。

赞(0）回复(0）举报 2022-12-26

ufj5ltwl2#

您需要先查看执行计划，以了解SQL Server正在执行的操作。您很可能只需要添加一个索引。像这样的小转换几乎永远不是查询速度慢的原因。索引是修复查询的好的第一站。
你不需要把它设为聚集索引。把它设为聚集索引意味着你不需要做查找，但是对于仅仅100行，查找是非常快的。我会把datetime和subcategory按这个顺序放进一个非聚集索引。
如果你要排序，你也应该确保它在索引中，因为每个表只使用一个索引是有意义的，你需要确保所有相关的列都在同一个索引中，并且顺序正确。
但首先，得到你的实际执行计划！

赞(0）回复(0）举报 2022-12-26

9gm1akwq3#

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

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
          )

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

赞(0）回复(0）举报 2022-12-26

xriantvc4#

试试这个-

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

赞(0）回复(0）举报 2022-12-26

t5fffqht5#

在datetime字段上创建簇索引肯定会有帮助。我们之前遇到过同样的问题。我们通过在datetime列上创建索引解决了这个问题。

赞(0）回复(0）举报 2022-12-26