[h2] H2 (embedded) uses a lot of memory for large Union Statement
Hi, H2 SQL statements containing a lof of UNION ALL expressions (top level) consume a lot of memory, especially when the unioned SELECT queries are quite large (like for generated statement that we use). According to JProfiler, this is caused because the H2 SelectUnion objects reference each other in a row: Currently, my workaround is a table function, that receives the SQL statement texts to union. The table function returns a result set that executes all queries in a row and creates the next resultset when a previous one was completely read. The runtime of both union all versions is equal, but using the table function, only the single statements must fit into memory, but not the full cascade of SelectUnion objects. Maybe there is a possibility to updates H2's handling of UNION ALL statements so that they require less memory? Kind regards Christoff Schmitz Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Slow insert on index vs hsqldb
I don't think hsqldb supports on-disk indexes, it's indexes are always stored only in RAM, so it has less disk IO to perform. (but I could be wrong, corrections welcome). At the moment, H2 does not support memory-only indexes on disk-based data (but patches are welcome) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Missing jar file in maven repository
Hi, I'm trying to use the h2 database within a gradle project. Unfortunately there is no jar file into the respective repo (http://search.maven.org/#search%7Cga%7C1%7Cg%3A%22com.h2database%22). Has anybody a useful hint about this error? Thanks, Juergen -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Missing jar file in maven repository
Le 22/05/2015 15:03, Eric Chatellier a écrit : search UI bug. Or invalid pom file : http://repo1.maven.org/maven2/com/h2database/h2/1.4.187/h2-1.4.187.pom -- Éric Chatellier - www.codelutin.com - 02.40.50.29.28 -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Missing jar file in maven repository
Le 22/05/2015 13:57, Jürgen Henning a écrit : Hi, I'm trying to use the h2 database within a gradle project. Unfortunately there is no jar file into the respective repo (http://search.maven.org/#search%7Cga%7C1%7Cg%3A%22com.h2database%22). Has anybody a useful hint about this error? search UI bug. jar does exists : http://repo1.maven.org/maven2/com/h2database/h2/1.4.187/ -- Éric Chatellier - www.codelutin.com - 02.40.50.29.28 -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Erroraccessingdatabase
Hi, I'm new to H2, and I have to work with an pre-existing h2 database. So basically, when I first access the database through the console, I manage to log in. However, after disconnecting, and trying to re-access it, I get the following error: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.PPT_TEMPLATE ADD CONSTRAINT PUBLIC.CONSTRAINT_21 FOREIGN KEY(ID_TEMPLATE) REFERENCES PUBLIC.PPT_TEMPLATE(ID_TEMPLATE) NOCHECK [23505-176] The only way for to re-access it is to get a fresh copy in git, but everytime, after I access it, I won't be able to re-access it... Can anyone pinpoint me to where I may be able to solve this issue? My database version is 1.3.176 (I took the version shown on the tab when I open the database through the H2 console tab) My URL is: jdbc:h2:C:\Users\Carson\db\PPT_DB;CIPHER=AES -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX error
Hi, I'm new to H2, and I'm working with an already existing H2 database. When I first run H2 Console and access the database, I manage to get in. However, after I disconnect, and try to re-access the database, the following error occurs: Unique index or primary key violation: PRIMARY KEY ON .PAGE_INDEX; SQL statement: ALTER TABLE PUBLIC.PPT_TEMPLATE ADD CONSTRAINT PUBLIC.CONSTRAINT_21 FOREIGN KEY(ID_TEMPLATE) REFERENCES PUBLIC.PPT_TEMPLATE(ID_TEMPLATE) NOCHECK [23505-176] I would need to get a fresh copy of the db from my git to be able to 're-access' the database. My URL is the following: jdbc:h2:C:\Users\carson\.db\PPT_DB;CIPHER=AES My H2 version is, I think 1.3.176 (it's the version I see on the H2 console tab) Do you guys know why this is occurring? -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Slow insert on index vs hsqldb
Hi, I think in this case, HSQLDB keeps the index on disk. I ran the test on my machine and it looks like there is a problem in H2 here when using the PageStore (which is the default for the version of H2 you are using). With a recent version of H2 (with the MVStore), it is much better, even thought initially inserts are slower. This I will still need to work on. As for indexing randomly distributed data, a fast way is to do it chunk by chunk, where a chunk fits in memory. Basically, sort the chunk, store it, then process the next chunk, and so on. Once you are done, sort the chunks using merge sort. This is what I did in the ArchiveTool: https://github.com/h2database/h2database/blob/master/h2/src/tools/org/h2/dev/fs/ArchiveTool.java - there is also a version that use the MVStore: https://github.com/h2database/h2database/blob/master/h2/src/tools/org/h2/dev/fs/ArchiveToolStore.java With revent versions of H2, with the MVStore, this algorithm is used if you create an index after inserting the data. So if you move create index after inserting, then inserting is much faster. Creating the index is also fast. Regards, Thomas On Fri, May 22, 2015 at 1:55 PM, Noel Grandin noelgran...@gmail.com wrote: I don't think hsqldb supports on-disk indexes, it's indexes are always stored only in RAM, so it has less disk IO to perform. (but I could be wrong, corrections welcome). At the moment, H2 does not support memory-only indexes on disk-based data (but patches are welcome) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. 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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Congrats on MVStore!
Hey! I am the author of http://prevayler.org Some of you might know it. I am currently working on http://sneer.me , a P2P app platform. (Think Facebook, Twitter and Google but without a central server) I found within H2 a precious gem called MVStore :) It is perfect for my needs: embedding it in an Android app. Could you release MVStore it as its own independent maven package? That would be really useful. Thanks, Klaus -- 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+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
