Hi Folks,
I have this latency issue when trying to retrieve records from the database. I'm running EJB3 with Hibernate Persistence on a Tomcat 6.1 server. Database is SQL Server 2005, front-end is JSF.
The problem is it takes forever for a query to return entities using this set up. If I run the query itself on the database directly, it takes less than a second. So i know the query is not taking long.
Here's a little sample code. When calling qsummary.getResultList(), this goes out to the DB and this is where it takes a long time to execute. 100 rows can take up to almost a minute
public List<Data> getData( DataQuery p_query, Integer cm ) {
createEntityManager( );
StringBuffer query = new StringBuffer("SELECT TOP 10 * FROM tableData");
applyWhereClause(p_query, query);
if (cm != null )
query.append("AND ClearingMember = :cm ");
RowCounter.getInstance( ).resetCounter( );
Query qsummary = em.createNativeQuery(query.toString(), Data.class);
applyParameters( p_query, qsummary );
if (cm != null )
qsummary.setParameter( "cm", cm );
/** QUERY STRING **/
System.out.println(query.toString());
List<Data> results = ( List<Data> )qsummary.getResultList();
closeEntityManager();
return results;
}
Anyone have an idea what can potentially be the problem?