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 On Mon, Nov 5, 2012 at 3:57 PM, Thomas Mueller <[email protected] > wrote: > 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 > > > > > On Mon, Nov 5, 2012 at 4:31 PM, Steve Ash <[email protected]> wrote: > >> 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 >> >> -- >> 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 [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. >> > > -- > 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. > -- 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.
