OLTP type fast path without prepare about node-odbc HOT 81 CLOSED

This is a good idea. Should be very doable. Time is the only issue for getting it done.

If you could provide a some mock-javascript on how you see it working, that would be great. Here is what I am thinking

var stmt = db.prepare('insert into data (col1, col2) values (?,?)');

/* other stuff here */

function someServerRequestHandler(req, res) {
    stmt.bind([req.form.col1, req.form.col2]);
    stmt.execute(function (err, data) {
        res.end('done');
    });
}

from node-odbc.

I'm a javascript/node noobie so my mock code probably wouldn't compile. :-)

Yours looks great to me. And I'll be glad to test this anytime you want (or you have time). With some measurable volume for cpu comparison using db2 v10.

Thanks!!

from node-odbc.

And having all three calls separate (prepare/bind/execute) would be the best for sure!!

So:
prepare once
bind once (as long as data types never change)
execute many

from node-odbc.

The groundwork for this has been laid out (https://github.com/wankdanker/node-odbc/tree/statements-and-connections-oh-my). You can experiment by installing that branch:

npm install git://github.com/wankdanker/node-odbc.git#statements-and-connections-oh-my

Then the code would look something like:

var odbc = require('odbc');

var db = new odbc.ODBC();

db.createConnection(function (err, conn) {
    conn.open('connectinon string', function (err) {
        conn.createStatement(function (err, stmt) {
            stmt.prepare('select ? as test', function (err) {
                stmt.bindSync(['testing']);

                stmt.execute(function (err, result) {
                    result.fetchAll(function (err, data) {
                        console.log(data);
                        //then you could stmt.bindSync() again and stmt.execute().
                    });
                });
            });
        });
    }); 
});

I only tested this one time and it worked, but I may have broken something since. :) I will likely modify the API so that you don't have to go through all this just to prepare, bind and execute and make it more like I originally mentioned above. If you get a chance to test and report back anything, that would be super helpful.

Thanks,

Dan

from node-odbc.

Finally got to this.

On 8.23 and installed with your command and all existing code works without any changes. Now on to changing the code to split out the prepare and bind. I'll let you know... and thanks!!

Got these messages during the install, in case any are bad...

npm install git://github.com/wankdanker/node-odbc.git#statements-and-connections-oh-my
npm WARN engine [email protected]: wanted: {"node":">=0.10.0"} (current: {"node":"v0.8.23","npm":"1.2.18"})

[email protected] preinstall /home/andrew/dma/prd/node_modules/odbc
node-gyp configure build

make: Entering directory /home/andrew/dma/prd/node_modules/odbc/build' CXX(target) Release/obj.target/odbc_bindings/src/odbc.o ../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::New(const v8::Arguments&)’: ../src/odbc.cpp:119:7: warning: unused variable ‘ret’ [-Wunused-variable] ../src/odbc.cpp: In static member function ‘static void ODBC::UV_CreateConnection(uv_work_t*)’: ../src/odbc.cpp:171:7: warning: unused variable ‘ret’ [-Wunused-variable] ../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::CreateConnectionSync(const v8::Arguments&)’: ../src/odbc.cpp:218:13: warning: unused variable ‘ret’ [-Wunused-variable] ../src/odbc.cpp: In static member function ‘static v8::Local<v8::Array> ODBC::GetAllRecordsSync(HENV, HDBC, HSTMT, uint16_t*, int)’: ../src/odbc.cpp:745:1: warning: no return statement in function returning non-void [-Wreturn-type] CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o ../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::QuerySync(const v8::Arguments&)’: ../src/odbc_connection.cpp:776:14: warning: ‘params’ may be used uninitialized in this function [-Wuninitialized] CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o ../src/odbc_statement.cpp: In static member function ‘static void ODBCStatement::UV_Bind(uv_work_t*)’: ../src/odbc_statement.cpp:641:21: warning: ‘ret’ may be used uninitialized in this function [-Wuninitialized] ../src/odbc_statement.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCStatement::BindSync(const v8::Arguments&)’: ../src/odbc_statement.cpp:536:3: warning: ‘ret’ may be used uninitialized in this function [-Wuninitialized] CXX(target) Release/obj.target/odbc_bindings/src/odbc_result.o ../src/odbc_result.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCResult::CloseSync(const v8::Arguments&)’: ../src/odbc_result.cpp:486:3: warning: variable ‘closeOption’ set but not used [-Wunused-but-set-variable] SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node: Finished COPY Release/odbc_bindings.node make: Leaving directory/home/andrew/dma/prd/node_modules/odbc/build'
npm http GET https://registry.npmjs.org/bindings
npm http 304 https://registry.npmjs.org/bindings
[email protected] node_modules/odbc
└── [email protected]

from node-odbc.

Something wrong when I try and call db.prepare. Maybe those install messages are an issue? but again, this version works with my original code.

var stmt = db.prepare(sqlc)  
              ^

TypeError: Object # has no method 'prepare'
at /home/andrew/dma/prd/p1qrun.js:175:19
at Database.open (/home/andrew/dma/prd/node_modules/odbc/lib/odbc.js:63:14)

I just looked more closely at this msg from the install:
npm WARN engine [email protected]: wanted: {"node":">=0.10.0"} (current: {"node":"v0.8.23","npm":"1.2.18"})

Do I have to be on 10.0 to test this? was hoping to put that off for a few more days...

from node-odbc.

Hey! Thanks for the feedback. The version you have installed does not have the prepare method exposed as you are using it. Try installing with:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

A bunch of those warnings should be gone and there is a lot more progress in the v0.5 branch including the prepareSync method. See https://github.com/wankdanker/node-odbc/tree/v0.5#preparesyncsql

In v0.5 there are/will be synchronous and asynchronous versions of every method. To get a stmt object the way you have shown above you will need to use synchronous version of the prepare method.

var stmt = db.prepareSync(sqlc);

or asynchronously...

db.prepare(sqlc, function (err, stmt) {
    //do stuff  
});

You do not have to use v0.10 for this. I have only been testing v0.10 though, so there may be bugs with other versions. If npm does not let you install it because the package.json file says >0.10.0, then you can probably force it with npm install -f ...

Good luck and let me know how it goes. :)

from node-odbc.

I'm having an issue with bindSync not working. It seems to work a few times when I start my code but then it fails. The prepareSync call up front worked fine.

And I'm such a javascript noob I can't figure out how to return the failure. All I can get is true or false so far...

But it does work in that INSERTs are getting to the database.

Should I use bind instead of bindSync?

from node-odbc.

same thing with bind... it works a few times, then starts failing but I'm not sure why... once the bind starts failing, the execute doesn't do anything...

and by works, it is INSERTing records... so if I could just debug bind

from node-odbc.

Two things: 1. The execute methods return result objects which should probably be closed before binding and executing again next time. 2. If you are using the asynchronous methods, make sure you do things within the proper callbacks.

Async example (untested) :

db.prepare(sql, function (err, stmt) {
    if (err) console.log(err.message);

    stmt.bind([42], function (err) {
        if (err) console.log(err.message);  

        stmt.execute(function (err, result) {
            if (err) console.log(err.message);

            result.closeSync(); 
        });
    });
});

or synchronously (not good if you are doing this in a web server because it blocks)

var stmt = db.prepareSync(sql);
stmt.bindSync([42]);
var result = stmt.executeSync();
result.closeSync();
//repeat bindSync, executeSync, closeSync

from node-odbc.

Also, just to note, you can mix and match the usage of async or sync method calls. So you might prepareSync(), bindSync() but execute() so that the database io occurs asynchronously.

Example (untested):

var stmt = db.prepareSync(sql);
stmt.bindSync([42]);

stmt.execute(function (err, result) {
    result.closeSync();
});

from node-odbc.

Sorry, can't close the database connection each time. I'm executing millions of these inserts a day. I only open it once.

I must be misunderstanding something...

from node-odbc.

I'm leaving out that this code does a setTimeout to call itself again after 1 sec.

I do the open (and now prepare) up front just once then call the function. The db.query, that does INSERTs, (and now bind/execute) I put in the body of the function, then the setTimeout happens, then the function, etc. etc... this technique has been working great for months.

I passed the "stmt" var (from the prepare) on the function call right after the open and then in the setTimeout in the function so that it stays available.

Confused? I am...

from node-odbc.

Closing the result doesn't close the connection. It just helps release
resources and get the statement ready for another bind/execute.

db.close() is the only thing that actually closes the connection. :-)
On Apr 28, 2013 5:19 PM, "amcintyre99" [email protected] wrote:

Sorry, can't close the database connection each time. I'm executing
millions of these inserts a day. I only open it once.

I must be misunderstanding something...

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/32#issuecomment-17142916
.

from node-odbc.

I have no "result" from the execute. It's an INSERT and I only return "err". I can add "result" during testing but I've learned to not use it for production with just pure INSERTs because it adds overhead since there's nothing to return.

Also, this particular code is node.js http client code long running, all day, once a second.

Other code I have that will really benefit from this "separating out the prepare" is some streaming code that receives millions of XML packets a day. And each packet runs a complex XML INSERT that could really stand to only be prepared once.

from node-odbc.

I added result to execute then result.closeSync in the execute cb.

Same thing. Runs at least once, then continues to look like its running (logging) but no INSERTs after the first set in the database.

from node-odbc.

Even though you are executing an insert statement, a result object is still
created. Node-odbc is unaware/ignorant of the type of query you are
executing. I plan in implementing executeNonQuery which would supress
that behavior.
On Apr 28, 2013 5:40 PM, "amcintyre99" [email protected] wrote:

I have no "result" from the execute. It's an INSERT and I only return
"err". I can add "result" during testing but I've learned to not use it for
production with just pure INSERTs because it adds overhead since there's
nothing to return.

Also, this particular code is node.js http client code long running, all
day, once a second.

Other code I have that will really benefit from this "separating out the
prepare" is some streaming code that receives millions of XML packets a
day. And each packet runs a complex XML INSERT that could really stand to
only be prepared once.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/32#issuecomment-17143286
.

from node-odbc.

If you have any code that you can share that shows the failure, please do.
I will make a test out of it and get it working. Thanks!
On Apr 28, 2013 5:54 PM, "Dan VerWeire" [email protected] wrote:

Even though you are executing an insert statement, a result object is
still created. Node-odbc is unaware/ignorant of the type of query you are
executing. I plan in implementing executeNonQuery which would supress
that behavior.
On Apr 28, 2013 5:40 PM, "amcintyre99" [email protected] wrote:

I have no "result" from the execute. It's an INSERT and I only return
"err". I can add "result" during testing but I've learned to not use it for
production with just pure INSERTs because it adds overhead since there's
nothing to return.

Also, this particular code is node.js http client code long running, all
day, once a second.

Other code I have that will really benefit from this "separating out the
prepare" is some streaming code that receives millions of XML packets a
day. And each packet runs a complex XML INSERT that could really stand to
only be prepared once.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/32#issuecomment-17143286
.

from node-odbc.

Wish I could share the code but it's hitting a secure server with ssl/security keys imbedded... and they don't have a test endpoint/account...

In retesting now it's consistent that the first time it works (INSERTs done) but the 2nd thru n times the bind returns false for some reason. This is with both bindSync/execute and bind cb/execute.

Its probably me trying to pass "stmt" from the prepare each time in the recursive calls. Did I mention I'm a js noob?

Rough code is here and I'd appreciate any help:
https://gist.github.com/amcintyre99/5478705

from node-odbc.

Cool. Thanks for that code. It is a good starting point for a test. Will
take a look tomorrow.
On Apr 28, 2013 6:39 PM, "amcintyre99" [email protected] wrote:

Wish I could share the code but it's hitting a secure server with
ssl/security keys imbedded... and they don't have a test endpoint/account...

In retesting now it's consistent that the first time it works (INSERTs
done) but the 2nd thru n times the bind returns false for some reason. This
is with both bindSync/execute and bind cb/execute.

Its probably me trying to pass "stmt" from the prepare each time in the
recursive calls. Did I mention I'm a js noob?

Rough code is here and I'd appreciate any help:
https://gist.github.com/amcintyre99/5478705

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/32#issuecomment-17144295
.

from node-odbc.

I made a test out of the code that you supplied. You can see it here: https://github.com/wankdanker/node-odbc/blob/master/test/test-prepareSync-multiple-execution.js

I believe I found out what the problem was and this test now passes. Reinstall with

npm install git://github.com/wankdanker/node-odbc.git#v0.5

and try again.

from node-odbc.

Hey great and thanks! I'll try it this evening as soon as I can...

One thing, your setTimeout is inside execute's cb... mine is right after the execute (not in the cb) so that the next http request can happen within a second and not wait on the database cb to complete. Any gotchas with that?

from node-odbc.

Yeah, currently, there would be a gotcha. That is that the synchronous function calls are not queued, whereas the the asynchronous calls are.

If setTimeout is called outside of the cb and your insert query took 3 seconds (for whatever reason) to execute, that means that recursive would be called another 3 times during that duration. Each time it would call bindSync which would not queue up the bind values. So when the next queued execute() occurred, it would only actually insert the values from the last call to bindSync.

If you change your code to call bind asynchronously, then you should be good to go:

...
stmt.bind([vals], function (err) { 
    stmt.execute(function (err, result) {
        result.closeSync();
    });
});
...

Also, I have recently implemented executeNonQuery which you may want to try out so that you do not have to call result.closeSync().

In the example above it would look like:

...
stmt.bind([vals], function (err) { 
    stmt.executeNonQuery(function (err, numRowsAffected) {
        console.log(numRowsAffected);
    });
});
...

from node-odbc.

Still have same problem of everything works 1st time (INSERT is in db) but then on 2nd time thru n, both stmt.bind (and inside it's cb the stmt.execute) seem to fail but neither return anything in their "err" object. By fail I mean no INSERTs occurred.

from node-odbc.

On another box with node 10.5 on it the npm install string gets these msgs:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

[email protected] preinstall /home/andrew/dma/prd/node_modules/odbc
node-gyp configure build

make: Entering directory /home/andrew/dma/prd/node_modules/odbc/build' CXX(target) Release/obj.target/odbc_bindings/src/odbc.o ../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::GetColumnValue(SQLHSTMT, Column, uint16_t*, int)’: ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_min’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_hour’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_mday’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_mon’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_year’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_wday’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_yday’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_isdst’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_gmtoff’ [-Wmissing-field-initializers] ../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_zone’ [-Wmissing-field-initializers] ../src/odbc.cpp: In static member function ‘static v8::Local<v8::Array> ODBC::GetAllRecordsSync(HENV, HDBC, HSTMT, uint16_t*, int)’: ../src/odbc.cpp:794:1: warning: no return statement in function returning non-void [-Wreturn-type] CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o ../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::CloseSync(const v8::Arguments&)’: ../src/odbc_connection.cpp:452:1: warning: no return statement in function returning non-void [-Wreturn-type] ../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::Query(const v8::Arguments&)’: ../src/odbc_connection.cpp:1334:1: warning: ‘sql’ may be used uninitialized in this function [-Wuninitialized] ../src/odbc_connection.cpp:592:22: note: ‘sql’ was declared here ../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::QuerySync(const v8::Arguments&)’: ../src/odbc_connection.cpp:1334:1: warning: ‘sql’ may be used uninitialized in this function [-Wuninitialized] ../src/odbc_connection.cpp:874:22: note: ‘sql’ was declared here CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o CXX(target) Release/obj.target/odbc_bindings/src/odbc_result.o ../src/odbc_result.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCResult::FetchSync(const v8::Arguments&)’: ../src/odbc_result.cpp:363:19: warning: unused variable ‘args’ [-Wunused-variable] CXX(target) Release/obj.target/odbc_bindings/src/dynodbc.o SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node: Finished COPY Release/odbc_bindings.node make: Leaving directory/home/andrew/dma/prd/node_modules/odbc/build'
npm http GET https://registry.npmjs.org/bindings
npm http 304 https://registry.npmjs.org/bindings
[email protected] node_modules/odbc
└── [email protected]

from node-odbc.

The messages you get from npm install on your secondary box are compiler warnings for various things. I am not concerned with those at the moment and do not believe that they are affecting you.

I can see on your second box, that [email protected] was definitely installed which includes the fix that I described earlier. Is that the version that is installed on the box where your testing failed?

You can look at the contents of package.json or run npm version from within the node-odbc install directory to determine the installed version.

If you have the sqlite3 libraries installed, you an run npm test and execute all of the tests. If test-prepareSync-multiple-execution succeeds then a difference between your test and mine is likely causing the problem.

from node-odbc.

Yes, it's failing on your latest version, that I just installed a few minutes ago.

If bind and/or execute are failing, why are there err objects empty?

On the bindSync version (that I won't use) I was getting an error message (with try/catch) but the e.message field was hex:
{ state: '\u001fur�',
error: '[node-odbc] Error in ODBCStatement::BindSync',
message: '�x9\b@�j\t\u0004' }

from node-odbc.

Hmmm. Weird. That is a very good question. I experienced a similar error in the try/catch earlier, but fixed with 0855800 which should prevent destroying an hSTMT when it shouldn't be. That garbled message indicates to me that the hSTMT has already been destroy with SQLFreeHandle somehow.

If you enable debugging by modifying bindings.gyp and define 'DEBUG' under 'defines' like this:

...
 'defines' : [
    'DEBUG'
],
...

Then execute npm rebuild and try your tests again. If you gist the debug log, that might be helpful to know the sequence of events that is happening internally.

from node-odbc.

Ok, did that but dumb question... where did the log go?

from node-odbc.

Oh, the log just dumps to stdout. :)

from node-odbc.

nope, no log - hmmmm

from node-odbc.

I wasn't doing the npm rebuild right..

https://gist.github.com/amcintyre99/5485701

from node-odbc.

The 1st execute inserted rows ok... the 2nd execute did not insert any rows... and no err object

from node-odbc.

Thanks for that log. It is super helpful and I see the problem: https://gist.github.com/amcintyre99/5485701#file-gistfile1-txt-L36, canFreeHandle should be 0. And it's the way I'm passing values around. I should have a fix for this tomorrow if I don't get to it tonight.

from node-odbc.

I just pushed ac63fb2, go ahead and try to install it with:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

It is my best guess as to what was going wrong. If you have trouble again, please post the debug log again.

from node-odbc.

Yes!! u da man!!

Let me try it again on x64 db2 v10.

from node-odbc.

Looking good. No storage or task creep.

I am going to push this out for tomorrow and I'll let you know the amount of db2 cpu decrease. Given my volume, I'm expecting a little. :-)

from node-odbc.

Awesome. I look forward to hearing how it goes. Keep an eye on memory usage too, if you would, just in case I have introduced any memory leaks.

Also, if you haven't tried the executeNonQuery() method as I mentioned in this comment above you might want to give it a shot to squeak out some extra performance.

Good luck! :)

from node-odbc.

I plan on using executeNonQuery and bypassing the driver manager thing. Need all I can get.

from node-odbc.

Dumb question on the placement of the bind.

Would it be possible for me to move the bind to right after the prepare and avoid the bind call every time? since the vars in the array are always the same names, which I assume means storage location... they just have different values each time...

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0002218.html

from node-odbc.

Unfortunately, the bind call is required right now in order to get the values passed from javascript-land to c++-land. This unfortunately also means that each element in the array is evaluated for its type and the parameters are bound again. We can probably come up with an optimized way to deal with this. You would have to keep a reference around to the same exact parameters array though. It would be something like this for example...

var stmt = db.prepareSync(sql);
var params = [];
stmt.bindSync(params);

doIt('hello', 'world');

function doIt(a, b) {
  params[0] = a;
  params[1] = b;

  stmt.rebind(params, function (err) {
    stmt.executeNonQuery(function (err, count) {
      doIt('something', 'else');
    });
  });
}

I don't know though. It might be a pain to implement. We either need to have a new method to tell the C++ side to re-read the values from the params array. Or read the params array on execute* each time. Not sure at the moment which would be better. Or maybe there is a chance that the internal pointers would all actually just work out. Hmm.... Let me test.

from node-odbc.

Started getting this:
16 { state: '22005',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver][DB2/LINUXX8664] SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018\n' }

I don't have any DECFLOAT data types... hmmmm....

from node-odbc.

Also started getting this (didn't get it last night testing):
/home/andrew/dma/prd/p1srun.js:2708: Uncaught TypeError: Cannot call method 'closeSync' of undefined

from node-odbc.

Had rolled in prepare/bind/execute changes on 2 nodes, both long running all day, called Q and S.

Q is working fine (the recursive code running every second) but only 23k runs per 6.5 hour day so not much volume.

The second process S is the streaming one with all the volume (3mill plus) and it's the one that started getting those errors.

I left your new odbc code in place for both. And am running with the new Q but backed off my S changes for now.

from node-odbc.

Re the SQL0420N msg.

This technote is close to explaining what's going on I think:
http://www-01.ibm.com/support/docview.wss?uid=swg21448700

From my code that works (and is working right now):
db.query(sqlc,[""+str+"",resp_ts.toString()],function(err) {

New version that failed: (but doesn't always fail - load related maybe?)
stmt.bind([""+str+"",resp_ts.toString()],function() {

from node-odbc.

Neither posted right. See this gist:
https://gist.github.com/amcintyre99/5489307

from node-odbc.

Might those string values sometimes look like numbers? When we evaluate the values passed in the array, we use v8 to determine the type of data contained within the value. (see https://github.com/wankdanker/node-odbc/blob/v0.5/src/odbc.cpp#L563).

If by chance v8 thinks the string is actually a number we might create a number parameter for a string field. I'm not a v8 pro though, so I'm not exactly sure that is happening.

from node-odbc.

The first parm is always an xml string, well formed. The 2nd parm is always a number (bigint) converted to a string.

The db.query version is working right now. Same data as the stmt.bind version.

Will the bind return an err object yet? I can try it and see if it's getting an error prior to the execute.

from node-odbc.

More debug messages related to the SQL0420N.

https://gist.github.com/amcintyre99/5489891

from node-odbc.

I don't see any reason why bind() would not return an error at this point. I can not create a case where it will error out yet though... If you have a test case for where it should cause an error, please provide.

Is there a reason that you convert the 2nd param (bigin) to a string? I do not use DB2 and know nothing of the function "DECFLOAT" and in what cases that it is invoked. :(

My best guess is that the second parameter in some cases is bound as a string and others as a number of some sort. If you enable the debugging, it will tell us the type that is being used during each bind() call. That might be helpful in figuring out if that is the case.

from node-odbc.

I've always bound the parms the same way. Been working for months with db.query.

Here is debug with just 1 node running. I stopped it right after the error.
https://gist.github.com/amcintyre99/5490123

from node-odbc.

This S code is a streaming app that's receiving millions of these XML packets using node http client:
response.on('data',function(data) {
db.query(...... (now replaced with bind/execute)
}

These can get queued up sometimes with hundreds of the db.query calls (doing INSERTs) per second. Any problem with hundreds of the bind/executes stacking up? all using the same "stmt" from the prepare?

from node-odbc.

Here's the debug of db.query version of S running just fine. Still your latest code.

https://gist.github.com/amcintyre99/5490249

from node-odbc.

Also, these tests during the day are on node 8.23 64bit.

from node-odbc.

I just pulled your latest 5.7 and now the new S is working fine.

from node-odbc.

That's great, I suppose! Wish I knew which fix was the issue you were hitting.

from node-odbc.

It has to be one of the fixes you made this morning because until now I was testing with your version from last night.

from node-odbc.

I thought there was a storage leak but maybe not.

Normally with your odbc 4.4 the 25 streaming S nodes I run take right at 880m of VIRT each according to top. And they don't change all day. I log this daily is how I know.

Right now they lowest of the 25 is at 997m and the highest at 1581m of VIRT.

But it seems to be staying right at those numbers. Did something change from 4.4 that might be making these numbers larger?

The Q process (only 1 node on that 1 sec timer) stayed the same at about 965m of VIRT.

from node-odbc.

A ton has change since the 0.4 series. There are now 4 separate classes which separate the odbc environment, connection, statements and results. One thing that will definitely increase memory usage are the result objects. Even after they are closed they may not be garbage collected by v8 for some time.

I noticed in one of your last logs that result objects were being created. You can avoid that by executing your inserts with stmt.executeNonQuery. That will only return the number of rows affected and not create a result object. That will probably save you memory.

from node-odbc.

Wish I had better news.
https://gist.github.com/amcintyre99/5498042

from node-odbc.

I have a couple comments about your results.

I would not necessarily expect the CPU usage of the database engine (db2sysc?) to change as a result of the changes to node-odbc. I would expect that it will still have the same workload in processing each insert. What I would expect is that the CPU usage of nodejs processes to decrease. Streamlining the process of bind/exec reduces the workload within the nodejs process.

I have absolutely no idea what is going on with your system and how you are running your tests, but I could imagine a situation where an optimized nodejs process could push more data to a database thus causing the database CPU to increase in the database engine while still using the same or less CPU usage within nodejs process. It might be better to get a comparison of throughput per CPU usage; like bytes inserted, inserts completed, etc. per total CPU time or per total wall clock time.

Also, in most of my cases, I never pay attention to VIRT. I only care about RES. Here are two passages from the top man page:

RES -- Resident Memory Size (KiB)
The non-swapped physical memory a task has used.

VIRT -- Virtual Memory Size (KiB)
The total amount of virtual memory used by the task. It
includes all code, data and shared libraries plus pages
that have been swapped out and pages that have been
mapped but not used.

So, the way I understand it, RES is the physical ram that is being take up by the process. Which still has increased in your tests but only by ~20M in the worst case.

Take this all with a grain of salt though. I have never used db2 and don't know what the best practices are for measuring its performance.

from node-odbc.

Avoiding 3 million prepares (aka sql compiles) should have reduced db2 cpu.

At least It always has when I've done something similar on other platforms.

from node-odbc.

I definitely see your point. Let me think about this. I'll come up with a couple benchmarks to compare.

from node-odbc.

I wrote some benchmarks and in the process realized some flaws in statement's bind() queuing. It's going to be best if you update to the most recent version of my v0.5 branch (v0.5.9) and modify your code to avoid the explicit bind() calls.

You can now pass the params directly to execute() or executeNonQuery().

...
stmt.execute([params], function (err, result) {
  result.closeSync();
});
...

Or

...
stmt.executeNonQuery([params], function (err, rowCount) {
  //whatever
});
...

All that aside. I saw significant increase in performance when prepareSync()ing one time and then binding/executing or passing params directly to execute.

$ node bench-prepare-not.js
100000 queries issued in 8.674 seconds, 11528/sec : Query

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 3.383 seconds, 29559/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 3.523 seconds, 28384/sec : Prepare - Execute - CloseSync

I'm not sure what is going on in your situation. You may want to try running with valgrind and viewing the results with kcachegrind. That's what works for me. I was able to ensure that prepareSync was indeed called only one time.

Let me know how things go.

from node-odbc.

will update later today with results from live load

meanwhile, benchmarks from old server running db2 97 32bit - (10k instead of 100k)

$ node bench-prepare-not.js
10000 queries issued in 21.285 seconds, 469/sec : Query

$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

$ node bench-prepare-execute-closeSync.js
10000 queries issued in 9.361 seconds, 1068/sec : Prepare - Execute - CloseSync

and if you would tell me the secret of putting inline code into this editor (like you are doing)...

from node-odbc.

Ouch. I don't like the look of that Segfault! I just pushed out commit 7529f54 which may help with that error. If you want to update your code and try that benchmark again, give it a shot.

Regarding the code highlighting... In the upper right corner of the text editor, there is a link that says "Comments are parsed with GitHub Flavored Markdown". If you click on "GitHub Flavored Markdown" it will show you a cheat sheet of some of the formatting that you can do. The code stuff is in the bottom left of that cheat sheet. Basically three back-ticks (`) in a row and then the programming language. And then three more back-ticks to break out of it. For command line highlighting, I specify bash as the programming language. Hope that helps!

from node-odbc.

others still run fine but...
$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

from node-odbc.

updated with latest daily results - unchanged
https://gist.github.com/amcintyre99/5498042

from node-odbc.

benchmarks from new server running db2 10 64bit - 100k iterations

and with your latest code installed with npm install git://github.com/wankdanker/node-odbc.git#v0.5

$ node bench-prepare-not.js
100000 queries issued in 38.876 seconds, 2572/sec : Query

$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 22.235 seconds, 4497/sec : Prepare - Execute - CloseSync

Your sqlite numbers are better than mine using db2. Hmmmm.....

My box is 8 core Xeon E5-2660 @ 2.2GHz. What is your chip and speed?

from node-odbc.

Commit c982ceb in my v0.5 branch that I just pushed will fix your invalid cursor state. I was hitting it too, but only when using FreeTDS (against remote MSSQL).

Here's my cpuinfo:

 $ cat /proc/cpuinfo | grep "model name"
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz

I am not totally sure about the internals of sqlite, but my guess is that the odbc calls bind right to the library calls. So, all the processing that is happening is within the same process and thus memory can be shared and there is no IO. It's not like there is a separate server that ODBC needs to talk to over TCP or pipe or something.

Here are my numbers against my non-local MSSQL Server:

$ node bench-prepare-not.js 
100000 queries issued in 95.572 seconds, 1046/sec : Query

$ node bench-prepare-executeNonQuery.js 
100000 queries issued in 117.266 seconds, 852/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js 
100000 queries issued in 133.676 seconds, 748/sec : Prepare - Execute - CloseSync

I find it totally weird that executing the benchmarks against non-local databases result in higher throughput when calling the normal query mechanism which does prepare, bind, execute, fetch, close.

from node-odbc.

"Deferred Prepare" may explain your non-local weird results: (does MSSQL have same feature?)
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/c0007239.html
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008789.html

Of course, my db is local on same box as node.

from node-odbc.

old box
$ node bench-prepare-executeNonQuery.js
10000 queries issued in 7.634 seconds, 1309/sec : Prepare - ExecuteNonQuery

new box
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.185 seconds, 4954/sec : Prepare - ExecuteNonQuery

so ExecuteNonQuery is definitely the fastest. I'll switch to it asap.

from node-odbc.

just fyi, because you mentioned "talk to over TCP or pipe"...

with no db2cli.ini file so defaults to TCP
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 29.763 seconds, 3359/sec : Prepare - ExecuteNonQuery

with db2cli.ini and Protocol = LOCAL so uses IPC
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.172 seconds, 4957/sec : Prepare - ExecuteNonQuery

from node-odbc.

Just for the record here are those benchmarks for local MySQL:

$ node bench-prepare-not.js
100000 queries issued in 10.528 seconds, 9498/sec : Query

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 6.419 seconds, 15578/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 7.189 seconds, 13910/sec : Prepare - Execute - CloseSync

from node-odbc.

when I run this: (still with node 8.23 by the way)
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.172 seconds, 4957/sec : Prepare - ExecuteNonQuery
my 8 core server only gets to about 5% cpu with always 0% wait (0.0%wa in top).

Is it possible your MySQL run is using more of your cpu somehow? what does yours max at?

Node version difference maybe? number of threads?

from node-odbc.

It might be using more CPU; will have to check that out later. I'm on node v0.10.5 which uses a different thread pool mechanism than v0.8. Node v0.8 uses libeio and uses the default number of threads which is 4. Node v0.10 uses a custom thread pool built in libuv which supposedly dynamically allocates threads. I haven't looked at that code yet though, so not sure how it works.

All in all, threading in v0.10 should probably be better.

from node-odbc.

According to this thread I had with Ben, 8.x also used libuv:
https://groups.google.com/d/msg/nodejs/GvO8WD6GPnc/HjBVVtn3M_cJ

I was trying to implement your tip in your readme but never could figure it out.

from node-odbc.

You are correct that v0.8 also uses libuv; it is the cross platform layer that node uses. However, the version of libuv used with v0.8 does not have it's own thread pool, it is farmed out to libeio.

Yeah, not sure how worthwhile that tip is, especially if you are only opening one connection per process. It really only makes a difference if one process opens multiple connections.

from node-odbc.

VIRT back to normal !!
https://gist.github.com/amcintyre99/5498042

from node-odbc.

node 10.5 x64 db2 10.1 - your latest with no dynodbc

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 19.872 seconds, 5032/sec : Prepare - ExecuteNonQuery

top still only showing 5% with no wait while running... not sure what that means...

from node-odbc.

I think we got this working; at least the feature is implemented. If you have any further issues with it, let's open new issues for the specific problem.

Thanks again for your testing and benchmarking, @amcintyre99.

from node-odbc.