Any thoughts about server-side cursors?

[Steve Ash]

I am using H2 to write millions of records into two tables that I then need to re-read back out.  The creation of the temp file to store the result set is taking about 90 minutes (its a 16gb database) and the utilization is really low during this as its just waiting on very slow sequential I/O.  I know that server side cursors are on the roadmap and that they are complicated.  Are those still "in progress" or are they probably never really going to happen?  

So in thinking through my options- I guess I could "partition" the resultsets at insert time by some kind of identifier, and then pull out a partition at a time such that a single partition was small enough to not trigger the temp db creation?  Is there anything else?  A lower-level API that I could read each b-tree independantly and hand-roll my own joins with lots of prefecthing for the inner join bookmark lookups?  reading will happen on a single thread and nothing else will use this database while I'm scrolling through these results so its simpler than general server side cursors.  Any other options?

Thanks,

Steve

[Thomas Mueller]

Hi,

The current plan for server side cursors is use them once the storage engine is switched to the MVStore. That way, the old data will be read, without having to copy data, and without having to prevent writes while reading. Unfortunately, it will take a while until the MVStore is integrated.

I believe for the moment, the best solution is to use some kind of paging yourself, just like you have described.

I know the temp file (or temp table) is very problematic. Just for completeness (I guess it doesn't apply in your case) in some cases a temporary file / table need to be created , for example if the data needs to be sorted or grouped.

> A lower-level API that I could read each b-tree independantly and hand-roll my own joins

Or you could directly use the MVStore... but I guess it is too much "work in progress" currently.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/LtxqfikevqAJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Steve Ash]

Thanks Thomas- one follow up.  Does it make sense that another query that I issue which doesn't have an order by...but is still doing a join-- doesn't produce the temporary table?  Do you _always_ create a temp table when the number of rows are larger than the buffer, or are there some "convenient cases" where you don't?  My other big query doesn't seem to create a temp table.  Perhaps I should investigate further.

steve