kadler
commented
1 year ago Do you know about such performance differences already with SP regarding IBM I and your package / IBM ODBC driver?
The old db2a package (which became the idb-connector package) connects to the database through libdb400, which is an in-job, in-memory interface. For ODBC, it must connect through a socket to the host server. Even on localhost, sending data over a socket will be slower than just accessing the data through a pointer, especially if it's large amounts of data.
I don't think Node 20 will bring any performance difference in this case, since the performance is being impacted by the database connection which in either case is written in C++ code and unaffected by any changes in Node.js runtime.
There may be some connection options which could affect your performance. You can also check out the performance tuning ODBC docs.
mlitters
stale[bot]
Environment: IBM i 7.3 (need to update to 7.4 now)
Old version: Node 6 (yes I know - it is a long story.... ;-) ) Native IBM Node.js driver const db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');
New version: Node.js 14.21.3 IBM ODBC driver for PASE in the actual version (July 2023)
Hi there,
after getting the Stored Procedure with the larger OUT Parameter to work thanks to your fix with 2.4.8 (thanks a lot again!) we recognized performance problems with the ODBC connection compared to the old Node.js which was running under Node 6 with the old IBM database driver (yes - I started with Node.js back in early 2015 with V 0.12 :-D ).
I now created some test scripts which call the same SP with promises for 10 times in parallel and collect them with promise.all and get values around 400ms for the old IBM DB2 driver as well as the idb-pconnector. With ODBC I get around 800ms for the same job.
In real life it was much worse when a lot of users access the same script in parallel. So about 100-300 users access my Node.js script at the (nearly) same time and each of them starts the 10 requests to the SP.
With the old IBM DB driver we have response times around 400ms - 10 seconds (this is something we are also working on because we are sure it is something in the RPG processes behind the SP) and with the ODBC Version we get response times around 500ms (which is confusing to me because in my above test which called the SP directly the best response is 700ms and that was only one time and late at night when only a few people worked on the system) to 70 seconds (!!!). Most times with the ODBC version we have more than 30 seconds while the old version is normally under 3 seconds (which is very bad at all).
I also tried the connection pool with and without reuse. With this I get the same response times. Sometimes they are even worse...
I tested it with and without CALLBACKS and get the same results.
So first of all my question is: Do you know about such performance differences already with SP regarding IBM I and your package / IBM ODBC driver?
Because my customer need to go on and leave the Node 6 now (since 5 years of course...) I need to change my scripts to the idb-pconnector but I don't want to do that... With the ODBC it is much easier to develop of course.
After switching to 7.4 we will convert the Node 14 to Node 20. Maybe that will bring a performance boost?
I know that the above scenario is very specific and the customers system is heavy loaded. For better understanding and compare I need to create a test environment on my system, which I will do later. With this you will be able to reproduce that.
First of all it would be interesting, if you or anybody else made the same experience?
Many thanks for your response and have a great day.
Kind regards Markus