Hi, Yes, that makes sense. I can reproduce the problem with a simple test case (that creates many tables and indexes). I have an idea how this could be solved by changing the database metadata code (MetaTable.java), but it is a bit complicated.
Just to confirm that this is not a MVStore or MVCC problem, but a general problem reading database metadata: Can you reproduce the problem when you append ";mv_store=false;mvcc=false" to the database URL, with a recent version of H2 (but exactly the same version of com.guidewire.pl.system. database)? Regards, Thomas On Wednesday, July 8, 2015, Wes Clark <[email protected]> wrote: > select count(*) from INFORMATION_SCHEMA.TABLES; > COUNT(*) > <http://10.58.2.44:8082/query.do?jsessionid=a463531bf3c62477a31f00116b7beda7#> > 1713(1 row, 34 ms) > > select count(*) from INFORMATION_SCHEMA.INDEXES; > COUNT(*) > <http://10.58.2.44:8082/query.do?jsessionid=a463531bf3c62477a31f00116b7beda7#> > 7557 > This is "sample" database. We have four other databases which might be > twice or three times as big, but same order of magniture. > > On Wednesday, July 8, 2015 at 9:35:49 AM UTC-7, Thomas Mueller wrote: >> >> Hi, >> >> You first indicate that the query can be sped by taking into account the >>> schema name. >>> >> >> I'm sorry! Yes, first I thought changing the query would help, but then I >> found out there is no easy way to do that in this case. Even adding the >> schema name as a condition will not help. That is, if you need all the info >> that the query returns. The problem is the join (which is needed for the >> "is nullable" column). This join will not use an efficient index. (Actually >> there is an index, the MetaIndex, on the column "table_name", and this >> index is used, but the index is inefficient if there are many tables). >> >> Later you indicate I should change the metadata, for example to use >>> materialized metadata tables. >>> >> >> That change would ideally be done in H2. >> >> >>> What is the simplest way to make this query efficient? Can it be sped >>> up by simply changing the query? >>> >> >> That's a good question. >> >> How does your schema look like? How many tables and how many indexes and >> schemas do you have? >> >> Regards, >> Thomas >> > -- > 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 [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
