H2 query taking a long time on "SELECT * FROM test" , and ResultSet not streaming from table as sqlite does

[IRON ALEKS]

The problem is that when i query "SELECT * FROM test" with a table of 10 million rows, it takes like 20 seconds and then it returns  the ResultSet, but when in sqlite i make the same query, it returns the ResultSet immediately and it seems to stream the rows, but H2 doesn't seem to stream the rowsl, is it me doing something wrong ?

[Noel Grandin]

we don't support "row streaming" unless you use the rather new 

LAZY_QUERY_EXECUTION feature (add ";LAZY_QUERY_EXECUTION=1") to your URL.

In general, trying to retrieve 10 million rows in one result set is not good idea.

​

[Sergi Vladykin]

By default H2 actively generates a result set into a separate place, then you can iterate over it. So yes, this will be slow for large tables. 

Lately we have introduced a new experimental setting for lazy query execution: 

SET LAZY_QUERY_EXECUTION TRUE;

With this setting enabled result set will be returned right away and query execution will happen lazily step by step on each call of method ResulSet.next().

There are few restrictions though:

1. This works only in embedded mode.

2. You must avoid doing anything with the connection that have open lazy result set.

3. You must be very careful with closing result sets, statements and connections, otherwise you easily may hit some deadlocks, leaks or other buggy behaviors.

Sergi

2017-07-23 15:08 GMT+03:00 IRON ALEKS <8iron...@gmail.com>:

The problem is that when i query "SELECT * FROM test" with a table of 10 million rows, it takes like 20 seconds and then it returns  the ResultSet, but when in sqlite i make the same query, it returns the ResultSet immediately and it seems to stream the rows, but H2 doesn't seem to stream the rowsl, is it me doing something wrong ?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Noel Grandin]

@sergi, any change you could work up some documenation for your nice new feature?

maybe in advanced.html and the help.csv lang reference?

​

[Sergi Vladykin]

Sure, I will document it.

Sergi

2017-07-23 16:44 GMT+03:00 Noel Grandin <noelg...@gmail.com>:

@sergi, any change you could work up some documenation for your nice new feature?

maybe in advanced.html and the help.csv lang reference?

​

--