search

ruichen199801 / forxnews

CIS550 database course project
https://youtu.be/nuDBwg3mG6o
0 stars 1 forks source link

[Improvement] Analytics SQL Optimization #76

Closed ruichen199801 closed 1 year ago

ruichen199801 commented 1 year ago

Optimize complex SQL queries used in analytics routes.

Details: https://docs.google.com/document/d/1G4SQG4v3Ostppn91XGbUNfPpWdlaAutYeeUZEM9J7cA/edit

ruichen199801 commented 1 year ago

Currently we optimized 3 of the 4 complex analytics queries. The execution time of the queries is improved, but there is no significant difference in the server response time of the analytics routes in production.

This happens probably because for queries with LIKE %text%, the query is mainly slowed down by it and other optimizations may not reflect on the actual server response time; for queries without it, althought the query optimization is significant, the query runs in less than 1s before the optimization and is fast enough already.

Also, the useEffect calls all of the 4 analytics routes, therefore contents are only rendered after all 4 queries finish executing. So although our optimizations work, they may not make a huge impact to users visually.

ruichen199801 commented 1 year ago

How to test BEST_SAME_CITY query:

  1. Test with 0 saved restaurants (expect no "based on save history" section)
  2. Save "New Asian Star", then visit analytics page (expect no "based on save history" section)
  3. Save another restaurant, then visit analytics page (expect restaurant results in "based on save history" section)
ruichen199801 commented 1 year ago

Issue with #77 (resolved):

imageUrl is not included in the BEST_SAME_CITY query, and the query does not have the same behavior as the original one (e.g. display no result when saving some restaurant), so we revert to the commit before merging the optimized analytics queries.

Currently the optimized queries are archived in the archive/feature/optimize-sql branch.