复杂查询性能

复杂查询性能

      过去内存数据库几乎专门用于处理简单查询的领域，但是，现在的趋势是内存数据库已经扩展到复杂查询领域，比如说记账应用，CRM等等。

      尽管其他内存DBMS趋向于简单查询处理的性能优化，但是Altibase内存DBMS也注重适合复杂应用的复杂处理的性能。在 TPC-H基准测试中通过22个查询进行的测试可以让我们看到dbms在复杂查询应用中的性能。下图显示了在TPC-H测试中Altibase相对于其他传统DBMS的性能优势。

[TPC-H性能图：Altibase 3 vs 磁盘DBMS]

NO.	查询概要
Q 1	index scan on 6 millon records, 8 aggregation operation(including SUM, AVG, COUNT) 2 columns group by, 2 columns order by
Q 2	5 join(200,000, 10,000, 800,000, 25, 5 records respectively) in outer query, 4 join(800,000, 10,000, 25 ,5 records respectively) in subquery, 4 columns order by, outer column reference (join predicate)
Q 3	3 join(150,000,1500,000,6000,000 records respectively), sum(expression), 3 columns group by, 2 columns order by, limit
Q 4	count(*), 1 column group by, 1 column order by, exists subquery(6,000,000 records), outer column reference
Q 5	6 join(150,000, 1,500,000, 6,000,000, 10,000, 25, 5 records respectively), sum(expression), 1 column group by, 1 column order by
Q 6	sum(expression), index scan on 6,000,000 records
Q 7	inline view, 6 join(10,000, 6,000,000, 1,500,000, 150,000, 25, 5 records respectively), both AND/OR are used in predicates, 3 columns group by, 3 columns order by, SUM
Q 8	8 join(200,000, 10,000, 6,000,000, 1,500,000, 150,000, 25, 25, 5 records respectively), inline view, 1 column group by, 1 column order by, aggregation
Q 9	star query, 6 join(200,000, 10,000, 6,000,000, 800,000, 1,500,000, 25 records respectively), inline view, 2 columns group by, 2 columns order by, like predicate
Q 10	4 join(150,000, 1,500,000, 6,000,000, 25 records respectively), 7 column group by, 1 column order by, aggregation, limit
Q 11	3 join(800,000, 10,000, 25 records respectively) in outer query, 3 join(800,000, 10,000, 25 records respectively) in subquery, 1 column group by, having, 1 column order by, aggregation
Q 12	2 join(1,500,000, 6,000,000 records respectively), 1 column group by, 1 column order by, aggregation
Q 13	left outer join(150,000, 1,500,000 records respectively), inline view, 1 column group by, 2 column order by, like predicate, aggregation
Q 14	2 join(6,000,000, 200,000 records respectively), aggregation, many expression in target
Q 15	create view => inline view, subquery, 1 column order by, aggregation
Q 16	2 join(800,000, 200,000), in predicates, like predicate, 3 columns group by, 4 columns order by, count(distinct), subquery
Q 17	2 join(6,000,000, 200,000 records respectively), subquery(6,000,000 records), outer column reference (join predicate), aggregation
Q 18	3 join(150,000, 1,500,000, 6,000,000 records respectively), subquery(6,000,000 records), 5 columns group by, having, 2 columns order by, aggregation, limit
Q 19	Using complex predicate (use of numerous AND, OR, in predicates), 2 join(6,000,000, 200,000 records respectively), aggregation
Q 20	2 join, numerous subquery, outer column reference, 1 column order by, aggregation
Q 21	4 join(10,000, 6,000,000, 1,500,000, 25), exists/not exists subquery(6,000,000), outer column reference, 1 column group by, 2 columns order by
Q 22	Using complex predicate (in predicate, substring, not exists subquery), 1 column group by, 1 column order by, aggregation

[TPC-H查询概要]

简单查询测试

下表中是Altibase简单查询测试结果

服务器配置：Sun E3500 4CPU X 400MHz 1GB内存
应用类型：客户端-服务器


可伸缩性

       Altibase DBMS在客户端-服务器架构中也有非常好的可伸缩性。像下面可伸缩性图显示的，客户端的数量增加时吞吐量也增加，直到客户端的数量达到CPU数量的2.5倍。然后即使客户端的数量再增加，吞吐量也保持平稳。

* 服务器配置：Sun E450，2CPU，2GB内存，Solaris 2.7