Hi there,
I do recognized a huge performance bottleneck when using HQL or Hibernate SQL: Retrieving 100000 cats takes 18 secs. But: The answering time when using a JDBC select loop (see code below) is just 70 Millisecs ?!
(MS SQL Server 2000) .

Any comment is appreciated, because I deal with such an amount of document objects. Of course, the select * stmt is somehow unrealistic, but in any case indeed raises the performance/answer time question. I was already trying to replaced the HQL object queries by Hibernate SQL queries I hoped to be basically 'normal' JDBC statement having a better performance, but the result time is as also poor.

Thanks for feedback,

Dirk V. Schesmer

/*
[Atinav][JDBC SQL Server Driver:Ver C3.0C] THIS IS A 30 DAY TRIAL VERSION-aveConnect30C build 0904
Start JDBC SQL:
End JDBC SQL, duration: 70 msecs, count=100000
Start SQL in Hibernate:
End SQL in Hibernate, duration: 17975 msecs, count=100000
start Hibernate QL:
End Hibernate QL, duration: 18245 msecs, count=100000
(or sometimes : java.lang.OutOfMemoryError)
*/

public void listHIA() {

int count = 0;
long a = 0, b = 0;

Iterator it;
String url = "jdbc:AvenirDriver://127.0.0.1:1433/Northwind";
try {
Statement stmt = null;
Class.forName("net.avenir.jdbc3.Driver");

Connection ctn = null;
ctn = DriverManager.getConnection(url, "sa", "");
stmt = ctn.createStatement();
boolean moreResult = stmt.execute("select name from dbo.Cat");
int updateCount = stmt.getUpdateCount();
ResultSet rst = null;
System.out.println("Start JDBC SQL:");
a = System.currentTimeMillis();

rst = stmt.getResultSet();
String n;
while (rst.next()) {
count++;
n = (String) rst.getObject(1);
}
} catch (SQLException ex3) {
} catch (ClassNotFoundException ex2) {
}

b = System.currentTimeMillis();
System.out.println("End JDBC SQL, duration: " + (b - a) +
" msecs, count=" + count);

HibernateUtil.beginTransaction();
Session s = HibernateUtil.getSession();

catDAO = new CatDAO();

Query q = null;
count = 0;

System.out.println("Start SQL in Hibernate:");
String sql = "select {cat.*} from cat {cat}";
Query sqlQuery = s.createSQLQuery(sql, "cat", Cat.class);
List cats = null;
Cat cat;
try {
a = System.currentTimeMillis();
cats = sqlQuery.list();
for (it = cats.iterator(); it.hasNext(); ) {
count++;
cat = (Cat) it.next();
}

b = System.currentTimeMillis();
System.out.println("End SQL in Hibernate, duration: " + (b - a) +
" msecs, count=" + count);

} catch (HibernateException ex1) {
System.out.println("Exc.: " + ex1);
}

System.out.println("start Hibernate QL:");
a = System.currentTimeMillis();
count = 0;
try {
q = s.createQuery("from Cat cat");
q.setMaxResults(100000);
cats = q.list();

System.out.println("Start: " + a + " msecs");

for (it = cats.iterator(); it.hasNext(); ) {
count++;
cat = (Cat) it.next();
}
b = System.currentTimeMillis();
System.out.println("End Hibernate QL, duration: " + (b - a) +
" msecs, count=" + count);

} catch (HibernateException ex) {
System.out.println("Exc.: " + ex);
}
HibernateUtil.commitTransaction();
HibernateUtil.closeSession();
}

Well, you selected just cat name from the table with plain jdbc,
and whole objects using hibernate - this way you have retrieved much more data - so there is difference.

IMHO, if you select your cats ( and not their fiekds ) and enable
proxying of them result will look different.

Look at using Query.scroll(), ScrollableResults. Or you could increase the JVM heap size. The slowdown you are seeing will be because the JVM runs out of memory very quickly creating the objects it needs to load the cat objects ( JDBC driver and hibernate will both eat through a lot of memory ) so it has to garbage collect ALOT. Run with -verbose:gc to see what your garbage collector is doing.