Re: [h2] Very slow calculateCost

[Christian MICHON]

Can you please share the database with H2 community?

It's hard to tell what is happening without a concrete dataset or at least the 
complete DDL.

Please do so if the above suggested solution does not work for you.

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Very slow calculateCost

[Sergi Vladykin]

Hi!

If you know correct join order for tables in your query, you can enable
setting FORCE_JOIN_ORDER on you connection. This way H2 optimizer will not
try to find the optimal join order, but will just find the best indexes for
the join order as it is written in query. It will be much faster.

Another option is to use some connection pool with caching of prepared
statements. This will allow to optimize query only once and then reuse the
prepared statement.

Sergi

2016-12-01 18:29 GMT+03:00 itineric :

> Hi,
>
> I have a database with many tables. This database is most part empty or
> has at most 10 lines per table.
> I have a query that uses many tables (~50) and uses inner/left join
> (always on indexed columns (PK/FK)).
> The query is really slow but not on execution, during prepareSatement.
> I activated traces on the database and find out that there were 10 000 000
> (at least) calls to "potential plan item cost".
> There is also the same call repeatedly "calculate cost for plan [T1 ...
> T30]" (where T1 ... T30 lists all the aliases of my tables.
> I had to stop the database when trace file got 1Gb big.
> Last cost printed in trace file was "best plan item cost
> 1,358,740,342,666,069,800,000,000,000,000"... It seams really big... for
> such an empty database.
>
> On other databases the query executes in less than 10ms. On H2, it needs
> between 5 and 6 seconds to perform prepareStatement.
>
> Is there a way to configure some optimization algorythm ? Or a way to not
> optimize at all ?
>
> Regards,
>
> Eric
>
> --
> 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 https://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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Very slow calculateCost

[itineric]

Hi,

I have a database with many tables. This database is most part empty or has 
at most 10 lines per table.
I have a query that uses many tables (~50) and uses inner/left join (always 
on indexed columns (PK/FK)).
The query is really slow but not on execution, during prepareSatement.
I activated traces on the database and find out that there were 10 000 000 
(at least) calls to "potential plan item cost".
There is also the same call repeatedly "calculate cost for plan [T1 ... 
T30]" (where T1 ... T30 lists all the aliases of my tables.
I had to stop the database when trace file got 1Gb big.
Last cost printed in trace file was "best plan item cost 
1,358,740,342,666,069,800,000,000,000,000"... It seams really big... for 
such an empty database.

On other databases the query executes in less than 10ms. On H2, it needs 
between 5 and 6 seconds to perform prepareStatement.

Is there a way to configure some optimization algorythm ? Or a way to not 
optimize at all ?

Regards,

Eric

-- 
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.