Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1.0


I'm using annotations and a NamedNativeQuery. The query looks like this:

select guid,date from post where guid in(
select ass.post_guid from association_post_tag ass inner join Post p on p.guid = ass.post_guid
where ass.tag_guid in ('3DA00CA6-4C48-4AEF-B2C9-F13B05DBE0A7','2B994A54-59BF-4043-8F19-19A6AA599566')
AND p.parent_guid is not null
group by ass.post_guid
having count(ass.post_guid) = 2) order by date desc

If i say setMaxResults(5) on my query object, it appends select top 5 to my query. Problem is, when i perform this query with top, my data base (SQL Server Express 2005) response time goes from a fraction of a second to over 2 minutes with this query. I'm no DB expert and i cant figure out why that happens but as a work around i thought that i'd be able to use Query.iterate and just read the first 5 records. But when i tried i found out that hibernate doesnt support iterate on native queries. So as a dirty hack to get my code running faster now i just return the whole result list and read off the first few and pitch the rest. But i'd like to fix this problem before calling this thing production ready.

Is there a way to tell hibernate not to use top when setting maxresults?

Is it better to port this query to HQL and use iterate? (I usually use HQL but couldnt figure out how to do it with group by and having)

Is there something obviously wrong with my sql that is causing this problem?

/NOTE: What i am trying to do is return posts that that are tagged with both of the tag's referenced by my guids. This list is dynamic. A friend suggested an alternative way of doing this using joins but that way would require dynamic sql, and in query analyzer the top problem still happens.

I ported this the query to hql and i'll just use the iterator. I guess top and setMaxResults are dangerous :-/

Here's the hql in case anyone cares

SELECT post.postId as postId FROM Post post WHERE post.postId IN (SELECT ass.post.postId FROM AssociationPostTag ass
INNER JOIN ass.post
WHERE ass.post.parent.postId IS NOT NULL AND ass.tag.tagId IN :tagIds)
GROUP BY ass.post.postId
HAVING count(ass.post.postId) = :count)) ORDER BY post.date DESC"