search

avniproject / avni-reporting

GNU Affero General Public License v3.0
0 stars 1 forks source link

Optimize long running report queries causing CPU spike #21

Closed 1t5j0y closed 4 years ago

1t5j0y commented 4 years ago

Optimise few JSS queries that are taking long time to execute which are spiking the CPU on the read database.

  1. https://avni.freshdesk.com/a/tickets/798 DB user: jss Log file - Search for text: "duration" (value over 50000ms) Reports using these queries: https://reporting.avniproject.org/question/143

  2. https://avni.freshdesk.com/a/tickets/802 DB user: jsscp Log file - Timestamp:11:40:16 Reports using this query: https://reporting.avniproject.org/question/520 https://reporting.avniproject.org/question/476 https://reporting.avniproject.org/question/940, Jasper JSSCP Aggregate - Antenatal services during pregnancy.

  3. https://avni.freshdesk.com/a/tickets/804 DB user: jsscp (Query structure is similar to case 1 above) Log file - Timestamp:04:49:36 Reports using this query: https://reporting.avniproject.org/question/940 https://reporting.avniproject.org/question/683

Steps to optimize: Refer to this doc

arjunk commented 4 years ago

how does someone QA these cards?

1t5j0y commented 4 years ago
  1. Compare with previous version to ensure output is the same
  2. Ensure that no CPU spike ticket is created when report is executed
1t5j0y commented 4 years ago

Closed tickets 798, 802 and 804