Segment builder generate unused SQL Joins

[sadortun]

Hi Oro Team,

It took me 4 months, but i finally was able to reproduce the issue we are facing. I was also able to reproduce this issue on demo.orocrm.com

This is a follow up on #925 Topic # 2. Ill move it here since i was able to reproduce the issue, but i am unable to find the source of the problem (yet :) ) !

Thé following example is with Tags, but the same behaviour is repeated with any Virtual Relationships in the QUery filter

How to reproduce

• With ORO Tag 3.1.x (and 4.0.0 on demo.orocrm.com)
• Create a Report for Contacts
• Add the filter Contact / Tag / Taxonomy = {your taxonomy}
• Enable "display SQL query"
• Save it

Results

The generated query will look something like this :

As you can notice, there is two blocks of the query that repeats,

RED Block 🔴

• Unused query, seems to simply join everything on the current contact table.
• This block return on our install returned 256827 total results and Query took 12.8162 seconds.

Green Block 💚

• Useful query,
• on our install, returned 25 total results and Query took 0.0121 seconds.

[anyt]

Thank you for an investigation @sadortun!
It looks like an issue, we'll validate it. Internal ticket id #BAP-19352.

[sadortun]

Thanks @anyt,

Do you have any ETA ? I think this issue is fairly important to any of your users who use reports and segments. Without knowing, they all face a severe performance hit.

We are also ready to contribute $$$ if it can help sped up the resolution of this issue. Contact me by email if you are interested.

Have a great day,
Samuel

[vtsykun]

Hi all,
Related to #738 (comment)
It worked as described in the issue, until version 1.9.2. To fix for our customization we coped the all old filters from oro/platform v1.9.1 and add toggle options "Entity Grouping" to switch between old and new oro filters

For example, the given query built with old filters looks like this

SELECT 
  o0_.last_name AS last_name_0, 
  o0_.id AS id_1, 
  o0_.organization_id AS sclr_2, 
  o0_.user_owner_id AS sclr_3 
FROM 
  orocrm_contact o0_ 
WHERE 
  o0_.id IN (
    SELECT 
      o1_.id 
    FROM 
      orocrm_contact o1_ 
      LEFT JOIN oro_tag_tagging o2_ ON (
        (
          o2_.entity_name = 'Oro\Bundle\ContactBundle\Entity\Contact' 
          AND o2_.record_id = o1_.id
        )
      ) 
      LEFT JOIN oro_tag_tag o3_ ON o2_.tag_id = o3_.id 
      AND (o3_.organization_id = 1) 
      LEFT JOIN oro_tag_taxonomy o4_ ON o3_.taxonomy_id = o4_.id 
      AND (o4_.organization_id = 1) 
      LEFT JOIN oro_tag_tag o5_ ON o4_.id = o5_.taxonomy_id 
      AND (o5_.organization_id = 1) 
    WHERE 
      o5_.id = '1' 
      AND o1_.organization_id = 1
  ) 
  AND o0_.organization_id = 1 
ORDER BY 
  o0_.id ASC 
LIMIT 
  25

[sadortun]

FYI @x86demon

[sadortun]

Hi @vtsykun Thanks for your details, did you try the fix implemented for #738 in ORO 3.1.7 ?

[vtsykun]

Hi @sadortun
Yes, but #738 fixes does not resolve problem from this issue.

[sadortun]

@vtsykun Thanks for time to share theses details, this little option is very useful !! I wish i knew it existed before !

[sadortun]

FYI Also tested with other types of joins (Contact -> Account ), and the same issue exists. It is not limited to Virtual Relations

[sadortun]

Hi @anyt !

Do you have an update for this ? We use segments extensively and this is still causing us an headache.

If you are able to expedite the resolution of this issue we can contribute to the development cost. Send me an email if you are interested.

Have a great day,
Samuel

[sadortun]

For anyone interested who have performance issue with their segments, we have a workaround for this.

It allow an optimization on segment loading time from 2x to 15x.

Feel free to contact me for more info

[anyt]

Hi @sadortun,

After the investigation, the team says that the only way we see to optimize the performance is to rewrite the builder from scratch, and there is no guaranty that everything would be fast for both database providers, MySQL, and Postgres.

Optimizing some specific queries, like tags, could work, but for now, we don't see how to optimize all the similar queries.
If you have ideas to share, feel free to create a PR or share them in an issue, it could help in further research.

[sadortun]

Hi @anyt,

Thanks for your quick answer.

As you mentioned, i spent countless hours trying to find if the issue was fixable simply, but sadly i could not, The issue seems to be part of the design of the QueryDesigner.

Our solution was to build a very simple but effective workaround. We look at all the LEFT JOIN defined in the $qb and check every part of the query to see if the join $alias is being used somewhere, if not, we remove the join fom the $qb

This simple fix allows to speed up queries that where taking over 5 minutes, down to about 5 seconds.

This allow us to use the QueryDesigner to allow our users to create complex queries that filter records from a table containing hundred of thousand of records, with related entities table containing about 10 millions records.

We are actively testing (and logging) every query optimization over the next week and we will rollout the feature to production servers by the end of next week

Here is the workaround: https://github.com/NewEraSystems/platform/pull/1/files

Have a great day,
Samuel