根据日期范围查询的SQL性能让你失望? 我有一个比较特别的技巧,可能会帮助你解决这个问题! (SQL开发人员讨厌这个!)*
如果你有一个类,在添加数据时记录时间戳,那么这些数据将与你的IDKEY值保持顺序--也就是说,当且仅当ID1<ID2时,表内所有ID和时间戳值的TimeStamp1<TimeStamp2--那么你可以利用这一知识来提高对时间戳范围的查询性能。 考虑一下下面这个表:
Class User.TSOrder extends %Persistent
{
Property TS as %TimeStamp;
Property Data as %String (MAXLEN=100, MINLEN=200);
Index TSIdx on TS;
Index Extent [type=bitmap, extent];
}
用过去30天内的30,000,000条随机行来填充,每天将得到1,000,000条行。 现在,如果我们想查询某一天的信息,你可以这样写:
SELECT ID, TS, Data
FROM TSOrder
WHERE
TS >= '2016-07-01 00:00:00.00000' AND
TS <= '2016-07-01 23:59:59.999999'
可以肯定的是,这是一个合理的查询。 然而,在我的系统上,这需要2,172,792个Global引用和7.2秒。 但是,知道ID和时间戳的顺序是一样的,我们就可以用时间戳来获得ID范围。 考虑一下下面的查询:
SELECT ID, TS, Data
FROM TSOrder
WHERE
ID >= (SELECT TOP 1 ID FROM TSOrder WHERE TS >='2016-07-01 00:00:00.00000' ORDER BY TS ASC) AND
ID <= (SELECT TOP 1 ID FROM TSOrder WHERE TS <='2016-07-01 23:59:59.999999' ORDER BY TS DESC)
新的查询在5.1秒内完成,只需要999,985个Global引用**!
这种技术可以更实际地应用于有更多索引字段的表和有多个WHERE子句的查询。 从子查询中产生的ID范围可以被放到位图格式中,当你得到一个多索引的解决方案时,会产生惊人的速度。 Ens.MessageHeader表是一个很好的例子,你可以把这个技巧用于实际工作中。
我们把话说清楚--这只是一个性能提升的例子。 如果你在同一个表中的WHERE子句中有许多条件(而且它们是有索引的),那么这个技术可以给你带来更大的提升,在你的查询中试试吧!
* SQL开发人员并不真的讨厌这个,但如果互联网教会了我们什么,那就是抓眼球的广告语会带来更多的流量。
** 当测试返回这么多行的查询时,SMP无法处理,大部分的时间都花在了显示数据上。 正确的测试方法是使用嵌入式或动态SQL,运行结果,但不输出它们的时间,并使用SQL Shell进行Global计数。 你也可以用SQL Stats来做这个。