search

oracle / node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
http://oracle.github.io/node-oracledb/
Other
2.24k stars 1.07k forks source link

How to handle long-running queries? #1621

Closed Andrija-Markovic closed 8 months ago

Andrija-Markovic commented 8 months ago

Oracle DB Version: "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.19.0.0.0" OracleDB.versionString: 6.1.0 OracleDB.oracleClientVersionString: could not get it because I am running in Thin mode process.platform: linux process.version: v16.20.2 process.arch: x64

package.json dependencies: "oracledb": "^6.1.0", "@types/oracledb": "^6.0.1",

Problem Description

I want to preface that this is purely a question and not an issue that I have to report--just need some advice to see if node-oracledb can be used to solve the problem or if a larger app restructuring needs to be done.

I am using node-oracledb in a reporting app where users can select different filters from drop downs and search for accounts in the DB based on those filters. Naturally, the app would return as many accounts that match the filters as the user asks for (this is usually between 10 and 50 accounts). The DB has many accounts and sometimes there are a lot of filters which causes queries to run anywhere between 3 seconds and 2 minutes. I have noticed this behavior both when my app executes queries, as well as when I directly execute queries in SQL Developer. To be perfectly clear, I have seen the exact same query run for 10 seconds and then another time run for 100 seconds. The app has many users and my poolMax is 4 so if one query runs for a long time, it hogs one of the DB sessions and other users need to wait in the queue for their turn. If several queries take long time to execute, timeout errors start flooding the app. Are there any ideas how to overcome this? I would like to purposefully leave the question open-ended and hear your thoughts. Ideally, node-oracledb could be configured to solve this but adding an external queue which I have more control over might be needed. Thanks for the help in advance!

Andrija-Markovic commented 8 months ago

I know that my question was very open-ended so I get why there were no comments. While I was waiting, I was reading through the node-oracledb documentation and I found out that each connection has a callTimeout property which allowed me to fine tune my app.

sharadraju commented 8 months ago

Hello @Andrija-Markovic Apologies that we could not get back to you sooner. Glad that you found out about callTimeout property though :) There are other timeouts you can look at as well.