Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues(EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues)
EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues [message #645074] Wed, 15 December 2010 09:07 Go to next message
Manpreet Singh is currently offline Manpreet Singh
Messages: 5
Registered: December 2010
Junior Member
Eclipse link build used: 2.1.1.v20100817-r8050
SQL Server 2005
Microsoft JDBC driver 3.0

When we are updating a field in the database on the same record again and again in a loop, prepared statements are not used. In the SQL Server profiler we see on every persist/commit of the same object a new statement is compiled and executed and then unprepared. The expected behaviour is to reuse the same statement that was created in the beginning for an object.

Test case is as below:
We have a table TABLE1 in the SQL Server database and create an entity called MyEntity from it. On this entity I retrieve one record object using a named query. Now on this object i am just running updates in a loop. The sample program is shown as below:


public class Main {

public static void main(String[] args) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("TestForEclipseLinkPU ");
EntityManager em = emf.createEntityManager();

Query q = em.createNamedQuery("MyEntity.findByKey");

q.setParameter("key", "KEY123");

MyEntity entity = (MyEntity) q.getSingleResult();

short loopcount = 0;

while (loopcount++ != 1000) {
em.getTransaction().begin();

entity.setFiled1((short) loopcount);

em.persist( entity);

em.getTransaction().commit();
}
em.close();

}


When i check the SQL server Profiler activity, for every commit I see a new
sp_prepexec and sp_unprepare
even though it is exactly the same update on the same Entity object.

Due to this behaviour the performance of the application is degraded as it takes unnecessary time and cpu cycles on the SQL Server side to prepare, execute and unprepare statements for each and every update. Ideally it should just prepare the update statement once like a named query and reuse it, till the time it is disposed. When the same thing is done using pure JDBC and PreparedStatements, the behaviour is as expected and the performance improves manifold.

I would appreciate if anyone can throw some light on this.
Is this the way Eclipse link is supposed to work ?
Am i doing something wrong here?
Is there some configuration that is required to make this work?

P.S. I have already tried statement-cache properties. and other cache options


Re: EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance is [message #645107 is a reply to message #645074] Wed, 15 December 2010 10:39 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please include your persistence.xml.

To enable statement caching the properties,

"eclipselink.jdbc.bind-parameters"="true"
and,
"eclipselink.jdbc.cache-statements"="true"

need to be used. The bind-parameters is true by default, but cache-statements is false by default, so must be enabled.

This only applies when using EclipseLink connection pooling, if you are using a DataSource, then you must enable statement caching in your DataSource config.


James : Wiki : Book : Blog : Twitter
Previous Topic:Querying a table with an array column fails
Next Topic:Eclipse product not saving preferences
Goto Forum:
  


Current Time: Sat Apr 19 08:59:19 EDT 2014

Powered by FUDForum. Page generated in 0.01571 seconds