Do PreparedStatements with same SQL compile to multiple classes?

[Adam McMahon <ad...@cs.miami.edu>]

Hi,

I have some questions regarding Derby's compilation of PreparedStatements to
Java bytecode, and how/when that byte/code is loaded/unloaded or released
from memory.

We use java verion 1.6.0_31 and derby 10.10.1.1

We use a custom connection connection pool that does something similar to
the following. I have labeled the lines of pseudo-code so I refer to them
with specific questions.

1. // pool is requested for a connection, if non exists, create a new one.
2. con1 = DriverManager.getConnection()
3. ps1 = con1.prepareStatement(“select * from cities where id=?”)
4. ps1.setInt(1, n)
5. ps1.executeQuery()
6. //continue to use ps1 with different values...
7. // after con1 is idle for a period of time it is closed by the pool.
8. ps1.close and con1.close

9. // new request comes in, and a new connection is created
10. con2 = DriverManager.getConnection()
11. ps2 = con1.prepareStatement(“select * from cities where id=?”)  // same
SQL as above
12. ps2.setInt(1, n)
13. ps2.executeQuery()
14. //continue to use ps2 with different values...
15. // after con2 is idle for a period of time it is closed by the pool.
16. ps2.close and con2.close()

Here my questions with the above scenario:

a) at step [2] and [10], two preparedstatments are created with the same
SQL, but by different connections. When these are compiled, are two separte
java classes created and loaded? Or is only one newly created/compiled class
created and loaded, and then reused since they have the same sql? (even
though they originate from different connections?)

b) Since Prepared Statements are compiled into byte code and loaded, are
they ever unloaded? If the above sequence continues, will the PermGen (or
another memory space) eventually fill up?



--
View this message in context: http://apache-database.10148.n7.nabble.com/Do-PreparedStatements-with-same-SQL-compile-to-multiple-classes-tp140440.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: Do PreparedStatements with same SQL compile to multiple classes?

[Adam McMahon <ad...@cs.miami.edu>]

Knut,

Thanks for your help!!  Thanks for pointing me in the right direction.

-Adam



--
View this message in context: http://apache-database.10148.n7.nabble.com/Do-PreparedStatements-with-same-SQL-compile-to-multiple-classes-tp140440p140475.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.

Re: Do PreparedStatements with same SQL compile to multiple classes?

[Knut Anders Hatlen <kn...@oracle.com>]

Adam McMahon <ad...@cs.miami.edu> writes:

> Hi,
>
> I have some questions regarding Derby's compilation of PreparedStatements to
> Java bytecode, and how/when that byte/code is loaded/unloaded or released
> from memory.
>
> We use java verion 1.6.0_31 and derby 10.10.1.1
>
> We use a custom connection connection pool that does something similar to
> the following. I have labeled the lines of pseudo-code so I refer to them
> with specific questions.
>
> 1. // pool is requested for a connection, if non exists, create a new one.
> 2. con1 = DriverManager.getConnection()
> 3. ps1 = con1.prepareStatement(“select * from cities where id=?”)
> 4. ps1.setInt(1, n)
> 5. ps1.executeQuery()
> 6. //continue to use ps1 with different values...
> 7. // after con1 is idle for a period of time it is closed by the pool.
> 8. ps1.close and con1.close
>
> 9. // new request comes in, and a new connection is created
> 10. con2 = DriverManager.getConnection()
> 11. ps2 = con1.prepareStatement(“select * from cities where id=?”)  // same
> SQL as above
> 12. ps2.setInt(1, n)
> 13. ps2.executeQuery()
> 14. //continue to use ps2 with different values...
> 15. // after con2 is idle for a period of time it is closed by the pool.
> 16. ps2.close and con2.close()
>
> Here my questions with the above scenario:
>
> a) at step [2] and [10], two preparedstatments are created with the same
> SQL, but by different connections. When these are compiled, are two separte
> java classes created and loaded? Or is only one newly created/compiled class
> created and loaded, and then reused since they have the same sql? (even
> though they originate from different connections?)

Hi Adam,

Derby has a statement cache that holds 100 statements by default, and it
will attempt to use one of those instead of compiling a new one if it
can. More details about the statement cache can be found in the tuning
guide:

http://db.apache.org/derby/docs/10.10/tuning/ctundepth32379.html

> b) Since Prepared Statements are compiled into byte code and loaded, are
> they ever unloaded? If the above sequence continues, will the PermGen (or
> another memory space) eventually fill up?

The generated classes can be unloaded by the garbage collector once
there are no references to their corresponding statements. Since a class
cannot be garbage collected until its class loader is eligible for
garbage collection, Derby creates a separate class loader for each
generated class so that the generated classes can be garbage collected
individually.

Because of the statement cache, though, a generated class may not become
eligible for garbage collection immediately after the application stops
referencing the statement. The statement will also have to be evicted
from the statement cache before the class can be garbage collected and
unloaded. In a typical application you'd expect the number of generated
classes to grow until the statement cache is full, and then stabilize.


Hope this helps,

-- 
Knut Anders