| EclipseLink on SQLServer 2005 behaviour, every statement is recompiled leading to performance issues [message #645074] |
Wed, 15 December 2010 09:07  |
Eclipse User |
|
|
|
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
|
|
|
|
Powered by
FUDForum. Page generated in 0.17280 seconds
Home