文章
Michael Lei · 八月 7 阅读大约需 2 分钟

最佳实践之改善日期范围查询的SQL性能

根据日期范围查询的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来做这个。

0
0 30
讨论 (0)1
登录或注册以继续