How to handle large properties (> 20mb)

timiscool999 · **Joined:** Thu Jun 02, 2005 12:00 pm **Posts:** 8

I'm using NHibernate for my project right now and it's working out great. However, I've run into a situation where it's using up way too much memory and I need to fix that.

Basically, I have a class called "Document", which can hold typical stuff like title, body, author, createdDate, etc. One such property on this class is "File", which can hold an attachment. It is a byte[] and in the database it's an Image type, so it can hold data up to 2gig. We're using MSSQL 2k.

So if I query for a list of documents to display to the user it will load all of these attachments (which could be quite large). I don't really need that. All I need really is whether or not a Document HAS an attachment, so that if they need the document then I can load it later.

My ideal situation is to have a computed property on the object called HasAttachment, which has a formula that looks like:
"SELECT CASE WHEN DATALENGTH([File]) > 0 THEN 1 ELSE 0 END"
However, it looks like that isn't supported in NHibernate (the CASE statement).

My next to ideal solution would be to call a stored procedure and give it ID of the object (when the object is loaded) to determine this value, so something like:
formula="DoesDocumentHaveFile(DocumentID)"
but I can't seem to get the formula to actually call the SPROC.

Something that I know will work but it's very messy would be to have an actual extra column called HasAttachment, then make sure to update it every time I change the File property and have a trigger enforce the integrity of the values. Ugh.

Any suggestions? Anybody run into this?

Oh and I can't really lazy load the File property because if I lazy load all of the File properties on a list of Documents just to see if it has an attachment, then that defeats the purpose of NOT returning the data.

sergey · **Posted:** Wed Sep 28, 2005 3:51 pm

NHibernate has some difficulties parsing formulas, what if you try just formula="DATALENGTH([File]) > 0"?

timiscool999 · **Joined:** Thu Jun 02, 2005 12:00 pm **Posts:** 8

Unfortunately that didn't work. However, I changed it a little bit to the following formula:

"SELECT (DATALENGTH([File]) > 0)"

and that generate the following SQL exerpt:

SELECT (DATALENGTH([File]) > 0) as f0_ from CourseAnnouncement coursean0_, Announcement announce1_, Document document2_ where coursean0_.AnnouncementID=announce1_.AnnouncementID and announce1_.DocumentID=document2_.DocumentID and ((coursean0_.CourseID='3'))

Any thoughts?

xor · **Posted:** Thu Sep 29, 2005 4:01 am

You can make a database view which will compute this column at the server. And you can make use of Lightweight Class pattern to load document itself only when it's really necessary.

timiscool999 · **Joined:** Thu Jun 02, 2005 12:00 pm **Posts:** 8

Works perfectly. Thank you so much for you suggestion, xor.

timiscool999 · **Joined:** Thu Jun 02, 2005 12:00 pm **Posts:** 8

Hmm, actually now I am running into another problem.

Reading from the view works great, but now I am having problems inserting into the view. Updating and deleting work fine.

Basically it will not let me update automatically because I have a computed column (whether or not the datalength(file) > 0).

I looked around Google and people have been talking about INSTEAD OF INSERT triggers on the view, but then I get complaints that I can't have DocumentID (The PK of Document table) and HasAttachment (computed column) as NULL.

Does anyone have any experience with using views instead of tables with NHibernate?

xor · **Posted:** Fri Sep 30, 2005 3:16 am

Yes, i'm using view in the mapping and along with INSTEAD OF trigger (as you correctly found) it works fine. Check you trigger again, try to insert into the view using manual SQL command. Is it working ok?

BTW, one of the thing you have to be aware of when using triggers is that DML statement (insert, update, delete) must report exactly the same number of updated records as NH expect. So be prepared to use "set nocount on" (in case of MS SQL server).

You can also try to put insert="false" and update="false" attributes on the mapping of your property. It could help.

timiscool999 · **Joined:** Thu Jun 02, 2005 12:00 pm **Posts:** 8

Here is my trigger. LightweightDocument is my view, Document is my table.

Code:

CREATE TRIGGER InsertIntoLightweightDocument 
ON dbo.LightweightDocument
INSTEAD OF INSERT
AS
BEGIN
        SET NOCOUNT ON

   DECLARE @CategoryID int
   DECLARE @Title   nvarchar(70)
   DECLARE @FileName nvarchar(256)
   DECLARE @FileMimeType nvarchar(100)
   DECLARE @CreatorID int
   DECLARE @OwnerID int
   DECLARE @UpdaterID int
   DECLARE @CreateTime datetime
   DECLARE @UpdateTime datetime
   DECLARE @Status char(1)

   SELECT 
      @CategoryID = CategoryID, 
      @Title = Title,
      @FileName = [Filename],
      @FileMimeType = FileMimeType,
      @CreateTime = CreateTime, 
      @UpdateTime = UpdateTime,
      @Status = Status,
      @UpdaterID = UpdaterID, 
      @OwnerID = OwnerID, 
      @CreatorID = CreatorID
   FROM inserted

   INSERT INTO dbo.Document(CategoryID, Title, [Filename], FileMimeType, CreateTime, UpdateTime, Status, UpdaterID, OwnerID, CreatorID)
   VALUES(@CategoryID, @Title, @FileName, @FileMimeType, @CreateTime, @UpdateTime, @Status, @UpdaterID, @OwnerID, @CreatorID)
END

This works with the following manual SQL statement:

Code:

insert into LightweightDocument(DocumentID,CategoryID,Title,Body,[Filename],[File],FileMimeType,CreateTime,UpdateTime,Status,UpdaterID,OwnerID,CreatorID,HasAttachment)
VALUES(500,3,'t','b','a.ata',null,'application/stuff','1/1/2000','1/1/2000','A',8,8,8,0)

However, there are two problems here:

1. I am inserting with the DocumentID, the PK of the table. NHibernate doesn't send the ID to the server on insert if I am using the "identity" generator. Even though it is ignored, I still have to send it because as xor said I have to send the same amount of arguments to the view as the number of tables returned.

2. I am not inserting the File attachment data and the body data (types "image" and "ntext"), because they aren't allowed in triggers.[/b][/code]