Hi, I have this query that took 250 sec.
If I use only 3 Join the time is 0,9
If I use 4 join the time is 8,9 sec.
How i can optimize?
(tried index on different column but no improve, now on interm.idgiro, interm.tipo, G.idbatt, G.idx primary)
Thanks!

SELECT I1.tempo AS I1, I2.tempo AS I2, I3.tempo AS I3, I4.tempo AS I4, I5.tempo, G.* FROM giri G
LEFT OUTER JOIN interm I1 ON I1.idGiro = G.idx AND I1.tipo = 1
LEFT OUTER JOIN interm I2 ON I2.idGiro = G.idx AND I2.tipo = 2
LEFT OUTER JOIN interm I3 ON I3.idGiro = G.idx AND I3.tipo = 3
LEFT OUTER JOIN interm I4 ON I4.idGiro = G.idx AND I4.tipo = 4
LEFT OUTER JOIN interm I5 ON I5.idGiro = G.idx AND I5.tipo = 4
WHERE G.idBatt = :idbatt AND G.tipo = 0 AND G.invalid = false AND G.giro > 0
GROUP BY G.idx
ORDER BY idnome, prog

================================================================================
SQL Query (Executed by ElevateDB 2.31 Build 8)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"I1"."tempo" AS "I1",
"I2"."tempo" AS "I2",
"I3"."tempo" AS "I3",
"I4"."tempo" AS "I4",
"G"."idx" AS "idx",
"G"."prog" AS "prog",
"G"."idBatt" AS "idBatt",
"G"."idNome" AS "idNome",
"G"."idPass" AS "idPass",
"G"."tipo" AS "tipo",
"G"."giro" AS "giro",
"G"."tempo" AS "tempo",
"G"."corrT" AS "corrT",
"G"."usRx" AS "usRx",
"G"."orario" AS "orario",
"G"."transp" AS "transp",
"G"."invalid" AS "invalid",
"G"."noTime" AS "noTime",
"G"."manu" AS "manu",
"G"."nota" AS "nota"
FROM "giri" AS "G" LEFT OUTER JOIN "interm" AS "I1" ON "I1"."idGiro" =
"G"."idx" AND "I1"."tipo" = 1
LEFT OUTER JOIN "interm" AS "I3" ON "I3"."idGiro" = "G"."idx" AND "I3"."tipo" =
3
LEFT OUTER JOIN "interm" AS "I2" ON "I2"."idGiro" = "G"."idx" AND "I2"."tipo" =
2
LEFT OUTER JOIN "interm" AS "I4" ON "I4"."idGiro" = "G"."idx" AND "I4"."tipo" =
4
WHERE "idBatt" = 33 AND "G"."tipo" = 0 AND "G"."invalid" = false AND "G"."giro"
> 0
GROUP BY "G"."idx"
ORDER BY "G"."idNome", "G"."prog"

Source Tables
-------------

giri (G): 22780 rows
interm (I1): 13548 rows
interm (I2): 13548 rows
interm (I3): 13548 rows
interm (I4): 13548 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the giri (G) table:

"idBatt" = 33

Index scan (giri.indice3): 3608 keys, 56KB estimated cost

AND

"G"."invalid" = false AND "G"."tipo" = 0 AND "G"."giro" > 0

Row scan (giri): 3608 rows, 1,76MB estimated cost

Joins
-----

The driver table was the giri (G) table

The giri (G) table was joined to the interm (I1) table with the left outer join
expression:

"I1"."idGiro" = "G"."idx" AND "I1"."tipo" = 1

The giri (G) table was joined to the interm (I3) table with the left outer join
expression:

"I3"."idGiro" = "G"."idx" AND "I3"."tipo" = 3

The giri (G) table was joined to the interm (I2) table with the left outer join
expression:

"I2"."idGiro" = "G"."idx" AND "I2"."tipo" = 2

The giri (G) table was joined to the interm (I4) table with the left outer join
expression:

"I4"."idGiro" = "G"."idx" AND "I4"."tipo" = 4

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the interm (I1) table:

"I1"."idGiro" = "G"."idx"

Index scan (interm.indice1)

AND

"I1"."tipo" = 1

Row scan (interm)

Hint: Create index(es) (interm) on column(s) "I1"."tipo" for possible better
performance

The following join condition was applied to the interm (I3) table:

"I3"."idGiro" = "G"."idx"

Index scan (interm.indice1)

AND

"I3"."tipo" = 3

Row scan (interm)

Hint: Create index(es) (interm) on column(s) "I3"."tipo" for possible better
performance

The following join condition was applied to the interm (I2) table:

"I2"."idGiro" = "G"."idx"

Index scan (interm.indice1)

AND

"I2"."tipo" = 2

Row scan (interm)

Hint: Create index(es) (interm) on column(s) "I2"."tipo" for possible better
performance

The following join condition was applied to the interm (I4) table:

"I4"."idGiro" = "G"."idx"

Index scan (interm.indice1)

AND

"I4"."tipo" = 4

Row scan (interm)

Hint: Create index(es) (interm) on column(s) "I4"."tipo" for possible better
performance


Result set I/O statistics
-------------------------

Total rows visited: 1113743

Row buffer manager

Max buffer size: 1023,72KB Buffer size: 1,56MB

Hits: 3334809   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 17   written: 1,61MB

Index Page buffer manager

Max buffer size: 2MB Buffer size: 192KB

Hits: 2238701   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 23   written: 520KB

================================================================================
3210 row(s) returned in 8,112 secs
================================================================================

Mirco


If you read the plan you're given a number of suggestions (adding indices) have you tried that? I would also suggest adding an index for the group by column and a compound index for the order by

Roy Lambert

Yes I have already tried the indices on every field, group by column is the primary key.
I don't understand why too much difference if I use 3 or 4 join on the same table
Thanks!


Roy Lambert wrote:

Mirco


If you read the plan you're given a number of suggestions (adding indices) have you tried that? I would also suggest adding an index for the group by column and a compound index for the order by

Roy Lambert

Mirco

Is this a typo or deliberate?
<<
LEFT OUTER JOIN interm I4 ON I4.idGiro = G.idx AND I4.tipo = 4
LEFT OUTER JOIN interm I5 ON I5.idGiro = G.idx AND I5.tipo = 4

>>

>Yes I have already tried the indices on every field, group by column is the primary key.

That's not what the execution plan is telling me

>I don't understand why too much difference if I use 3 or 4 join on the same table

Neither do I

Your best course of action may be to open a support ticket with Tim.

Can you post the database (at least the catalog & those two tables) so I can have a look at it.I have some ideas of things that can be done but it depends on what the results should be eg do you expect values for I1..I5 or will only one have a value and the others be null?

Roy

Sorry I5.tipo = 5 is correct!
There is a value on every event.I1...I5
I prepare a backp of table for you
Thanks!

Roy Lambert wrote:

Mirco

Is this a typo or deliberate?
<<
LEFT OUTER JOIN interm I4 ON I4.idGiro = G.idx AND I4.tipo = 4
LEFT OUTER JOIN interm I5 ON I5.idGiro = G.idx AND I5.tipo = 4

>>

>Yes I have already tried the indices on every field, group by column is the primary key.

That's not what the execution plan is telling me

>I don't understand why too much difference if I use 3 or 4 join on the same table

Neither do I

Your best course of action may be to open a support ticket with Tim.

Can you post the database (at least the catalog & those two tables) so I can have a look at it.I have some ideas of things that can be done but it depends on what the results should be eg do you expect values for I1..I5 or will only one have a value and the others be null?

Roy

DB attachment

Mirco Malagoli wrote:

Sorry I5.tipo = 5 is correct!
There is a value on every event.I1...I5
I prepare a backp of table for you
Thanks!

Roy Lambert wrote:

Mirco

Is this a typo or deliberate?
<<
LEFT OUTER JOIN interm I4 ON I4.idGiro = G.idx AND I4.tipo = 4
LEFT OUTER JOIN interm I5 ON I5.idGiro = G.idx AND I5.tipo = 4

>>

>Yes I have already tried the indices on every field, group by column is the primary key.

That's not what the execution plan is telling me

>I don't understand why too much difference if I use 3 or 4 join on the same table

Neither do I

Your best course of action may be to open a support ticket with Tim.

Can you post the database (at least the catalog & those two tables) so I can have a look at it.I have some ideas of things that can be done but it depends on what the results should be eg do you expect values for I1..I5 or will only one have a value and the others be null?

Roy

Mirco


Just adding these two indices

CREATE INDEX "IdxGiro" ON "interm" ("idGiro")!

CREATE INDEX "IdxTipo" ON "interm" ("tipo")!


reduced the time from >250 seconds to <10 seconds


Roy Lambert

Mirco


I've experimented a bit and it looks as though most of the time is spent writing to disk now so sub 10 seconds is about as good as it will get without doing anything complex.

Roy Lambert

Hi, I added the sixth join
"LEFT OUTER JOIN interm I6 ON I6.idGiro = G.idx AND I6.tipo = 6"
and now the query is finished after 310 sec.
The plan is to get 18 joins.
What can I do at this point?
Thanks in advance!

Roy Lambert wrote:

Mirco


I've experimented a bit and it looks as though most of the time is spent writing to disk now so sub 10 seconds is about as good as it will get without doing anything complex.

Roy Lambert

Mirco

I've just tried here and with I6 added in I still get c10 seconds. I suspect that you've somehow lost the indices I said to add in.

However, I suspect that you need to go back to the beginning and see why you're doing this and if there's a better way.

Roy Lambert