@samjf opened this Pull Request on October 5th 2021 Contributor

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:

  • [x] Replace SQL with optimized SQL query
  • [x] Only execute query if e-commerce feature is enabled
  • [x] 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 commented on October 5th 2021 Contributor

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
This Pull Request was closed on October 11th 2021
Powered by GitHub Issue Mirror