[h2] Re: JDBC getIndexInfo for large table is very slow
Right! Thanks Evgenij, I will change that immediately. Thanks again guys. On Friday, 30 September 2022 at 12:57:50 UTC+2 Evgenij Ryazanov wrote: > Hello! > > You need to pass true as the last argument (approximate). It allows to > return a fast approximation instead of exact number of rows in the index. > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/687ad393-014d-4ef4-b507-cf5c02643600n%40googlegroups.com.
[h2] Re: JDBC getIndexInfo for large table is very slow
Hello! You need to pass true as the last argument (approximate). It allows to return a fast approximation instead of exact number of rows in the index. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/29742cd3-9bed-4c9e-b8c2-619c1f162cc2n%40googlegroups.com.
Re: [h2] JDBC getIndexInfo for large table is very slow
BTW: there are usually about 30 tables. Some approaching 100K rows, the big one being the only that goes into the millions. On Friday, 30 September 2022 at 12:52:49 UTC+2 Silvio wrote: > Thanks Noel, > > Also see my other remark. I will look into that table. Actually, we did > some stuff directly on the INFORMATION_SCHEMA tables before but the H2 > versions above 200 had some modified layout/content in there so we switched > to the JDBC call for this one. > > On Friday, 30 September 2022 at 12:47:12 UTC+2 Noel Grandin wrote: > >> How many tables do you have, that should return in milliseconds. >> >> You could try rinning the built in profiler (see the docs) and see what >> it is doing. >> >> You could also directly query the metadata table >> INFORMATION_SCHEMA.INDEXES >> >> -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a98164da-90f2-42af-94c4-c031c23e1e3dn%40googlegroups.com.
Re: [h2] JDBC getIndexInfo for large table is very slow
Thanks Noel, Also see my other remark. I will look into that table. Actually, we did some stuff directly on the INFORMATION_SCHEMA tables before but the H2 versions above 200 had some modified layout/content in there so we switched to the JDBC call for this one. On Friday, 30 September 2022 at 12:47:12 UTC+2 Noel Grandin wrote: > How many tables do you have, that should return in milliseconds. > > You could try rinning the built in profiler (see the docs) and see what it > is doing. > > You could also directly query the metadata table > INFORMATION_SCHEMA.INDEXES > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ee8adec7-182d-4ec8-8eef-b0086e747240n%40googlegroups.com.
[h2] Re: JDBC getIndexInfo for large table is very slow
I read up on the method and now realize the call returns a number of statistics (like CARDINALITY) that is based on the rows in the index. That explains the slowness. The problem is that I only need to know which indexes there are on each table and do not care about the statistics. The tables are created dynamically based on meta data extracted from application user definitions (surveys). The application offers an admin interface that displays existing indexes and allows users to add/remove indexes if their use of the datasets (reporting dashboards) requires it. On Friday, 30 September 2022 at 12:40:06 UTC+2 Silvio wrote: > I have a largisch database (~6G) which I use in H2 2.1.214 embedded mode > on an NVME SSD. I use the JDBC call > > rsIdx = con.getMetaData.getIndexInfo(catalog,schema,table,false,false) > > to get the indexes for a single table (which is by far the largest in the > whole database) that contains about 3.6 million rows. The call takes about > 14 seconds to return the index es/segments for the table. On a not-so-fast > older SSD the same call takes ~30 seconds. > > Is there a reason that this takes so long? Is there a more efficient way > to determine which indexes are present on a table? > > Thanks, > > Silvio > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ef987e32-ac36-41b0-9cda-1f1c407b972en%40googlegroups.com.
Re: [h2] JDBC getIndexInfo for large table is very slow
How many tables do you have, that should return in milliseconds. You could try rinning the built in profiler (see the docs) and see what it is doing. You could also directly query the metadata table INFORMATION_SCHEMA.INDEXES -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnUcg_-kwi5jg_azxTb1kBE7K_%3DCjjx-7YSaTQL9GdQjoA%40mail.gmail.com.
[h2] JDBC getIndexInfo for large table is very slow
I have a largisch database (~6G) which I use in H2 2.1.214 embedded mode on an NVME SSD. I use the JDBC call rsIdx = con.getMetaData.getIndexInfo(catalog,schema,table,false,false) to get the indexes for a single table (which is by far the largest in the whole database) that contains about 3.6 million rows. The call takes about 14 seconds to return the index es/segments for the table. On a not-so-fast older SSD the same call takes ~30 seconds. Is there a reason that this takes so long? Is there a more efficient way to determine which indexes are present on a table? Thanks, Silvio -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/db841c68-feca-465b-9382-ba054c379b97n%40googlegroups.com.
