如何提高 SQL Server 中日期时间过滤的性能? [英] How to improve performance for datetime filtering in SQL Server?
问题描述
我在按 datetime 列过滤时遇到问题.
I have a problem with filtering by datetime columns.
我试过这两种方法:
datefield < '2013-03-15 17:17:55.179'
datefield < CAST('2013-03-15 17:17:55.179' AS datetime)
我有一个包含超过 3.000.000 个主要对象的大型数据库.
I have a large database with over 3.000.000 main objects.
所以我需要提高 datetime 过滤的性能.我正在阅读有关 UNIX 时间戳的信息(将所有 datetime 转换为 UNIX 时间戳，然后按此 UNIX 字段过滤).
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).
我认为这比按 datetime 过滤更好.但如果有人知道其他方式，我将不胜感激.
I think it's a better way than filtering by datetime. But if anyone knows some other way, I would appreciate it.
我的查询是:
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 秒.
If I turn off these filters, request duration is several seconds. If I turn them on then request duration is over 25 seconds.
所以有很多关于执行计划、索引等的讨论.但是 UNIX 时间戳 怎么样，这是我提出这个问题的主要原因.它会提高 datetime 过滤的性能吗?
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?
提前致谢.
推荐答案
当涉及到 msql 中日期时间的索引时，一个建议是索引足迹会影响搜索时间(是的，这似乎很明显……但请继续阅读).
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).
在日期时间编制索引时对此的重要性说例如2015-06-05 22:47:20.102"，索引必须考虑日期时间内的每个位置.这在空间上变得非常大和笨重.我利用的一个成功方法是创建一个新的日期时间列并通过将时间四舍五入到小时来填充数据，然后在这个新列上构建索引.示例2015-06-05 22:47:20.102"转换为2015-06-05 22:00:00.000".通过采用这种方法，我们可以单独保留详细数据，并可以通过在这个新列上搜索来显示或使用它，这使我们在返回结果的速度方面获得大约 10 倍(至少)的回报.这是因为索引不必考虑分钟、秒和毫秒字段.
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.
这篇关于如何提高 SQL Server 中日期时间过滤的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
