Re: Alias for arguments in TABLE function
Hi,
You could do the following prepared statement :
SELECT * FROM TABLE(ID NUMBER=?)
then you can set the parameter with a setObject(1,new Object[]
{1,2,3,4,5,6,7,8,9,10})
If you really want to do an external java function (though I do not
understand why), I suppose you could write
SELECT * FROM TABLE(ID NUMBER=GET_NUMBERS())
...and the function must return an array. But I'm not sure if it is
possible to do that.
LP
2012/4/27 Łukasz Drumiński
> Hello,
>
> this is working fine:
>
> SELECT * FROM TABLE(ID NUMBER=(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))
>
> but I need to do something like this:
>
> SELECT * FROM TABLE(GET_NUMBERS())
>
> is this posible?
>
> I could create alias GET_NUMBERS for some Java method but I don't know
> how this method should look. I will be grateful for any help.
>
> Regards,
> Lukas
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-database@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: Aggregate performance issue
Hi Thomas, Thanks for the answer (I didnt see it at first). I'm just testing every possible query plans for a study and I have also an accumulated buffer that can do the trick as well. Any thoughts on why the group sorted has a poorer performance? Is there a way to override the choices? Thanks LP On 16 fév, 20:22, Thomas Mueller wrote: > Hi, > > Large result sets are buffered on disk (in a temporary table or temporary > file). > > What you could do is create a separate table where you keep the sum of the > value, plus the count, grouped by id. The table would need to be updated > using a trigger. That way, you don't need to read all rows every time. > > Regards, > Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Aggregate performance issue
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 h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: Optimize group by on simple nested queries
Hey, Ok, I'm gonna try to find a work around in order to rewrite the sql query. Thanks anyway ! LP Le 6 févr. 2012 à 08:41, Thomas Mueller a écrit : > Hi, > > I'm afraid it would be quite hard to implement this optimization. Currently I > wouldn't know to do it I'm afraid. > > Regards, > Thomas > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to h2-database@googlegroups.com. > To unsubscribe from this group, send email to > h2-database+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Optimize group by on simple nested queries
Hi everyone, I tried to evaluate the following SQL expression : SELECT a, AVG(b) FROM (SELECT a,b FROM Table) GROUP BY a. I know it is a bit silly as the expression is clearly (for a human) equivalent to SELECT a, AVG(b) FROM Table GROUP BY a. Unfortunately, the sql is generated from a program (in a very straightforward way), then prepared in jdbc and executed several times after. When I'm executing only conditions and joins it works like a charm, it flattens everything and uses the right indexes. But if I try to use a group by, the explain tells me that it performs a FullScan (and therefore do not use the index on a). Note that the problem is the same for views. If I define a view, even a simple one, I can't use the original index on them to perform a group by. I tried to look into the code but I only found out that the group by is performed on a TableView which does not have indexes (getIndexes which is used to determine if a sorted group will be performed). Is there any way to fix this ? Or at least where to look for ? Regards LP -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
