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.

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

Optimise ecommerce life time metric query #18097

Merged
merged 12 commits into from Oct 11, 2021
Merged

Optimise ecommerce life time metric query #18097

merged 12 commits into from Oct 11, 2021

Conversation

samjf
Copy link
Contributor

@samjf samjf commented Oct 5, 2021 •
edited

Description:

fixes issue 18036

The e-commerce life time query needs to be optimised due to a slower performance. There was also a few other things to be addressed:

  • Replace SQL with optimized SQL query
  • Only execute query if e-commerce feature is enabled
  • Group the two queries that the function used into a single query

Notes for reviewer
There are a huge amount of tests that needed to be updated due to default fixtures including ecommerce lifetime defaults even if it was turned off for that website.
You can filter a majority of these in the Files changed view with the file extension filtering. Filter out XML to see major changes.

Review

@samjf
Copy link
Contributor Author

samjf commented Oct 5, 2021

Here are the results of the performance tests run against different join types, and other modifications to the query that helped decide on the committed query change:

  • each query is run 100x without caching with a visitor sample as the arguments
  • each query is run 100x without caching with the top visitor as the argument

Testing query with 100 random visitors
--- Test join types ---
SAMPLE RESULT FOR: LEFT JOIN: Time elapsed: 0.119s
Top Visitor RESULT FOR: LEFT JOIN: Time elapsed: 3.781s
SAMPLE RESULT FOR: STRAIGHT_JOIN: Time elapsed: 0.088s
Top Visitor RESULT FOR: STRAIGHT_JOIN: Time elapsed: 0.997s
SAMPLE RESULT FOR: INNER JOIN: Time elapsed: 0.115s
Top Visitor RESULT FOR: INNER JOIN: Time elapsed: 3.697s

--- Test additional where clause on conversions ---
SAMPLE RESULT:  Time elapsed: 0.185s
Top visitor result:  Time elapsed: 4.699s

--- Test re-order select clauses ---
SAMPLE RESULT:  Time elapsed: 0.122s
Top visitor result:  Time elapsed: 3.873s

--- Test DUAL query vs GROUP query ---
DUAL QUERY (STRAIGHT_JOIN) timings Time elapsed: 1.950s
GROUP QUERY (STRAIGHT_JOIN) timings Time elapsed: 0.944s

--- ORIGINAL --- LEFT JOIN + DUAL
SAMPLE RESULT:  Time elapsed: 0.239s
Top visitor result:  Time elapsed: 7.572s

--- WINNER - OPTIMISED NEW QUERY --- STRAIGHT_JOIN + GROUP AGG.
SAMPLE RESULT:  Time elapsed: 0.092s
Top visitor result:  Time elapsed: 1.062s

@samjf samjf mentioned this pull request Oct 8, 2021
Merged
11 tasks
@samjf samjf added the Needs Review PRs that need a code review label Oct 11, 2021
@samjf samjf marked this pull request as ready for review October 11, 2021 01:08
@tsteur tsteur merged commit 94c4db4 into 4.x-dev Oct 11, 2021
@tsteur tsteur deleted the i18036-optimise-ecomm-life-time-metric branch October 11, 2021 03:20
tsteur added a commit that referenced this pull request Oct 11, 2021
@tsteur
Mention Optimise ecommerce life time metric query in developer changelog
c025891 
refs #18097

Just merged #18097 and then figured might better to mention this in  the dev changelog.
@tsteur tsteur mentioned this pull request Oct 11, 2021
Merged
11 tasks
tsteur added a commit that referenced this pull request Oct 11, 2021
@tsteur
Mention Optimise ecommerce life time metric query in developer change…
465ad18 
…log (#18126)

refs #18097

Just merged #18097 and then figured might better to mention this in  the dev changelog.
@justinvelluppillai justinvelluppillai added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Nov 29, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
Needs Review PRs that need a code review not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

Optimise ecommerce life time metric query
3 participants
@samjf @tsteur @justinvelluppillai