Re: [h2] Re: Very slow calculateCost

2016-12-08 Thread 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 <christian.mic...@gmail.com>:

> 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

2016-12-07 Thread 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 <noelgran...@gmail.com>:
>
>> 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

2016-12-02 Thread 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.

[h2] Re: Very slow calculateCost

2016-12-02 Thread itineric 
I have tested the FORCE_JOIN_ORDER option, prepareStatement is faster but 
it is still not good enough. Now it takes 1.5s.

I had to impersonate the database and cannot provide any data but you will 
find the database creation script attached.

I also joined the query which I have troubles with. The query is generated 
from Java code (representing search filters) to SQL.
You may have a solution that whould be to rewrite the query but I cant do 
that since the query is generated from Java filters that can be combined in 
many different ways.
The main issue is that optimise algorythm does too many iterations on each 
joins. You will see that with the last h2 version and trace level 3.

As I said before, the query is top-fast on other DBMS, optimiser does the 
job right, removes unnecessary joins, etc.

Hope it helps,

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.

CREATE TABLE "AA"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NOT NULL
);

CREATE TABLE "AB"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NULL,
  "AD" VARCHAR(128) NOT NULL,
  "AE" VARCHAR(128) NULL
);

CREATE TABLE "AC"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL
);

CREATE TABLE "AD"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NULL
);

CREATE TABLE "AE"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NOT NULL,
  "AE" BIGINT NULL,
  "AF" BIGINT NULL,
  "AG" BIGINT NULL,
  "AH" BIGINT NULL,
  "AI" BIGINT NULL,
  "AJ" VARCHAR(128) NULL,
  "AK" VARCHAR(128) NULL,
  "AL" VARCHAR(128) NULL,
  "AM" INT NULL,
  "AN" INT NOT NULL,
  "AO" VARCHAR(2048) NULL,
  "AP" DATETIME NOT NULL,
  "AQ" DATETIME NULL,
  "AR" DATETIME NULL,
  "AS" DATETIME NULL,
  "AT" DATETIME NULL,
  "AU" DATETIME NULL,
  "AV" DATETIME NULL,
  "AW" DATETIME NULL,
  "AX" DATETIME NULL
);

CREATE TABLE "AF"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NULL
);

CREATE TABLE "AG"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NULL,
  "AD" INT NOT NULL
);

CREATE TABLE "AH"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NULL,
  "AC" BIGINT NULL,
  "AD" BIGINT NULL,
  "AE" BIGINT NULL,
  "AF" BIGINT NULL,
  "AG" BIGINT NULL,
  "AH" BIGINT NULL,
  "AI" INT NOT NULL
);

CREATE TABLE "AI"
(
  "AA" CHAR(36) NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NULL,
  "AE" DATETIME NOT NULL,
  "AF" INT NOT NULL,
  "AG" BOOLEAN NOT NULL,
  "AH" BOOLEAN NOT NULL
);

CREATE TABLE "AJ"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NULL,
  "AD" VARCHAR(128) NOT NULL,
  "AE" BIGINT NOT NULL
);

CREATE TABLE "AK"
(
  "AA" BIGINT NOT NULL
);

CREATE TABLE "AL"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NULL,
  "AC" BIGINT NULL,
  "AD" BIGINT NULL,
  "AE" VARCHAR(64) NOT NULL,
  "AF" VARCHAR(64) NOT NULL,
  "AG" CHAR(36) NOT NULL
);

CREATE TABLE "AM"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL
);

CREATE TABLE "AN"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BLOB NOT NULL,
  "AD" BIGINT NOT NULL
);

CREATE TABLE "AO"
(
  "AA" BIGINT NOT NULL,
  "AB" BLOB NOT NULL
);

CREATE TABLE "AP"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NULL
);

CREATE TABLE "AQ"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" VARCHAR(128) NOT NULL,
  "AD" VARCHAR(128) NULL
);

CREATE TABLE "AR"
(
  "AA" BIGINT NOT NULL,
  "AB" VARCHAR(64) NOT NULL,
  "AC" BIGINT NOT NULL
);

CREATE TABLE "AS"
(
  "AA" BIGINT NOT NULL
);

CREATE TABLE "AT"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" VARCHAR(128) NOT NULL
);

CREATE TABLE "AU"
(
  "AA" BIGINT NOT NULL
);

CREATE TABLE "AV"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NOT NULL
);

CREATE TABLE "AW"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" VARCHAR(128) NOT NULL
);

CREATE TABLE "AX"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NOT NULL,
  "AE" BOOLEAN NOT NULL
);

CREATE TABLE "AY"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NULL,
  "AD" BIGINT NOT NULL,
  "AE" VARCHAR(128) NOT NULL
);

CREATE TABLE "AZ"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL,
  "AC" BIGINT NOT NULL,
  "AD" BIGINT NOT NULL,
  "AE" BIGINT NOT NULL
);

CREATE TABLE "BA"
(
  "AA" BIGINT NOT NULL,
  "AB" VARCHAR(128) NOT NULL,
  "AC" BOOLEAN NOT NULL
);

CREATE TABLE "BB"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL
);

CREATE TABLE "BC"
(
  "AA" BIGINT NOT NULL,
  "AB" BIGINT NOT NULL
);

CREATE TABLE "BD"
(
  "AA" BIGINT NOT NULL,

[h2] Very slow calculateCost

2016-12-01 Thread 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.