| Author |
Message
|
| prasadpav |
 Posted: Tue Jan 15, 2013 7:13 am Post subject: Question(s) on prepared statements for a JDBCProvider |
 |
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
Hi,
I'm configured a JDBCProvider using JDBC configurable service to communicate with remote AS400 database. I'm creating a prepared statement (for SQL statements) and CallableStatements (for calling stored procedures). However, I want to cache them for reuse and to make application more performant. I'm not sure how it is going to work within the broker because there is a disjoint between the connection management and the prepared statement cache management. The JDBC connections are managed by the broker and the user's custom code create/maintain prepared statements by making use of the connections. My questions:
1) Assuming there is no connection pooling, if a database connection is opened and a prepared statement is cached then after 1min of message flow idle time the broker default behaviour is to release the database connection. What happens to the cached prepared statement object? Is broker going to close the prepared statement too? I guess broker don't know about this prepared statement object and it is left with the garbage collector to release it. And this cached object will not work if a new connection is made afterwards and before GC releases it. Agreed?
2) If the connection pooling is in place, then is it possible to maintain prepared statement cache? The JCN user code cannot possibly map the connection object to the prepared statement cached object and i think it becomes a nightmare to get it working.
So, may I ask if it there a recommended approach for creating prepared statement cache with/without connection pooling?
As of now this is what I have - Prepared statement is a class member (private variable) of Java compute node class in a single connection setup (connection pooling is not yet enabled). However, I feel that even in this scenario I might end up in problems (Question #1 above) if I use the cached object.
Thanks in advance for taking time in going through this post. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Jan 15, 2013 8:23 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You may like to investigate the use of Stored Procedure, which gives you the benefit of a prepared statement, but is pre-compiled on the host, and is not dependent on the JDBC connection remaining active.
At the very least, submit an : R F E : since we've hammered the connection pooling issue before but received stiff resistance about modifying how WMB manages connections from IBM Hursley.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jan 15, 2013 8:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can simply implement your own JDBC connection management, as well.
From Java, you can also call the Message Broker API to retrieve the properties of a JDBCConfigurable service without actually asking for Broker to create a connection using one.
Then you can use the information in the service to create your own connection.
These are obviously work-arounds.
You could do something more obnoxious by using a timeoutnotification node to ensure your flow doesn't go inactive for more than 1 minute, as well. |
|
| Back to top |
|
|
| prasadpav |
| Posted: Wed Jan 16, 2013 7:47 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
@mqjeff - We initially went down the route of creating our own connections. We shifted to JDBC configurable service because it gives the benefits of global transaction participation and abstracting the connection/security details related to the database away from the code.
Anyway, as per the JDBC specification, the prepared statements are closed when the connection is closed. Hence, I tried this scenario in a non-connection pooling scenario and broker (v 7.0.0.2) behaves as per the specification i.e. when broker closes the JDBC connection after default 60 secs idle time, the prepared statements are also closed (verified using the result from PreparedStatement.isClosed() and Connection.isClosed()).
For connection pooling this is the conclusion to which I arrived (correct me if I'm wrong), there is no built-in support for prepared statement pooling and unless a custom prepared statements pooling is implemented to associate connection object to statement objects there is no point in creating prepared statements.
Thanks everyone for your sharing your views. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jan 17, 2013 5:51 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Which kind of SQL Statement did you use? What was the class name for that SQL statement? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| prasadpav |
| Posted: Thu Jan 17, 2013 6:36 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
It is a simple SELECT statement on a table of the following format. The class name is "PreparedStatement". Here is the outline of my java code that I've used:
| Quote: |
/* JCN */
class sfsdf extends MbNode
{
PreparedStatement pstmt = null;
evaluate(inputAssembly)
{
Connection conn = getJDBCType4Connection("CUSTOM_JDBC_DB_AS400", JDBC_TransactionType.MB_TRANSACTION_AUTO);
String selectPrepSQL = "SELECT * FROM CUST_TAB WHERE COL_A = ? AND COL_B = ?";
String connResult = (pstmt == null)? "[[Not applicable because prepared statement is NULL]]" : String.valueOf(pstmt.getConnection().isClosed());
String pstmtResult = (pstmt == null)? "[[Not applicable because prepared statement is NULL]]" : String.valueOf(pstmt.isClosed());
System.out.println("Prepared statement connection closed? : " + connResult);
System.out.println("Prepared statement closed? : " + pstmtResult);
if ((pstmt == null) || (pstmt.getConnection().isClosed()))
{ System.out.println("Preparing statement ....");
pstmt = conn.prepareStatement(selectPrepSQL);
}
else
System.out.println("Reusing the Prepared statement!!!");
pstmt.setInt(1, 0);
pstmt.setString(2, "D");
Result rs = pstmt.executeQuery();
}
}
|
After a successful SELECT, I waited for 60 seconds for broker to drop the connection. Then when I run the same test again i got the following output from "stdout", which shows that both the connection and the prepared statements are closed.
| Code: |
.....
Prepared statement connection closed? : [[Not applicable because prepared statement is NULL]]
Prepared statement closed? : [[Not applicable because prepared statement is NULL]]
......
Prepared statement connection closed? : true
Prepared statement closed? : true
..... |
Hope it answered your question! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jan 17, 2013 6:45 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You might want to be looking into the Mbxxx classes for manipulating SQL...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| prasadpav |
| Posted: Thu Jan 17, 2013 7:02 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
Here's the excerpt from the Message Broker info center:
| Quote: |
| MbSQLStatement provides support for accessing an external ODBC Database. |
So, I guess MbSQLStatement & createSQLStatement classes do not work for JDBCProviders. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jan 17, 2013 7:06 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| After a successful SELECT, I waited for 60 seconds for broker to drop the connection |
But did you try using the statement WITHIN the 60 seconds?
Regards
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| prasadpav |
| Posted: Thu Jan 17, 2013 7:36 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
| Quote: |
| @mgk - But did you try using the statement WITHIN the 60 seconds? |
Ohh yes. I did. I forgot to add that detail in my previous posts. Here's the "stdout" when I ran a test within 60secs.
| Quote: |
....
Prepared statement connection closed? : [[Not applicable because prepared statement is NULL]]
Prepared statement closed? : [[Not applicable because prepared statement is NULL]]
Preparing statement ....
......
Prepared statement connection closed? : false
Prepared statement closed? : false
Reusing the Prepared statement!!!
.....
Prepared statement connection closed? : true
Prepared statement closed? : true
Preparing statement ....
..... |
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 17, 2013 7:40 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
So what's the actual performance impact of recreating the prepared statement?
Suppose your flow receives one message every 2 minutes. You will therefore always incur the overhead of re-preparing the statement.
How bad is that?
Suppose your flow receives 10,000 messages over five minutes and then does nothing for two minutes, and then gets another 10,000 messages over 5 minutes... You'll prepare the statement once, and reuse it for the whole time of the first batch, lose it, reprepare it, and reuse it over the 2nd batch...
How bad is that?
What conditions are you trying to optimize for by retaining the prepared statement across the broker idle-connection boundary? |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jan 17, 2013 7:40 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Reusing the Prepared statement!!! |
So this is the most interesting bit - the Prepared Statement is being reused. Remember the broker only closes the Connection when it has not been used for 60 seconds - if you keep using it your statement stays alive. And if you are not using it caching it serves no purpose...
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Jan 17, 2013 8:13 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mgk wrote: |
| Quote: |
| Reusing the Prepared statement!!! |
So this is the most interesting bit - the Prepared Statement is being reused. Remember the broker only closes the Connection when it has not been used for 60 seconds - if you keep using it your statement stays alive. And if you are not using it caching it serves no purpose...
Kind regards, |
Another way to view this is you are trying to use Prepared Statements as Stored Procedures.
Stored Procedures give you the same performance benefits of Prepared Statements with the added benefit that they do not expire upon closure of the database connection.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| prasadpav |
| Posted: Thu Jan 17, 2013 8:26 am Post subject: |
|
|
Centurion
Joined: 03 Oct 2004
Posts: 142
|
| Quote: |
| So this is the most interesting bit - the Prepared Statement is being reused. Remember the broker only closes the Connection when it has not been used for 60 seconds - if you keep using it your statement stays alive. And if you are not using it caching it serves no purpose... |
Agreed. As long as the application is busy (which in majority of our cases is) I would be reusing the prepared statements. The above "stdout" is from my dev system when I'm trying to establish the ways to create prepared statements when connection pooling is in place.
The point which I wanted to make in my above posts is that "if the maxConnectionPoolSize" parameter in the JDBC configurable service is set to a value greater than 0 , then one cannot use prepared statements unless implementing a custom code (may be complicated too) to map broker managed connections to prepared statements. Also, another reason for this discussion is - In case of ODBC DB connections, broker manages connections and also stores the prepared SQL statements (behind the scenes) thus making it more performant. But when coming to JDBC configurable service, broker performs the connection management but not the SQL statement preparation. So I was curious if at all anyone else addressed this."
| Quote: |
@mqjeff - So what's the actual performance impact of recreating the prepared statement?
Suppose your flow receives one message every 2 minutes. You will therefore always incur the overhead of re-preparing the statement.
How bad is that? |
It depends on the SQL query and whether or not it is doing any joins. One cannot generalise how much overhead it'll add but I read that creating the SQL query plan is quite an intensive operation. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 17, 2013 8:43 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| prasadpav wrote: |
| It depends on the SQL query and whether or not it is doing any joins. One cannot generalise how much overhead it'll add but I read that creating the SQL query plan is quite an intensive operation. |
I agree.
One also can't generalize across specific scenarios. One can't say that the existing behavior is bad for all flows, because it may be just fine for a flow that never goes idle for more than one minute, and it may be just fine for a flow that only runs once a day.
It may also be terrible for both of those cases. It depends entirely on the complexity of the SQL statement, and the SLA of the flow.
Likewise the use of a PreparedStatement as an optimization choice depends entirely on these kinds of things. It may indeed be better to use a StoredProcedure, or it may be better to implement one's own connection pooling, or it may be better to do none of these things.
Otherwise, yes, I agree that disparity in features between ODBC and JDBC within Broker is less than ideal, and can in fact be identified as problematic in many cases. |
|
| Back to top |
|
|
|
|
