Eclipse Community Forums: EclipseLink » EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues

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)

Show: Today's Messages :: Show Polls :: Message Navigator

EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues [message #645074]

Wed, 15 December 2010 14:07

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

Report message to a moderator

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 15:39

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

Report message to a moderator

Previous Topic:	Querying a table with an array column fails
Next Topic:	Eclipse product not saving preferences

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:41:33 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter