[h2] Re: JDBC getIndexInfo for large table is very slow

2022-09-30 Thread Silvio 
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

2022-09-30 Thread Evgenij Ryazanov 
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.

[h2] Re: JDBC getIndexInfo for large table is very slow

2022-09-30 Thread Silvio 
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.