Hi, I'm currently experimenting performance issues with the aggregate operation.
Here is my data : A historic of values for different ids CREATE TABLE Bench (id INT NOT NULL, value INT NOT NULL, T TIMESTAMP NOT NULL) Thereafter I insert a lot of data in it (generated). My parameter for generation are : the number of groups (COUNT(DISTINCT id) if you prefer) and the size of each group (COUNT(value) GRP id) I now want to perform the following query SELECT id, AVG(value) as avg FROM Bench GROUP BY id Now: if I create an index on id, I've got a lesser performance (about x2 on computing time). You can find the results here : (computing time is in millisec) http://petitl.fr/h2bench/WithIndex.txt http://petitl.fr/h2bench/WithoutIndex.txt Therefore the *group sorted* choice on the optimizer is not a valid option when no filtering is applied. Also, the performance is approximately linear on the cardinality (which is normal) but when the size is above 5 million (there are 3 examples in the bench) the computing time explodes. Any particular reason ? Maybe the Java GC ? My setting: Ext4 hard-drive disk (7200rpm) H2: 1.3.163 Thanks LP -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
