Hibernate version:
2.1
In testing query performance we're trying to improve the count() query performance on one case with a large data set. All the SQL here is what's spewed out from Hibernate.
This query took about 25 seconds:
HQL:
Code:
select count ( * ) from ChannelViewActivity cva where cva.createdAtDate between :startDate and :endDate
SQL:
Code:
select count(*) as x0_0_ from MC.CHANNEL_VIEW_ACTIVITY channelvie0_, MC.ACTIVITY channelvie0__1_ where channelvie0_.ACTIVITY_ID=channelvie0__1_.ACTIVITY_ID and ((channelvie0__1_.CREATED_AT between ? and ? ))
In testing with SQLSquirrel (cut/past the SQL above), we found switching from count(*) to count(channelvie0_.ACTIVITY_ID) cut the query time from 25 seconds to less than 1 second. (I don't really understand the underlying database reason for the speed increase, so maybe there's a reason I shouldn't count() this way?).
So, we changed the HQL query to use:
Code:
select count ( cva.key ) from ChannelViewActivity cva where cva.createdAtDate between :startDate and :endDate
The SQL generated was identical to the above SQL, except the count(*) was now the desired count(channelvie0_.ACTIVITY_ID). I expected the query to process in ~1 second, just like in SQLSQuirrel. It didn't though. The execution through Hibernate still takes about 25 seconds. No performance increase.
I'm completely baffled. In every case in the past, SQLSquirrel's performance on an SQL statement generated by Hibernate was basically the same as in Hibernate. No surprise.
Here are some log lines from the query. As near as I can tell, it spends 25 seconds waiting for the database to respond with a results set, but SQLSQuirrel, executing the same SQL, only takes 1 second.
Why the performance difference? Is there something I did wrong specifying my HQL query that would account for it?
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG QueryTranslator HQL: select count ( cva.key ) from com.ncube.nable.platform.activity.type.ChannelViewActivity cva where cva.createdAtDate between :cvastart_createdAtDate and :cvaend_createdAtDate
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG QueryTranslator SQL: select count(channelvie0_.ACTIVITY_ID) as x0_0_ from MC.CHANNEL_VIEW_ACTIVITY channelvie0_, MC.ACTIVITY channelvie0__1_ where channelvie0_.ACTIVITY_ID=channelvie0__1_.ACTIVITY_ID and ((channelvie0__1_.CREATED_AT between ? and ? ))
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG BatcherImpl about to open: 0 open PreparedStatements, 0 open ResultSets
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG SQL select count(channelvie0_.ACTIVITY_ID) as x0_0_ from MC.CHANNEL_VIEW_ACTIVITY channelvie0_, MC.ACTIVITY channelvie0__1_ where channelvie0_.ACTIVITY_ID=channelvie0__1_.ACTIVITY_ID and ((channelvie0__1_.CREATED_AT between ? and ? ))
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG BatcherImpl preparing statement
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG TimestampType binding '2006-08-27 16:13:18' to parameter: 1
2006-08-29 16:13:25,955 [SocketListener0-8] DEBUG TimestampType binding '2006-08-29 16:13:18' to parameter: 2
2006-08-29 16:13:53,705 [SocketListener0-8] DEBUG Loader processing result set
2006-08-29 16:13:53,705 [SocketListener0-8] DEBUG Loader result row:
2006-08-29 16:13:53,705 [SocketListener0-8] DEBUG IntegerType returning '505' as column: x0_0_
Login
FAQ
Search
