Hi, I would probably try with the following logic:
select _rowid_, * from test_update_perf where _rowid_ > ? order by _rowid_ limit 100 At the beginning, use -1, then the last _rowid_ from the last result, and so on. This works independently of the primary key: It uses the internal unique row id, which may or may not be the primary key, depending on the table definition. 100 is just an example, you may want to test with 10, 100, 1000, and so on. Regards, Thomas On Friday, March 13, 2015, Nicolas Fortin (OrbisGIS) <[email protected]> wrote: > Hi, > > > At some point, server side cursors will be implemented, then you will just >> have to run "select * from tableName" and you are done. This will require >> some changes however; it can not be supported when using the PageStore. I >> expect it will take a while until this is available. >> > > Ho great ! But we use h2 95% of time in embedded mode.Cursors will be > available also in h2 embedded ? > > Are you sure the bottleneck is evaluating the condition? That's weird. How >> did you test it? Do you have a simple test case? >> > > We call hundred of time something like this "SELECT * FROM MYTABLE WHERE > PK IN (? , ? , .." in a prepared statement. I'm not sure if evaluating the > query condition is the bottleneck of the operation, but I'm talking about > the overall process of communicating primary keys to engine. > > It might be slightly faster to use some internal API, depending on what >> you want to do with the data. For example, "create table ... as select" >> uses a direct API that doesn't buffer rows. Possibly even faster would be >> to access the MVStore directly (if the data is stored there). But such >> solutions are harder to maintain, and might not help all that much. >> > > Yes it is a good idea. We could cast dbobjects up to Table and ScanIndex > interface then use this direct access. > > Thanks for your support. > > Regards, > > -- > > Nicolas Fortin > IRSTV FR CNRS 2488 > GIS http://orbisgis.org > Spatial DB http://h2gis.org > Noise http://noisemap.orbisgis.org > > Le jeudi 12 mars 2015 18:50:11 UTC+1, Thomas Mueller a écrit : >> >> Hi, >> >> At some point, server side cursors will be implemented, then you will >> just have to run "select * from tableName" and you are done. This will >> require some changes however; it can not be supported when using the >> PageStore. I expect it will take a while until this is available. >> >> we are caching _ROWID_ or primary keys if available, then query a batch >>> of rows using the keys in the where clause. >>> >> >> That's what I would do as well. >> >> This is quite slow as the query condition has to be evaluated by the h2 >>> engine. >>> >> >> Are you sure the bottleneck is evaluating the condition? That's weird. >> How did you test it? Do you have a simple test case? >> >> It might be slightly faster to use some internal API, depending on what >> you want to do with the data. For example, "create table ... as select" >> uses a direct API that doesn't buffer rows. Possibly even faster would be >> to access the MVStore directly (if the data is stored there). But such >> solutions are harder to maintain, and might not help all that much. >> >> Regards, >> Thomas >> >> -- > 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 [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
