Re: [h2] Re: Very slow calculateCost

[Noel Grandin]

I suspect that we have an O(n^2) problem here, triggered by the way
TableView and ViewIndex tend to pass around query information as a SQL
string.

Which causes us to unnecessarily reparse and re-optimise subtrees of
queries.

Changing that, however, is a fair amount of work which I'm not likely to
get to anytime.

-- 
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] Re: Very slow calculateCost

[Christian MICHON]

Thanks for clarifying. So you've no control on the database DDL and no 
control on the query (as it is a generic query for an API).

My usual practice for long API is to at least split the internal elements 
into subqueries and only compile the result at the end in the business 
logic server language. Roda on ruby is typically a good tool for this.

If you cannot split this long generic query into manageable bits, then you 
have a good reproducible slow performance testcase for H2 maintainers.

My experience is that H2 is really good as temporary database with decent 
performances. When used in ETL (extract-tranform-load) data migrations, it 
is faster than major DBs.

Yet if you need stable DB engine for 24/7 operations, this might not be the 
best candidate until we move out of beta again. 1.3.176 was really good.

On Thursday, December 8, 2016 at 1:52:42 PM UTC+1, itineric wrote:
>
> Yes it is an obfuscated real database as I mentionned it when I attached 
> the files.
>
> The database is designed to trace all actions performed on an application. 
> The database design is what it is and is mostly optimized to store/restore 
> particular data fastly.
> The use case of this query is that the application provides an API to 
> query "everything". The querying API provides classes / methods to filter 
> returned elements. So the query is mostly generated to represent what was 
> asked through the API.
> I know the query is complex and one solution whould be to change the query 
> itself (but as I said, it is generated).
> If other databases had similar problems, I would be looking into that but 
> MySql, PostgreSql, Oracle handle it very fast. I would be expecting H2 to 
> have better performances then it does, especially when the database is 
> empty ! (I am not expecting the same performance from H2 that I get on 
> Oracle but like to get something acceptable).
>
>
> 2016-12-08 13:30 GMT+01:00 Christian MICHON  >:
>
>> I beg to differ: I ran the DDL into DBVisualizer (references mode, 
>> circular view) and from the look of it this is a real database with 
>> obfuscated names.
>>
>> Redesigning the database is not an option if it's not from the original 
>> author: the query itself has to be redesigned and this leads to the 
>> validity of the use case.
>>
>> @Itineric: can you share the application type and the use case of your 
>> query?
>>
>>
>> On Thursday, December 8, 2016 at 9:22:46 AM UTC+1, Steve McLeod wrote:
>>>
>>> Your query is extreme. Hundreds of joins, dozens of nested selects. Way 
>>> too big, and way too complicated. You'll never get any decent performance 
>>> with a query like that, nor will you ever be able to analyse and understand 
>>> the reasons for the performance problems.
>>>
>>> The solution to your problem is to redesign your database. Read up on 
>>> database normalisation.
>>>
>>> If you need ad hoc, complicated queries, first load your data from your 
>>> normal schema into a star schema as described in database warehouse 
>>> textbooks.
>>>
>>> -- 
>> You received this message because you are subscribed to a topic in the 
>> Google Groups "H2 Database" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/h2-database/6B5Sla2PkG8/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to 
>> h2-database...@googlegroups.com .
>> To post to this group, send email to h2-da...@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.

Re: [h2] Re: Very slow calculateCost

[itineric]

Yes it is an obfuscated real database as I mentionned it when I attached
the files.

The database is designed to trace all actions performed on an application.
The database design is what it is and is mostly optimized to store/restore
particular data fastly.
The use case of this query is that the application provides an API to query
"everything". The querying API provides classes / methods to filter
returned elements. So the query is mostly generated to represent what was
asked through the API.
I know the query is complex and one solution whould be to change the query
itself (but as I said, it is generated).
If other databases had similar problems, I would be looking into that but
MySql, PostgreSql, Oracle handle it very fast. I would be expecting H2 to
have better performances then it does, especially when the database is
empty ! (I am not expecting the same performance from H2 that I get on
Oracle but like to get something acceptable).


2016-12-08 13:30 GMT+01:00 Christian MICHON :

> I beg to differ: I ran the DDL into DBVisualizer (references mode,
> circular view) and from the look of it this is a real database with
> obfuscated names.
>
> Redesigning the database is not an option if it's not from the original
> author: the query itself has to be redesigned and this leads to the
> validity of the use case.
>
> @Itineric: can you share the application type and the use case of your
> query?
>
>
> On Thursday, December 8, 2016 at 9:22:46 AM UTC+1, Steve McLeod wrote:
>>
>> Your query is extreme. Hundreds of joins, dozens of nested selects. Way
>> too big, and way too complicated. You'll never get any decent performance
>> with a query like that, nor will you ever be able to analyse and understand
>> the reasons for the performance problems.
>>
>> The solution to your problem is to redesign your database. Read up on
>> database normalisation.
>>
>> If you need ad hoc, complicated queries, first load your data from your
>> normal schema into a star schema as described in database warehouse
>> textbooks.
>>
>> --
> You received this message because you are subscribed to a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/h2-database/6B5Sla2PkG8/unsubscribe.
> To unsubscribe from this group and all its topics, 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.

Re: [h2] Re: Very slow calculateCost

[Christian MICHON]

I beg to differ: I ran the DDL into DBVisualizer (references mode, circular 
view) and from the look of it this is a real database with obfuscated names.

Redesigning the database is not an option if it's not from the original 
author: the query itself has to be redesigned and this leads to the 
validity of the use case.

@Itineric: can you share the application type and the use case of your 
query?

On Thursday, December 8, 2016 at 9:22:46 AM UTC+1, Steve McLeod wrote:
>
> Your query is extreme. Hundreds of joins, dozens of nested selects. Way 
> too big, and way too complicated. You'll never get any decent performance 
> with a query like that, nor will you ever be able to analyse and understand 
> the reasons for the performance problems.
>
> The solution to your problem is to redesign your database. Read up on 
> database normalisation.
>
> If you need ad hoc, complicated queries, first load your data from your 
> normal schema into a star schema as described in database warehouse 
> textbooks.
>
>

-- 
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] Re: Very slow calculateCost

[Steve McLeod]

Your query is extreme. Hundreds of joins, dozens of nested selects. Way too 
big, and way too complicated. You'll never get any decent performance with a 
query like that, nor will you ever be able to analyse and understand the 
reasons for the performance problems.

The solution to your problem is to redesign your database. Read up on database 
normalisation.

If you need ad hoc, complicated queries, first load your data from your normal 
schema into a star schema as described in database warehouse textbooks.



-- 
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] Re: Very slow calculateCost

[itineric]

Hello,

Does anyone have an idea how I could get better performances ?

Regards,

Eric


Le vendredi 2 décembre 2016 16:21:47 UTC+1, itineric a écrit :
>
> When I found the problem I was using version "1.4.182". I tested on 
> version "1.4.193" the result was pretty much the same. I just find out what 
> the problem was using the last version since the debug is more verbose 
> during optimization phase in the latest version.
>
>
> 2016-12-02 15:57 GMT+01:00 Noel Grandin :
>
>> Also, which version are you testing against?
>>
>> One of our recent versions had a regression where it prepared statements 
>> slower than before.
>>
>>
>> -- 
>> 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.

Re: [h2] Re: Very slow calculateCost

[itineric]

When I found the problem I was using version "1.4.182". I tested on version
"1.4.193" the result was pretty much the same. I just find out what the
problem was using the last version since the debug is more verbose during
optimization phase in the latest version.


2016-12-02 15:57 GMT+01:00 Noel Grandin :

> Also, which version are you testing against?
>
> One of our recent versions had a regression where it prepared statements
> slower than before.
>
>
> --
> 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.

Re: [h2] Re: Very slow calculateCost

[Noel Grandin]

Also, which version are you testing against?

One of our recent versions had a regression where it prepared statements slower 
than before.

--
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.