Reusing existing MySQL connection in Script Reports

ERPNext Reports
1

I have a script report in which query has a number of joins and a lot calculation and the worst part is it cannot be avoided. I have noticed that once I open that report a query will start. Let us say, I set the filters, then on each change in filters a new query will be started. This leads to a few queries and the server CPU getting all used up and this affects the users.

Is there a way, by which the current query could be stopped and the new query be run on the same connection ?

I tried to solve this by the following manner in the report but was not successful :-

  1. Capture connection ID :
    In execute() :
global_conn_id = frappe.db.sql("""SELECT CONNECTION_ID() as id""", as_dict=1)[0]['id']

global_conn_id is a global variable for string that would store the connection ID and was supposed to be updated each time a new query would be run.

  1. Compare current ID before new sql query:
    In the function where I used frappe.db.sql so as to get the data from DB, I would check for any previous connections made like this:
current_conn_id = (frappe.db.sql("""SELECT CONNECTION_ID() as id""", as_dict=1)[0])['id']
if global_conn_id and current_conn_id != global_conn_id:
    kill_string = kill_string + str(global_conn_id)
    frappe.db.sql("KILL %s" %(global_conn_id))
    global_conn_id = current_conn_id

I was successful only to an extent. Because the first query started was never logged, I probably did not capture it at the right time. Hence later, it would run into exceptions where it was trying to kill non-existent connections.

Is there any other way without going around killing connections ?