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!