aspose file tools*
The moose likes JDBC and the fly likes Queries regarding PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Queries regarding PreparedStatement" Watch "Queries regarding PreparedStatement" New topic
Author

Queries regarding PreparedStatement

Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
I've queries regarding PreparedStatement.

According to the sun's description

---------------------------------------------------------------------------
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement's SQL statement without having to compile it first
---------------------------------------------------------------------------

My queries are
1. Where it keeps these PreparedStatement?
2. How long does it keep the PreparedStatement in precompiled state?
3. Can we set the time for keeping it in the precompiled state?

Thanks
Navin
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
PreparedStatement(PS) is the reference (interface) in Java to the precompiled SQL in the database. There are so called database drivers which does the actual work of pointing to the precompiled SQL. Once you close PS (or the connection from which PS was obtained) the reference to the precompiled SQL and hence the performance advantage is lost. To realize the performance advantage you may have to cache the PS in your application.


http://www.skillassert.com


Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hello Karthi,

Thank you for your reply. I've one more query. If i have a pool of connection and after every 5 minuts users are geeting a connection from pool, firing a same query with different parameters and returning it to the pool. So how can i get the advantage of precompiled statements or cache in such situations

Thanks
Navin
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425

Once you close PS (or the connection from which PS was obtained) the reference to the precompiled SQL and hence the performance advantage is lost


is not completely true (atleast in Oracle it is not and I believe in other DB's to be same).

PreparedStatement does nothing but instruct the DB to create SQL statement with bind variable and then provides the bind values. When a SQL is sent to the DB, first the DB tries to find if this statement as been executed in the recent past by taking the hash value of the incomming SQL text and comparing with previously parsed+plan generated cached SQL statements in the memory. If it find's a match it will use the previously generated plan instead of generating the plan again.

When the session closes, the parsed statements of that particular session are NOT discard as there is more possibility that some other session can issue the same SQL. So partically DB Server retains the plan of previously executed statement in the shared memory.

If you are using Oracle and have DBA privilege to dynamic views you can verify this. You can query the V$SQLTEXT and V$SQL to verify this.
Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hello Purushothaman

Thank you for your reply. I got more clear vision due to your answer. But still i've a query.

1. Can we change the time for keeping the compiled statements from DB?

Thanks
Navin
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
is not completely true (atleast in Oracle it is not and I believe in other DB's to be same).


I agree and disagree with the above.

The JavaDoc for the Connection.close() method says "Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released."

So close the connection, all the acquired objects, such as Statements and ResultSets willbe closed.

However, if you use connection pool, the close() method returns the connection to the pool and doesn't actually close the connection. In this case dependant objects may be left open.

I tested the following logic using MySQL

which gave me the following error

java.sql.SQLException: Connection.close() has already been called. Invalid operation in this state.

Please correct me if i am wrong.
[ September 21, 2006: Message edited by: karthi keyan ]
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425

1. Can we change the time for keeping the compiled statements from DB?


In oracle you don't have an option to do that. The statement will be cached in the memory as long as possible (unless Oracle couldn't find space to hold new statement and it uses LRU to maintain the statement in memory).


Karthik



No wonder you got the exception as you closed the connection. What you need to do is


and verify MySQL to see if it has used the previous plan. I am not MySQL guy so I don't know if there is way to check the SQL execution. It doesn't matter whether you are using connection pool or not in Oracle and I tested with standalone program.
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
Purushothaman I know why the Exception happens. We both are on the same page(I am not sounding rude ).

The point which i disagree (that is i am not sure) is when you say the performance advantage is not lost when the PreparedStatement is closed.

Can you provide some supporting document. (I remember reading that the performance advantage is lost in Wiley's More Java Pitfalls. May be I have a bad memory)
[ September 21, 2006: Message edited by: karthi keyan ]
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Okay, I didn't check the java documentation on whatever you have posted. Java spec could say anything as it doesn't mandate what should happen on DB side when the connection is closed. If you want to verify your self and if you have access to Oracle with DBA privilege you can try these things

1. Write a java program with preparedstatement (open connection, bind values, fire query and close the connection)
2. Run the program twice
3. Go to Oracle
i) Query V$SQLTEXT by filtering on the table name. You should get the hash value of the query.
ii) With that hash value query the V$SQL view and select the columns like
executions, loads, open_versions.
iii) You will see that the statement executed through JDBC the execution count as 2.

If you aren't satisfied forget this.
karthikeyan Chockalingam
Ranch Hand

Joined: Sep 06, 2003
Posts: 259
But I was mentioning the performance gain in reusing the already compiled PreparedStatement. (Its not about the database increasing the counter for same query structure ) The below books support this.

Chapter 12 Working with the PreparedStatement Apress publications - Apress JDBC Recipes A Problem Solution Approach - Sep 2005

Item 44: The Unprepared PreparedStatement Wiley Publications - More Java Pitfalls.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Queries regarding PreparedStatement