C# DbDataReader populating List result in OutOfMemoryException

Question

I am currently trying to read a large table from an compact ce database containing aprox 3-4 million rows. The database size i currently 832MB. Populating a list with the records is throwing OutOfMemoryException

The mockup code:

    using (var con = new DomainContext())
    {
        foreach (var item in con.logRecords)
        {
            if (item.Info != null && item.Info != "")
                item.Timestamp = DateTime.ParseExact(item.Info, "MM.dd.yyyy HH:mm:ss.fff", culture).Ticks;
        }
        con.SaveChanges();

    }

New approach, still not getting it to work....

        Task.Factory.StartNew(() =>
        {
            using (var con = new DomainContext())
            {
                for (int i = 0; i < 300; i++)
                {
                    try
                    {
                        var temp = con.logRecords.Where(p => p.Id <= i * 10000 + 10000 && p.Id >= i * 10000);

                        foreach (var item in temp)
                        {

                            if (item.Info != null && item.Info != "")
                                item.Timestamp = DateTime.ParseExact(item.Info, "MM.dd.yyyy HH:mm:ss.fff", culture).Ticks;
                        }

                        con.SaveChanges();

                    }
                    catch { }
                    GC.Collect();
                    Console.WriteLine(i.ToString());


                }
            }
        });

Because you are always using the same string literal in the second code. This is interned in the .NET string pool. If you would use `new String("WGwegWEGwegWEGwegWEGwegWEGweg".ToCharArray())` you would get the same exception because strings which are initialized through constructor aren't interned. — Tim Schmelter, Jul 28 '16 at 11:58
Ahh, Exactly! Thanks! I guess I have to figure out how to do this in bulks.... — jsandv, Jul 28 '16 at 12:04
The best optimization would be to not load all into memory but process group of records(by database paging) or by omitting records(using `WHERE ...`). — Tim Schmelter, Jul 28 '16 at 12:11
My new approach wasnt successful either. Could any provide an example? — jsandv, Jul 28 '16 at 19:53
Yes SQL would be equally great. I tried to figure out how to do that datetime conversion to ticks in SQL. Unfortunatly DateDiff only returns int, so it overflows if i try to get a valid value for ticks. — jsandv, Jul 28 '16 at 20:52

score 0 · Answer 1 · answered Aug 01 '16 at 10:34

0

I used native SQL, parsed timestamp to SQL timestamp then found number off seconds since 1970 using DATEDIFF ( datepart , startdate , enddate ). Added number of seconds since year 0. I lose millisecond part, but i guess this is the next best thing.

answered Aug 01 '16 at 10:34

jsandv

276
4
20

C# DbDataReader populating List result in OutOfMemoryException

1 Answers1