Performance is slow with larger result sets
I've noticed that this connector is slower than jdbc in terms of handling the result sets and I have an idea. The idb connector is converting the result set to json, where as jdbc is not. I'm thinking that this may be the major difference in execution time that I'm seeing. I've tried turning debugging on for the connection but didn't get what I was hoping for.
I'd like someone (I'm willing to help with assistance) to modify dbconn.cc to accumulate time spent performing various functions. Specifically, how much time is being spent converting the odbc result to json. Then, add debug() statements to report these times to the console.
If we find that the json conversion is where a large majority of time is being spent, I would like the maintainers to explore alternate solutions.
Thanks!
Comments (12)
-
reporter -
Hi @krisbaehr,
Could you share the Node.js code you're using to calculate the Node.js stats? If the code is more than 100 lines then maybe create a Bitbucket Snippet.
-
reporter @aaronbartell Be forewarned, I'm pretty new to this Node.js thing!
https://bitbucket.org/snippets/krisbaehr/keBa4E/node-sql-sp-call-for-performance-testing
-
Account Deactivated Hello @krisbaehr , I have rebuilt the idb-connector to allow flexible column width to save memory usages. You can reinstall idb-connector and use the new environment value MAXCOLWIDTH to change the default value 32766. (Note: smaller MAXCOLWIDTH value costs less memory but may trim long text)
My test --
for(var colwidth = 128; colwidth < 65535; colwidth *= 2) { process.env.MAXCOLWIDTH = colwidth; var dbconn = new db.dbconn(); // fetch / exec ...... }
bash-4.4$ node bench.js 8000 fetchAll took 2782.625468 ms -- Row Count: 8000 -- Col Width: 128 fetchAll took 1727.906687 ms -- Row Count: 8000 -- Col Width: 256 fetchAll took 1963.632875 ms -- Row Count: 8000 -- Col Width: 512 fetchAll took 1600.714552 ms -- Row Count: 8000 -- Col Width: 1024 fetchAll took 1616.5393869999998 ms -- Row Count: 8000 -- Col Width: 2048 fetchAll took 2070.021549 ms -- Row Count: 8000 -- Col Width: 4096 fetchAll took 3308.1328750000002 ms -- Row Count: 8000 -- Col Width: 8192 fetchAll took 4757.888971 ms -- Row Count: 8000 -- Col Width: 16384 fetchAll took 7919.675255 ms -- Row Count: 8000 -- Col Width: 32768
-
reporter @mengxumx Thank you for this! I appreciate your diligence in resolving this performance issue. I will give it a shot soon.
-
@krisbaehr, any luck? I'm hoping this shows proof of concept while we work on a more permanent fix.
-
-
assigned issue to
-
assigned issue to
-
Account Deactivated -
Account Deactivated @krisbaehr Does version 1.0.6 fix the problem? I forgot to mention that in the fix there is no more environment variable to set the column width. The accurate column width is calculated automatically to save memory usage.
-
reporter @mengxumx, We switched over to version 1.0.6 last week and ran some tests. There was a significant performance improvement! Before db2a was performing about the same as JDBC at 500 rows, after our various tweaks, including OS tweaks for improved multi-threaded processing. Now, 1.0.6 is equal with JDBC at 1000 rows. At 2000 rows, it's still performing pretty well and flat out smokes db2a (I'm not sure what version that was). Thanks for everything.
-
Awesome!
-
Account Deactivated - changed status to resolved
v1.0.6 fixed it.
- Log in to comment
Node - SQL is doing the same thing the RPG SP is doing. Node - RPG is calling an SP with the idb connector. Java - RPG is Java calling the same SP as node using jdbc.