Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Querybuilder / Segment SQL Optimisation #925

Closed
sadortun opened this issue Jun 5, 2019 · 5 comments · May be fixed by #965
Closed

Querybuilder / Segment SQL Optimisation #925

sadortun opened this issue Jun 5, 2019 · 5 comments · May be fixed by #965

Comments

@sadortun
Copy link
Contributor

sadortun commented Jun 5, 2019 •
edited
Loading

Hi Oro Team,

We are investigating why on some query the Segment page become very slow ( 30-45 seconds to load with complex query),

Topic 1

We found that the filter query is executed twice and the only difference is the LIMIT clause. and there is query executed multiple times

  • If you look bellow at query # 57 and query # 59. Is # 57 nessessary ? If not, this could improve loading time by 25% !
  • If you look at query # 56 and # 58, they are identical .... That would be another 25% improvement in speed.

Optimizing theses two would give users a 50% improvement in all features related to segments !

(example with query email contains "a"
image

Topic 2

In our use case, the COUNT() query takes almost 15 seconds, compared to the SELECT which takes about 300ms.

  • Do you have any idea why the COUNT() is taking such excruciating (50 times) time to run ?

image

Have a good day,
Samuel
@sadortun
Copy link
Contributor Author

sadortun commented Jun 5, 2019 •
edited
Loading

After some troubleshooting, i've found out that the query are only executed by the validate() method

https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/QueryDesignerBundle/Validator/QueryValidator.php#L100

And results from query # 56 and # 57 are discarded. Then re-executed by the appropriate getGridData()

Is it normal the validation run every time the segment is used ?

@aivus
Copy link
Contributor

aivus commented Jun 5, 2019

Hi @sadortun
Thank you for your investigation

We need some time to check this

@sadortun
Copy link
Contributor Author

sadortun commented Aug 6, 2019

Hi @aivus

Do you have an update on this ? Can you provide an ETA ? It's a blocking issue for us.

We are willing to pay to get support on this issue quickly if it can help to get a faster resolution.

Thanks,
Samuel

@anyt
Copy link
Contributor

anyt commented Aug 6, 2019

Hi @sadortun,
For now, no ETA as it's quite complex and not a high priority issue. Also, I didn't find any similar issue reported by the enterprise customers that are still open, this usually means that the query performance is good enough on a PostgreSQL. And this is actually expected behavior that complex queries are significantly slower on Mysql.

@sadortun sadortun mentioned this issue Oct 17, 2019
Closed
@vtsykun vtsykun mentioned this issue Oct 21, 2019
Open
@sadortun sadortun mentioned this issue Oct 29, 2019
Open
@sadortun
Copy link
Contributor Author

Closing, since TOPIC 1 is covered by #965 and TOPIC 2 is covered by #967

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Disable the validation of segment filter form vtsykun/platform
3 participants
@sadortun @aivus @anyt