time comparison: executing query with JDBC and hibernate

gs_1481 · **Posted:** Wed Dec 08, 2004 2:45 am

Hi

I have 6000 records in one table T1 and 2000 records in table T2... OK

So I have a few scenarios now:

1.a When i am retrieving 100 records from the table T1 using only JDBC, the query execution takes

Quote:

0ms

.

1.b When i am retrieving 100 records from the table T1 using only Hiberanete, the query execution takes

Quote:

16ms

.
----------------------------------------------------------------------
2.a When i am running a join on T1 and T2 from the T1 using only JDBC, the query execution takes

Quote:

46391ms

.
2.b When i am running a join on T1 and T2 from the T1 using only Hibernate, the query execution takes

Quote:

16ms

.
----------------------------------------------------------------------
3.a When i am inserting 100 records, using a for loop, in table T1 using only JDBC, the query execution takes

Quote:

78ms

.
4.a When i am inserting 100 records, using a for loop, in table T1 using only Hibernate, the query execution takes

Quote:

797ms

.
-----------------------------------------------------------------------

I would like to know why Hibernate shows such irregular responses??????

Thanks............

mchyzer · **Joined:** Sat Jul 17, 2004 5:16 pm **Posts:** 143

gs_1481 wrote:

1.a When i am retrieving 100 records from the table T1 using only JDBC, the query execution takes

Quote:

0ms

.

1.b When i am retrieving 100 records from the table T1 using only Hiberanete, the query execution takes

Quote:

16ms

.

OK

Quote:

2.a When i am running a join on T1 and T2 from the T1 using only JDBC, the query execution takes

Quote:

46391ms

.
2.b When i am running a join on T1 and T2 from the T1 using only Hibernate, the query execution takes

Quote:

16ms

.

So hibernate beats jdbc by 46 seconds? Go Hibernate!

Quote:

3.a When i am inserting 100 records, using a for loop, in table T1 using only JDBC, the query execution takes

Quote:

78ms

.
4.a When i am inserting 100 records, using a for loop, in table T1 using only Hibernate, the query execution takes

Quote:

797ms

.

Did you check the SQL generated? Are you closing sessions and committing after each row? Lets see some code.

Chris

gs_1481 · **Posted:** Wed Dec 08, 2004 3:59 am

Quote:

3.a When i am inserting 100 records, using a for loop, in table T1 using only JDBC, the query execution takes

Quote:

78ms

.
4.a When i am inserting 100 records, using a for loop, in table T1 using only Hibernate, the query execution takes

Quote:

797ms

.

Did you check the SQL generated? Are you closing sessions and committing after each row? Lets see some code.

Chris[/quote]

I need to put the log in a file which i need to see... mean while i am sending u the code........

for(int i=1;i<=100;i++)
{
// Instantiate and populate object to be persisted

Employee emp = new Employee();
emp.setName("EmployeeETC" + (i + random.nextInt(10000)));
emp.setDob(new Date());
Transaction t = sess.beginTransaction();
sess.save(emp);
t.commit();
}

can u tell me few things????
1. which part is executing the SQL?
2. Why it takes longer?

mchyzer · **Joined:** Sat Jul 17, 2004 5:16 pm **Posts:** 143

OK, now lets see the JDBC that you used to compare.

When you commit the transaction, it sends SQL.

Hibernate will take a little longer, but I wouldnt think it would be this long. I suspect we will see why when you post your JDBC code.

Chris

gs_1481 · **Posted:** Wed Dec 08, 2004 5:56 am

mchyzer wrote:

OK, now lets see the JDBC that you used to compare.

When you commit the transaction, it sends SQL.

Hibernate will take a little longer, but I wouldnt think it would be this long. I suspect we will see why when you post your JDBC code.

Chris

i am using conn.createStatement() object ............ and J Connector 3.0.10 driver to connect MYSQL.........

for(int i=6001;i<=6100;i++)
{
String sql;
sql = "Insert into tbl_emp_profile ";
sql = sql + " VALUES (" + i + rn.nextInt(10000) + ",";
sql = sql + "'EmployeeETC" + i + rn.nextInt(10000) + "',";
sql = sql + "'2004-12-08'" + ")";
// inserting records
statement.executeUpdate(sql);
}

Can u now tell.........

christian · **Posted:** Wed Dec 08, 2004 6:27 am

Guys, while it is certainly fun watching you test apples and oranges, please make sure you read this before:

http://www.hibernate.org/15.html
http://www.hibernate.org/157.html

gs_1481 · **Posted:** Wed Dec 08, 2004 8:24 am

christian wrote:

Guys, while it is certainly fun watching you test apples and oranges, please make sure you read this before:

http://www.hibernate.org/15.html
http://www.hibernate.org/157.html

Hi christian,

I am still not getting .... How should i relate my topic with the theoritical part.... I am new use to it..... Plz help.

Thanks

christian · **Posted:** Wed Dec 08, 2004 10:10 am

Well, I'd recommend spending one or two years with database fundamentals. Some years experience in system and network administration of course also help. Finally, you should be an experienced (~5 years) software developer. Then you could start thinking about writing usable database application benchmarks.

mchyzer · **Joined:** Sat Jul 17, 2004 5:16 pm **Posts:** 143

Try putting the transaction code outside of the loop (like below), also look at the generated sql and make sure you are only generating one sql statement per object save... Chris

Code:

Transaction t = sess.beginTransaction(); 
for(int i=1;i<=100;i++) 
{ 
// Instantiate and populate object to be persisted 

Employee emp = new Employee(); 
emp.setName("EmployeeETC" + (i + random.nextInt(10000))); 
emp.setDob(new Date()); 
sess.save(emp); 
} 
t.commit(); 

anthony · **Posted:** Thu Dec 09, 2004 3:29 am

you really have to understand what is a session and a transaction
http://blog.hibernate.org/cgi-bin/blosx ... find&path=

mchyzer · **Joined:** Sat Jul 17, 2004 5:16 pm **Posts:** 143

anthony wrote:

you really have to understand what is a session and a transaction
http://blog.hibernate.org/cgi-bin/blosx ... find&path=

Thanks for that link, that is very interesting. Sometimes I have a handful of transactions and I do a new session for each. Gavin says this (session-per-operation) is an antipattern. Understood. Is this because of performance? Besides caching, if I compare session-per-operation and session-per-request, it the session-per-operation loses by 6ms per loop iteration. If this is the only reason, and I am ok with 6ms, then the session-per-operation is ok right? Just want to make sure Im not missing something. Obviously this loop is not a real-world example. Normally I have a few per request, a select or two, and an update or two.
If you see how the session-per-operation cleans up the code (and prevents DB pool mismanagement), you will see why it is attractive

Code:

    public void testAudit() {
        long now = System.currentTimeMillis();
        for (int i=0;i<10;i++) {
            AuditLog auditLog = new AuditLog();
            auditLog.setCategory("test");
            auditLog.setAction("test");
            auditLog.setDescription("test");
            
            HibernateHelper.saveOrUpdate(auditLog);
        }
        System.out.println(System.currentTimeMillis()-now);

    }

The above executes in 2068ms.

Code:

    public void testAuditLoop() {
        long now = System.currentTimeMillis();
        HibernateHelper hibernateHelper = null;
        Transaction tx = null;
        try {
            hibernateHelper = new HibernateHelper();
            Session session = hibernateHelper.getSession();
            tx = session.beginTransaction();
            for (int i=0;i<10;i++) {
                AuditLog auditLog = new AuditLog();
                auditLog.setCategory("test");
                auditLog.setAction("test");
                auditLog.setDescription("test");
                session.saveOrUpdate(auditLog);
            }
            tx.commit();
        } catch (HibernateException he) {
            he.printStackTrace();
            fail();
        } finally {
            HibernateHelper.closeTransaction(tx);
            HibernateHelper.closeSafe(hibernateHelper);
        }
            
        System.out.println(System.currentTimeMillis()-now);
        
    }

The above executes in 2011ms.

Thanks,
Chris