OCI Database: Performance Issues with Query Execution Using Node.js SDK
I'm learning this framework and I'm stuck on something that should probably be simple... Hey everyone, I'm running into an issue that's driving me crazy. I've searched everywhere and can't find a clear answer... I'm experiencing significant performance issues when executing a complex SQL query against my Oracle Cloud Infrastructure (OCI) Autonomous Database using the Node.js SDK. The query involves multiple joins and aggregates over a dataset of about 10 million rows. While testing this locally with a direct connection using SQL Developer, the query completes in about 3 seconds. However, when I run it through the Node.js application, it frequently times out after 30 seconds, throwing an `ORA-01013: user requested cancel of current operation` error. Here's the sample code I'm using: ```javascript const oracledb = require('oracledb'); async function runQuery() { let connection; try { connection = await oracledb.getConnection({ user: 'myuser', password: 'mypassword', connectionString: 'mydb_high' // TNS entry for high performance }); const result = await connection.execute(` SELECT a.column1, b.column2, SUM(c.amount) FROM table_a a JOIN table_b b ON a.id = b.a_id JOIN table_c c ON b.id = c.b_id WHERE a.status = 'ACTIVE' GROUP BY a.column1, b.column2 `); console.log(result.rows); } catch (err) { console.error('Error executing query:', err.message); } finally { if (connection) { try { await connection.close(); } catch (err) { console.error('Error closing connection:', err.message); } } } } runQuery(); ``` I've tried several optimizations, such as increasing the connection pool size and explicitly setting the SQL execution timeout in the `execute` call, but nothing seems to help. I also checked the database performance metrics, and there are no evident bottlenecks or high loads when the query runs. Is there a better way to handle this type of query in Node.js with OCI, or any specific settings that I might have overlooked that could improve performance? This is part of a larger CLI tool I'm building. I'm working on a web app that needs to handle this. Any ideas what could be causing this? I'd really appreciate any guidance on this. I've been using Javascript for about a year now. I'd really appreciate any guidance on this. The project is a microservice built with Javascript. Thanks for taking the time to read this! I'm working with Javascript in a Docker container on Windows 11. Thanks for taking the time to read this! This is part of a larger REST API I'm building.