Hi,

I'm running this SQL query using SqlCommand over a 4 milion records table:

SELECT SUM(nc), sid FROM Readings WHERE (sid IN (1, 2, 3) AND (date >= @from) AND (date <= @to) GROUP BY sid

and it takes 5 seconds to complete.

Running this HQL query agains the same table:

select sum(rec.nc), rec.sid from Reading rec where (rec.sid in (1, 2, 3) and (rec.Date >= :from) and (rec.Date <= :to) group by rec.sid

completes in over 20 seconds...

What am I missing?

Thanks


Hibernate version:
1.2.0
Name and version of the database you are using:
SQL Server 2005

Sorry,

select rec.sid, sum(rec.nc) from Reading rec where (rec.sid in (1, 2, 3) and (rec.Date >= :from) and (rec.Date <= :to) group by rec.sid

returned in over 20 seconds BUT

select sum(rec.nc), rec.sid from Reading rec where (rec.sid in (1, 2, 3) and (rec.Date >= :from) and (rec.Date <= :to) group by rec.sid

returned in 5.

whats the difference between the 2 besides the order of the columns?

rec.sid, sum(rec.nc) vs sum(rec.nc), rec.sid

I'm lost, after restarting SQL Server 2005 both HQL queries are performing bad... over 20 sec... after running for a while is back to 5 sec

It looks like caching issue (e.g. Query Plan Cache) in SQL Server.