Problem with slow hql query in grails app

I have this hql query but it takes so long (10 minutes) to be executed since its basically sweep the whole table. I was wondering if there is other way to write this to run faster.

What I am doing in this query is to list all the position for a particular date and if for that date there is no position I want to pick the most rece开发者_开发百科nt one.

    def flList = FlPosition.findAll(" from FlPosition f \ 
      where f.asOfDate = ( \   
         select max(f2.asOfDate) \ 
         from FlPosition f2  \
         where f2.cusip = f.cusip and \
               f2.shareholderAccount = f.shareholderAccount and  \
               f2.asOfDate <= ?)", [date] \
        ) \
   "