Hibernate version: 1.2.0 GA
Name and version of the database you are using: Oracle 10g
Hi,
I'm trying to rewrite a perl-based web app to use ASP.Net and NHibernate. I'm having a problem mapping an edit history table to an NHibernate object. Since there is existing code that uses this database, I would like to avoid changing the schema, but can if that is the only way of fixing this.
Here are what my current tables look like:
Code:
Event (Id, Name, etc)
Faq (Id, Name, etc)
Product (Id, Name, etc)
Promotion (Id, Name, etc)
User (Id, First, Last, ...)
EditRecord( Id, RecordId, ChangeDate, TableName, UserId)
previously I was doing a query like:
Code:
Select P.*, er.Changedate, U.* from Promotion P
Left Join EditRecord ER on ( P.Id = Er.RecordId AND Er.TableName = 'PROMOTION')
Left Join User U On (Er.UserId = U.Id)
WHERE ER.Id = (SELECT max(id) from EditRecord e2 where e2.recordId = Promotion.Id and e2.tablename = 'promotion')
-- followed by other Where conditions
I'm working on a summary page for the Promotion table, which shows a list of search results like this:
Promotion Name, Last Edit Date, User Name
My first attempt at this was to create this Oracle view:
Code:
CREATE VIEW LastEditRecord AS
SELECT * FROM EDITRECORD
WHERE id IN
(SELECT MAX(id)
FROM EDITRECORD
GROUP BY RecordId, TableName
)
Then my mapping file was:
Code:
<class name="LastEditRecord" table="lasteditrecord" mutable="false">
<id name="Id" column="id" type="int">
<generator class="assigned" />
</id>
<discriminator column="tablename" />
<property name="RecordId" column="recordid" />
<property name="ChangeDate" column="changedate" />
<property name="UserId" column="userid" />
<subclass name="LastEditRecordEvent" discriminator-value="EVENT" />
<subclass name="LastEditRecordFaq" discriminator-value="FAQ" />
<subclass name="LastEditRecordProduct" discriminator-value="PRODUCT" />
<!-- subclass name="LastEditRecordPromotion" discriminator-value="PROMOTION" -->
</class>
and then the Promotion class had a one-to-one mapping to LastEditPromotion.
Now, this all worked,
except that Oracle performs really poorly when doing this kind of view, so poorly that it takes up to a minute to render this page, where then old version took only a few seconds.
I've tried searching this site and the web to find examples of someone doing this sort of thing, but have found nothing. I thought using the <loader> and <sql-query> tags might work, but I can't get any them working. (may mapping for those is:
Code:
<class name="LastEditRecordPromotion" table="editrecord" mutable="false">
<id name="Id" column="id" type="int">
<generator class="assigned" />
</id>
<!-- property name="TableName" column="tablename" -->
<property name="RecordId" column="recordid">
<column name="RecordId" />
</property>
<property name="ChangeDate" column="changedate" />
<property name="UserId" column="userid" />
<loader query-ref="loadLastEditPromotion" />
</class>
<sql-query name="loadLastEditPromotion" >
<return class="LastEditRecord">
<return-discriminator column="tablename"/>
<return-property name="Id" column="id" />
<return-property name="RecordId" column="recordid" />
<return-property name="ChangeDate" column="changedate" />
<return-property name="UserId" column="userid" />
</return>
<![CDATA[
SELECT * From EditRecord
WHERE id in
(select max(id)
from EditRecord
group by RecordId, TableName
)
AND RecordId = :RecordId
AND TableName = 'PROMOTION'
]]>
</sql-query>
Any suggestions on a the correct course of action?
Login
FAQ
Search