👀 Views: 86 💬 Answers: 1 📅 Created: 2025-07-02

oci oracledb nodejs performance JavaScript

I'm maintaining legacy code that I've been experiencing high latency issues when querying partitioned tables in an Oracle Cloud Infrastructure (OCI) Autonomous Database from my Node.js application. The queries are relatively straightforward, but some take significantly longer than expected, often around 5-10 seconds, while others execute almost instantaneously. For instance, when I execute this query: ```sql SELECT * FROM my_partitioned_table WHERE partition_column = '2023-01-01'; ``` I notice the execution time spikes. I've tried optimizing the queries by ensuring that the partitioning scheme aligns with the data access patterns, but the performance remains inconsistent. I also checked the database resource limits and confirmed that I have sufficient CPU and memory allocated. Here's the code snippet I use to connect to the database and execute the query: ```javascript const oracledb = require('oracledb'); async function queryDatabase() { let connection; try { connection = await oracledb.getConnection({ user: 'my_user', password: 'my_password', connectString: 'my_connect_string' }); const result = await connection.execute( `SELECT * FROM my_partitioned_table WHERE partition_column = :date`, { date: '2023-01-01' } ); 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); } } } } queryDatabase(); ``` I have tried using different connection pool settings and have also experimented with various query patterns, but nothing seems to yield consistent results. I even enabled SQL monitoring in the OCI console to analyze the execution plans, and they seem fine, with no full table scans appearing. I'm starting to suspect that it could be related to the Node.js OracleDB driver or some network latency. Could anyone provide insights or suggestions on how to diagnose and resolve this high latency issue? What's the best practice here?