Retriving single record from database exceeding 25MB causes OutOfMemoryException

Question

0.00/5 (No votes)

See more:

Hi all,
I am running into a problem when selecting a single record from our email database.
The record is over 25MB (estimated, probably more) in size and every time I try to get it from the context it throws an OutOfMemoryException.
NoTracking is unfortunately no option in this case in order to avoid the second copy.

The application copies emails to an archive database and I already set the batch size to 1 record per context before disposing and recreating the context (using statement)

Here is a stub for the function in question:

using (EmailDataEntities context = ContextFactory.GetEmailDataEntities())
{
    using (EmailArchiveDataEntities contextArchive = visualbooking.EmailArchiveDataContext.ContextFactory.GetEmailArchiveDataEntities())
    {
        foreach (long id in idList)
        {
            try
            {
                EmailDataContext.Model.tblEMailPostBox oDataEmail = context.tblEMailPostBox.FirstOrDefault(f => f.id == id);

                // copy the data here
                            

                // deleting original email


                // saving both contexts


                // Accepting all changes


            }
            catch (Exception ex)
            {
                // logging exception
            }
        }
    }
}

The exception is thrown in the .FirstOrDefault() part, copying and saving is working in all cases except in the case of the big record.
Right now I just skipped the record. It is possible to copy it manually in the database, but I'd like to know about the background causing the problem... or the limits of records if there are any...

Is there a way to get the record regardless of its size?
Or is there a workaround?

Any help is kindly appreciated,
have a great day
Andy

Posted 27-Mar-14 4:18am

hoernchenmeister

Updated 27-Mar-14 4:27am

v2

Add a Solution

Comments

Oshtri Deka 27-Mar-14 10:21am

Can we see some code?
What sort of application is this?
Service? WebApp?

hoernchenmeister 27-Mar-14 10:29am

Hi Oshtri Deka,
thanks a lot for your interest in my question.
It's a Console App that will later be called by a Scheduler.
Right now I am testing it and experienced the problem mentioned above.
I am just trying to get a better overview about the possible impact of this (there might be more of those large records in the future) before taking it to the live environment.
Best regards
Andy

hoernchenmeister 27-Mar-14 10:29am

I have added the relevant code stub :)

Maarten Kools 28-Mar-14 2:37am

What does the property context.tblEMailPostBox represent? If I had to wager a guess, I'd say all records. When you execute FirstOrDefault, the IEnumerable will be iterated (which, by the way, is very inefficient in your loop over the ids). If this property contains all records then you might have a problem with memory, because it would load every single record in memory (no idea how many we're talking about here?) and from that collection you select only one.

hoernchenmeister 7-Apr-14 8:45am

Dear Maarten,
sorry for being so late with my reply and thanks a lot for your help on this one.
That's a really good point you made here. the property represents indeed a complete table. The "id" field is the primary key for this table. I always assumed thet FirstOrDefault would be the right way to retrieve a single record (over SingleOrDefault). Would it be better to use a where statement first?
Like this:
EmailDataContext.Model.tblEMailPostBox oDataEmail = context.tblEMailPostBox.Where(f => f.id == id).FirstOrDefault();

have a great day
Andy

Maarten Kools 7-Apr-14 9:01am

Hi Andy,

Using a Where clause has no effect, as you can pass the same predicate to FirstOrDefault. context.tblEMailPostBox.FirstOrDefault(f => f.id == id) and context.tblEMailPostBox.Where(f => f.id == id).FirstOrDefault() will yield exactly the same result. The Where clause is obsolete in this case (if you use a tool like Resharper[^] for example, it will tell you so).

It really depends on what context.tblEMailPostBox represents. If it's an IEnumerable[^] that is being created with LINQ to SQL you have to watch out for something called deferred execution[^]. Each time you would call FirstOrDefault[^] the LINQ query would be executed. So if you would loop over, let's say, 10 Ids, then the LINQ statement would be executed 10 times as well. That makes it, very inefficient and I can imagine perhaps an issue with memory as well.

I'd say it would be much more efficient to query the right records from the database, instead of querying everything and resolving it in your application (as you are doing now).

hoernchenmeister 7-Apr-14 9:25am

Thanks again Maarten for your time,
Resharper indeed told me to skip the Where clause :)
In this scenario context.TblEmailPostbox is an ObjectSet<tblEMailPostBox>. I am currently implementing the precompiled version of the query:

Func<emaildataentities, long,="" emaildatacontext.model.tblemailpostbox=""> getSingleEmailByIdQuery = CompiledQuery.Compile<emaildataentities, long,="" emaildatacontext.model.tblemailpostbox="">((context, emailId) => context.tblEMailPostBox.FirstOrDefault(f => f.id == emailId));

and I invoke it like this: tblEMailPostBox oDataEmail = getSingleEmailByIdQuery.Invoke(context, id); just for improving the performance as I have read about.

I have run into the OutOfMemoryException about five times and it's a really big record (Email with attachments - structure is pretty old and was accessed mainly using a SqlDataReader).

Just to make sure I understood correctly. You mentioned I should "query the right records from the database, instead of querying everything and resolving it in your application (as you are doing now)". This is confusing me a little because I assumed I would do it like that. The context variable is the ObjectContext I use for accessing the table. Would you mind to give me a little hint on how to query the right records only in this example?

I really appreciate your help Maarten,
cheers
Andy

Maarten Kools 7-Apr-14 14:15pm

To be honest, I haven't worked with an ObjectSet or CompiledQuery before, so I don't know the inner workings of it. But you have to make sure that you're not querying all records from the database, store it in memory and select the records from that set. If you need only 10 records, it makes no sense keeping all 100 (or whatever it is) in memory. But like I said, I haven't worked with an ObjectSet before, so I don't know whether it's doing that or not.

hoernchenmeister 8-Apr-14 2:39am

I checked the query using the SQL Analyzer tool and it creates a query like this when I use FirstOrDefault:
SELECT TOP 1 * FROM tblEmailPostbox WHERE id = @id

...so I assume that's all right. ObjectSet does not contain records, it just represents the table object to query from.
Anyway Maarten, I really appreciate your help on this one.
Thanks a lot!
Cheers
Andy

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Retriving single record from database exceeding 25MB causes OutOfMemoryException

Add your solution here

Preview 0

Retriving single record from database exceeding 25MB causes OutOfMemoryException

Add your solution here

Preview 0

Existing Members

...or Join us