I'm trying to move optimized (simple) query from SQL to HQL. I've three tables:
-
cam_Campaigns with identity "ID" column
-
rel_SubscriberToCampaign with "SubscriberID" and "CampaignID" columns
-
cam_Subscribers with identity "ID" column
I've a simple
original SQL query:
Code:
select count(*) from cam_Subscribers where ID in
(select SubscriberID from rel_SubscriberToCampaign where CampaignID=1)
This query returns all subscribers for given campaign ID. Simple and quick.
I tried to do the same with Nhibernate.
cam_Subscribers mapping:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="DAO.Subscriber, DAO" table="cam_Subscribers" lazy="false">
<id name="ID" type="Int32" column="ID">
<generator class="identity" />
</id>
<bag name="Campaigns" table="rel_SubscriberToCampaign">
<key column="SubscriberID"></key>
<many-to-many class="nl.DAO.Campaign, nl.DAO" column="CampaignID"/>
</bag>
</class>
</hibernate-mapping>
cam_Campaigns mapping:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="DAO.Campaign, DAO" table="cam_Campaigns" lazy="false">
<id name="ID" type="Int32" column="ID">
<generator class="identity" />
</id>
<bag name="Subscribers" table="rel_SubscriberToCampaign">
<key column="CampaignID"></key>
<many-to-many class="nl.DAO.Subscriber, nl.DAO" column="SubscriberID"/>
</bag>
</class>
</hibernate-mapping>
And
HQL query:
Code:
select count(*) from Subscriber ss,Campaign ca where
exists (from Campaign ca where ca in elements(ss.Campaigns) and ca.ID=1)
This generates following
SQL generated by NHibernate:
Code:
select count(*) as x0_0_
from cam_Subscribers subscriber0_, cam_Campaigns campaign1_
where (exists(select campaign2_.ID from cam_Campaigns campaign2_ where (campaign2_.ID in(select campaigns3_.CampaignID from rel_SubscriberToCampaign campaigns3_ where subscriber0_.ID=campaigns3_.SubscriberID))and(campaign2_.ID=1 )))
This query is then
4x slower than original one. What I'm doing wrong in HQL, can I replicate SQL query in HQL?
NHibernate version: 1.2.1.4000
Name and version of the database you are using: SQL Server 2005/2008
Login
FAQ
Search