[SOLVED] Performance with many joins

[tali]

Hi,

I have a very big form (more than a hudred fields).
I order to organize the form (Tabs) I have built 1-1 groups (about 20).

Fabrik, when querying to get the Ids produces a huge amount of lines (more than 1 000 000) just to get the 8 Ids corresponding to the WHERE clause.

Instead of simply build this query

SELECT DISTINCT `fab__books`.`id` AS __pk_val0
FROM `fab__books`
WHERE ( fab__books.id_stage = '4' )
ORDER BY `fab__books`.`id` DESC

it builds this huge query (here shorten by ellipsis)

SELECT DISTINCT `fab__books`.`id` AS __pk_val0,
`fab__stages`.`id` AS __pk_val1,
`fab__historybooks`.`id` AS __pk_val7,
`fab__historyusers`.`id` AS __pk_val8,
`fab__books_10_repeat`.`id` AS __pk_val2,
`fab__books_11_repeat`.`id` AS __pk_val3,
...
`fab__books_88_repeat`.`id` AS __pk_val26,
`fab__books_89_repeat`.`id` AS __pk_val27,

FROM `fab__books`
    LEFT JOIN `fab__dates_rm` AS `fab__dates_rm` ON `fab__dates_rm`.`date_meeting` = `fab__books_88_repeat`.`date_available`
    LEFT JOIN `fab__stages` AS `fab__stages_0` ON `fab__stages_0`.`id` = `fab__books`.`id_stage`
    LEFT JOIN `fab__stages` AS `fab__stages` ON `fab__stages`.`id` = `fab__books`.`id_stage`
    LEFT JOIN `fab__historybooks` AS `fab__historybooks` ON `fab__historybooks`.`projet_id` = `fab__books`.`id`
    LEFT JOIN `fab__historyusers` AS `fab__historyusers` ON `fab__historyusers`.`projet_id` = `fab__books`.`id`
    LEFT JOIN `fab__books_10_repeat` AS `fab__books_10_repeat` ON `fab__books_10_repeat`.`parent_id` = `fab__books`.`id`
    LEFT JOIN `fab__books_11_repeat` AS `fab__books_11_repeat` ON `fab__books_11_repeat`.`parent_id` = `fab__books`.`id`
...
    LEFT JOIN `n05pm_users` AS `n05pm_users_8` ON `n05pm_users_8`.`id` = `fab__books_89_repeat`.`de`
    LEFT JOIN `n05pm_users` AS `n05pm_users` ON `n05pm_users`.`id` = `fab__books`.`id_written`
WHERE ( fab__books.id_stage = '4' )
ORDER BY `fab__books`.`id` DESC,`fab__books`.`id` DESC

How can I bring Fabrik to generate a slimmer query?

 

[troester]

If you are defining such an amount of joins (not groups) Fabrik has to query them (additionally implicit joins from dbjoin/cdd/user elements).

The tabs template is displaying groups, (non-repeatable) groups don't need to be (1-1)joins. You can define multiple groups for one form to organize your elements.

 

[Ghuerren]

Please, where can we find Fabrik's query?

 

[tali]

Thank you troester. I achieved this by

• Copying the List
• Deleting all joins of the copied list
• Showing the list from the copying list
• But with customized links to form and details belonging to the original list

@Ghuerren

1. Go to Fabrik in the backend, and in Options (top right) set Debugging Fabrik to Yes
2. Then, add ? (ou &) fabrikdebug=1 to any url you want to see the query
3. Look especially at table:mergeJoinedData get ids and list GetData:Your list

 

[cheesegrits]

Fabrik, when querying to get the Ids produces a huge amount of lines (more than 1 000 000) just to get the 8 Ids corresponding to the WHERE clause.

Are you using any CDD elements? When I see queries that generate that many rows, it's almost always because there are CDD elements which don't have unique FK's.

The other culprit is usually just incorrect join design, using unnecessary joins.

-- hugh

 

[Ghuerren]

Thank u for the explanation!

 

[tali]

I don't use CDD, but have several joined tables.

The problem was the use all these joins for the liste, where I do not need them.

Hence the solution to copy the list, which I mentioned above, and delete all the joins.
I still use the initial list with all the joins but only for the form.

The time required to show the list decreased dramatically from several minutes to less than a second

 

[cheesegrits]

There's almost certainly a logic issue with the join setup on the original list. I've never seen this "millions of rows" thing in a setup which is correctly normalized and joined on unique fk/pk.

-- hugh