During this post we will discuss Java Script Procedure to resolve a business use case. In recent times developers noticed query execution is getting slower on the database. Normal queries would often be long running, lasting from several seconds to a couple of minutes. Since these queries were running smoothly last week but as of sudden there is downfall in execution. After the analysis we have found there are some adhoc queries running on the database. These long runner queries consider as resource intensive eating up the Warehouse resources. Due to this, Normal queries to get exceptions in case of resource crunch on WH.
Currently there are three queries running in system for past 50 minutes.
We have developed the following procedure.
Executed the procedure:
This way we can leverage this procedure and can schedule it with Task to run at specified interval.
Also based on the business requirement we can change the threshold value in procedure to identify long runners.