Re: Is bind useful for one-time queries and commands?

Any query to a DB2 server must first go through a bind step. The bind step not only validates the query and parameters, but it also 'compiles' the query into the most efficient form (as the database engine sees it) given the data queried, indexes available, table statistics, etc, etc. The results of a bind (the final query executed internally) one day, may not be the same as the results of a bind on another day because contents of one or more tables may have grown (or shrunk) making it more efficient to execute the query differently now. This may not appear to make much sense if you're only querying one table, but if your joining multiple tables with sub-selects, it can have a real impact. The bind step is a fact of life with DB2. You either do it in advance or the DB2 server will do it for you internally when the query is executed.

There's significant overhead involved in the bind step. If you're making repetitive database operations, that overhead can be greatly reduced by binding the queries in advance. When the query is bound, DB2 stores the final compiled version of the query within its own environment and the program executes the stored, bound query directly, bypassing the internal bind step on every call. In compiled languages like COBOL, you submit a pre-compile step which scans the code for queries, binds them, then comments the original code replacing it with new calls to the bound queries. If the database statistics change significantly later, the bound queries may become inefficient again and will require re-binding. Rebinding existing stored queries happens automatically with a database re-org (so don't have to continually update your code, but you do have to bring the databases down for the re-org). If your queries are ad-hoc, one time operations, then there's nothing to be gained by binding in advance.

At least that's how binding in DB2 works (to my recollection) with compiled languages. I don't know how DBI and interpreted Perl handle the bind step. It may very well be that what you call a bind step through the DBI interface may have nothing to do with the DB2 bind step.

Comment on Re: Is bind useful for one-time queries and commands?

Replies are listed 'Best First'.
Re^2: Is bind useful for one-time queries and commands? by talexb (Chancellor) on May 20, 2008 at 14:10 UTC
I'm confused .. your initial statement Any query to a DB2 server must first go through a bind step. ... contrasts with your closing statement ... At least that's how binding in DB2 works (to my recollection) with compiled languages. I don't know how DBI and interpreted Perl handle the bind step. It may very well be that what you call a bind step through the DBI interface may have nothing to do with the DB2 bind step. Do you have experience with DB2 and Perl, or is this more of an educated guess? Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply]
Re^3: Is bind useful for one-time queries and commands? by ruzam (Curate) on May 20, 2008 at 19:00 UTC
The DB2 server will execute the bind step internally if it has not already been done by the application. In other words, DB2 will intercept it's own query requests and bind them (temporarily) before actually getting the data. I don't know if or how long DB2 might cache ad hoc query requests, it's probably configurable. A cached query request would effectively eliminate the need for a bind step at all from the application side. I have experience with DB2, but not DB2 and Perl. It's an educated guess.	[reply]
Re^4: Is bind useful for one-time queries and commands? by talexb (Chancellor) on May 20, 2008 at 19:10 UTC
Update: Sorry -- I did the benchmarks wrong. Please disregard. I have updated the thread in question, in case you are interested in a more up to date benchmark. OK -- I've run benchmarks and found that `bind` doesn't slow things down at all -- my test actually ran faster with `bind`, which is what I should have expected. Now I'm trying to understand whether the type checking that `bind_param` does is really useful. Alex / talexb / Toronto "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds	[reply] [d/l] [select]
Re^2: Is bind useful for one-time queries and commands? by sabari (Beadle) on May 20, 2008 at 11:34 UTC
Dear Friends, I am very new to perl ,can any one post the sample code for the database operation with bind ?. Best Regards, S.Sabarinathan,	[reply]
Re^3: Is bind useful for one-time queries and commands? by sathiya.sw (Monk) on Nov 19, 2008 at 14:06 UTC
check the following sample code ( for sabari ).. #Establish the connection to the database. my $dbh = DBI->connect( 'dbi:Pg:database=test','test','1234',{RaiseErr +or => 1,AutoCommit => 0}) \|\| die "Database connection not made: $DBI::errstr"; #It get the port number and IP address from the data base. my $sql = qq{ SELECT port,ipaddr,log_file FROM details where name='$na +me'}; my $sth = $dbh->prepare( $sql ); $sth->execute(); #bind_columns - It stored the port number and IP address to variable. my( $port, $ipaddr,$log_file ); $sth->bind_columns(\$port, \$ipaddr,\$log_file); #It fetch the values from table $sth->fetch(); $sth->finish(); #Dis-connect the database,if we miss it tells warnings, that destroy h +ad been #called with out disconnect. $dbh->disconnect(); [download]	[reply] [d/l]