Problem with response time of millions of records

[Dennis-96]

1. What versions are you using?

2. Describe the problem

The problem I have is that when I run a query with a return of a base with around 11 million data, it takes too long, more than half an hour, so I did a direct test using sqldeveloper and it took 0.029 seconds. But in node js it takes too long, is there a way this doesn't happen?

3. Include a runnable Node.js script that shows the problem.

      let sql, binds, options, result;
      // Get a non-pooled connection
      oracledb.initOracleClient({libDir: 'C:\\oracle\\instantclient_19_8'});
      connection = await oracledb.getConnection(dbConfig);

      sql = "SELECT * FROM SGS.VW_RMS_W_INFORMATION V WHERE V.AUTOR_EDITOR LIKE '%P%'";

    binds = {}; 

    // For a complete list of options see the documentation.
    options = {
      outFormat: oracledb.OUT_FORMAT_OBJECT,   // query result format
      // extendedMetaData: true,               // get extra metadata
      // prefetchRows:     100,                // internal buffer allocation size for tuning
      // fetchArraySize:   100                 // internal buffer allocation size for tuning
    };

    result = await connection.execute(sql, binds, options);

    console.log("Metadata: ");
    console.dir(result.metaData, { depth: null });
    console.log("Query results: ");
    console.dir(result.rows, { depth: null });

[cjbj]

How many records does the query actually return?

Are you connecting to the same DBs with the same table - and indexes? And running SQL Dev and Node.js on the same computer?

If you want to return a large number of rows through node-oracledb you would want to use a ResultSet or stream the rows. If you don't then all rows have to be held in Node.js memory, and there will be a lot of reallocations as data is successively fetched.

What values did you actually use for prefetchRows and fetchArraySize? Have you read Tuning Fetch Performance?

[Dennis-96]

How many records does the query actually return?

With the use of like it returns 5222370 records Without the use of like the query returns 14335121 of records

Are you connecting to the same DBs with the same table - and indexes? And running SQL Dev and Node.js on the same computer?

I am connected to a view made on the base, the base is not on my computer, but remotely using nodejs

What values did you actually use for prefetchRows and fetchArraySize? Have you read Tuning Fetch Performance?

If I use with 100, 50000 and 100000, in the case of 100 it takes more than half an hour and in the case of 50000 and 100000 it takes about a minute and 50 seconds //first test prefetchRows: 101, fetchArraySize: 100, // second test prefetchRows: 50001, fetchArraySize: 50000, //third test prefetchRows: 100001, fetchArraySize: 100000

I really don't know what I may be doing wrong as I need a response time of less than 3 seconds which is what I get when I check with the SQL developer, please help me

[Dennis-96]

Completing the question you asked me about the indexing of the table, I do all that in a view and I consult from there and in sql developer the answer is almost immediate, but through nodejs the query becomes too slow

[cjbj]

Is SQL Developer run on the same computer as Node.js? It seems optimistic if SQL Developer is returning 5222370 rows in 0.029 seconds over a network. Are you sure it's not returning a page of rows?

What are you doing with all the rows in Node.js? Can you use a ResultSet?

Regardless, you have various options mentioned above, including tuning the SQL statement with appropriate indexes.

[Dennis-96]

Is SQL Developer run on the same computer as Node.js? It seems optimistic if SQL Developer is returning 5222370 rows in 0.029 seconds over a network. Are you sure it's not returning a page of rows?

You are right, I check it carefully and it returns a page of rows in sql developer

What are you doing with all the rows in Node.js? Can you use a ResultSet?

At the moment I am only consulting the logs, but the answer I have to implement in a real-time search engine When I use resultSet the response comes out as undefined

Can you paginate the results with node js? Or what do you recommend?

[cjbj]

See Limiting Rows and Creating Paged Datasets.

Regarding ResultSets, see https://github.com/oracle/node-oracledb/blob/master/examples/resultset2.js

Hope this helps.

[Dennis-96]

Thank you very much friend, right now I will read what you send me and if I have any problem I will be commenting on this post

[cjbj]

Don't forget to tune prefetchRows if you use a ResultSet.

[Dennis-96]

Ok friend I will take it into account, with what you indicated I am getting the answers I need, I will continue doing tests to see that everything is going well, thank you very much for your help

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.