我在过滤时遇到问题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 秒。
- 具有过滤日期时间的执行计划 https://www.dropbox.com/s/xjnu5om2r7yru04/execution_plan.sqlplan
- 没有日期时间过滤器的执行计划 https://www.dropbox.com/s/0ab15b7nuamlw8s/execution_plan_without_datefilter.sqlplan
所以有很多关于执行计划、索引等的讨论。但是关于UNIX 时间戳,这就是我在那里提出问题的主要原因。它会提高性能吗datetime
过滤?