-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Help with complex hql query
PostPosted: Thu Dec 03, 2009 8:00 am 
Beginner
Beginner

Joined: Fri Nov 14, 2008 7:11 am
Posts: 31
Hi,

I have a quite complex and very long running query (> 15 minutes):

Code:
select new " + CustomerFeedback.class.getName() + " (
   cus,
   cs,
   c,
   (select
      count(cd2)
   from
      CampaignDelivery cd2
   where
      cd2.campaign.id = c.id
      and cd2.customer.id = cus.id))
   from
      CampaignDelivery cd
   join cd.campaign c
   join cd.customer cus
   left join c.customerSelections cs
   left join cs.customerSelectionAssignments csa
   where
      (
         cus.id = csa.customer.id
         or csa.customer.id = null
      )
      and
         c.id in (" + campaignsString + ")
      and (
         cd.feedback <> :feedbackUndefined
         or (c.frequencyCapping > 0
         and c.frequencyCapping <= (
            select
               count(cd3)
            from
               CampaignDelivery cd3
            where
               cd3.campaign.id = c.id and cd3.customer.id = cus.id
            )
         )
      )
      and cd.mandator.id = :mandatorId
   group by
      cus.id,
      cs.id,
      c.id
   )


The resulting SQL is:

Code:
select
        customer2_.id as col_0_0_,
        customerse4_.id as col_1_0_,
        campaign1_.id as col_2_0_,
        (select
            count(campaignde6_.id)
        from
            CAMPAIGNDELIVERY campaignde6_
        where
            campaignde6_.campaign_id=campaign1_.id
            and campaignde6_.customer_id=customer2_.id) as col_3_0_
    from
        CAMPAIGNDELIVERY campaignde0_
    inner join
        CAMPAIGN campaign1_
            on campaignde0_.campaign_id=campaign1_.id
    left outer join
        CAMPAIGN_CUSTOMERSELECTION customerse3_
            on campaign1_.id=customerse3_.campaign_id
    left outer join
        CUSTOMERSELECTION customerse4_
            on customerse3_.customerSelection_id=customerse4_.id
    left outer join
        CUSTOMERSELECTIONASSIGNMENT customerse5_
            on customerse4_.id=customerse5_.customerSelection_id
    inner join
        CUSTOMER customer2_
            on campaignde0_.customer_id=customer2_.id
    where
        (
            customer2_.id=customerse5_.customer_id
            or customerse5_.customer_id is null
        )
        and (
            campaign1_.id in (
                775
            )
        )
        and (
            campaignde0_.feedback<>0
            or campaign1_.frequencyCapping>0
            and campaign1_.frequencyCapping<=(
                select
                    count(campaignde7_.id)
                from
                    CAMPAIGNDELIVERY campaignde7_
                where
                    campaignde7_.campaign_id=campaign1_.id
                    and campaignde7_.customer_id=customer2_.id
            )
        )
        and campaignde0_.mandator_id=368
    group by
        customerse4_.id ,
        customer2_.id ,
        campaign1_.id


All tables contain just a few records (<50) except the table CAMPAIGNDELIVERY which has about 100.000 records.

I have asked in a dbms forum first and got a better sql statement from them:

Code:
SELECT col_0_0_,
    col_1_0_,
    col_2_0_,
    campaign_count
FROM (
select
        customer2_.id as col_0_0_,
        customerse4_.id as col_1_0_,
        campaign1_.id as col_2_0_,
        count(campaign1_.id) over (partition by campaign1_.id, customer2_.id) as campaign_count,
        campaign1_.frequencyCapping
    from
        CAMPAIGNDELIVERY campaignde0_
    inner join
        CAMPAIGN campaign1_
            on campaignde0_.campaign_id=campaign1_.id
    left outer join
        CAMPAIGN_CUSTOMERSELECTION customerse3_
            on campaign1_.id=customerse3_.campaign_id
    left outer join
        CUSTOMERSELECTION customerse4_
            on customerse3_.customerSelection_id=customerse4_.id
    left outer join
        CUSTOMERSELECTIONASSIGNMENT customerse5_
            on customerse4_.id=customerse5_.customerSelection_id
    inner join
        CUSTOMER customer2_
            on campaignde0_.customer_id=customer2_.id
    where
        (
            customer2_.id=customerse5_.customer_id
            or customerse5_.customer_id is null
        )
        and (campaign1_.id in (775))
        and (campaignde0_.feedback<>0 or campaign1_.frequencyCapping > 0)
       campaignde0_.mandator_id=368
) t
where frequencyCapping <= campaign_count


My problem is that I have no idea how to realize this in hibernate. At the moment we are using just simple HQL (no criteria API i.e.). Maybe we don't have to use that 'count() over...' at all but can use another HQL function.

Any hints would be great! Thanks in advance!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.