executemany command is slow comparing with python cx_oracle

[binlu999]

We are using node.js to call oracle database stored procedure. The code segment is:

        let spName = config.TARGET_IMPORT_SP_NAME
        let plsql = `CALL ${spName} (:input, :output)`;
        const options = {
            bindDefs: [
              { type: "OCS_ADMIN.SP_I_WML_ROUTE"  },
              { dir: oracledb.BIND_OUT , type: "DB_TYPE_NAME"  },
            ]
          }; 
        let startTime = (new Date()).getTime();
        log.info(" Start DB call %s", startTime);
        **const result = await connection.executeMany(plsql, binds,options);**
        let endTime = (new Date()).getTime();
        log.info(" End DB call %s", endTime);
        log.info("Duration of DB execution %s ", endTime - startTime);
        log.info(`Oracle result: `+JSON.stringify(result));

The execution time connection.executeMany is about 1.8 seconds. However, we have a python implementation with exactly same logic, same loading, same run time environment and same database setting, the python execute the same stored procedure only in 200 ms. Is there anyway we can improve or anything I did wrong with executeMany? Thanks

[anthony-tuininga]

Can you provide the SQL used to create the table and a standalone script that demonstrates the problem? Not just the Node.js version but also the Python version? If there is a discrepancy between the two implementations I would like to know about it!

[binlu999]

Hi Anthony, We implemented both very simple way. In python,

result_args=cursor.callproc(Configuration.TARGET_IMPORT_SP_NAME ,

  | [dbObject, dbReturnObject])

Where we are calling exactly same database stored procedure with same data.

Thanks

[anthony-tuininga]

Yes, I understand that. But without the SQL used to create the table, the stored procedure and the data that you are using I can't answer your question.

[anthony-tuininga]

I do have Zoom, but I'd prefer to get the necessary information on this issue. Please provide it!

[binlu999]

Thanks Anthony. We will works on sample data table and stored procedure and provide to you. Meanwhile, I just tested with both node-oracledb (in node.js) and cx_oracle (in python) and we found for exactly same database and test data, the node-oracle required 44 round trips while the cx_oracle required 2 round trips

[anthony-tuininga]

That's good information. Hope to see the code and data soon so we can get to the bottom of the discrepancy between the two!

[milindkhurd]

Anthony, how does it matter how the table and stored proc was created? In addition, it is the same Oracle proc called from nodejs and Python code. What arguments are passed to the proc may make a diff. As I see the argument is a user defined Oracle type and the node version may not be able to handle udt's the same as Python? Binlu9999 can you first share the type create statement?

[cjbj]

When we get the testcase we can take a look. I have some thoughts but want to see the code before sending you off on tangents.

There are bound to be some Node.js v Python differences, but most of the heavy work is done in the ODPI-C layer which is common to node-oracledb and cx_Oracle.

[anthony-tuininga]

@milindkhurd, it doesn't really matter how the table and stored proc were created -- but we want to see the test case for both node-oracledb and cx_Oracle and be able to run them ourselves to determine the source of the issue. For that we will need the table and stored proc!

Yes, node-oracledb can handle udt's in the same manner as Python. There are some differences, though, so that's why the test case is important. :-)

[stale[bot]]

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

[stale[bot]]

This issue has been automatically closed because it has not been updated in 28 days.