"Out of Memory" error when using store procedures

Question

I wrote some services for creating dbs, copying some records among dbs or tables etc, so many db operations. But im dealing with memory problems. One of them:

Im getting records with store procedures and filling in entity lists;

    public List<FpBatteryEntity> LoadFPBattery()
    {
        List<FpBatteryEntity> retVal = new List<FpBatteryEntity>();

        DataSet dataSet = _dataAccessProvider.ExecuteSPDataSet("sp_fp_battery", null);
        if (dataSet != null && dataSet.HasData())
        {
            DataTable requestsTable = dataSet.Tables[0];
            if (requestsTable != null && requestsTable.HasData())
            {
                foreach (DataRow row in requestsTable.Rows)
                {
                    FpBatteryEntity entity = new FpBatteryEntity()
                    {
                        Faceplate = row["tf_faceplate"],
                        Battery = row["tf_battery"]
                    };
                    retVal.Add(entity);
                }
            }
        }
        return retVal;
    }

Then for each entity in list, executing a new sp;

    public bool LoadFPBattery(List<FpBatteryEntity> entityList)
    {
        foreach (var entity in entityList)
        {
            DBParameterCollection parameters = new DBParameterCollection
            {
                new DBParameter("@faceplate", SqlDbType.VarChar, entity.Faceplate),
                new DBParameter("@battery", SqlDbType.VarChar, entity.Battery)
            };

            _dataAccessProvider.ExecuteSPNonQuery("sp_ins_fp_battery", parameters);
        }

        return true;
    }

Unfortunately, I have more than 30 operations like that and also some of them are processing thousands of records.

Calling steps like that;

        .....

        List<MapColorEntity> mapColor = _dbRepository.LoadMapColor(marketId);
        if (!_otherDbRepository.LoadMapColor(mapColor))
            return false;

        List<AttributesEntity> attributes = _dbRepository.LoadOptionAttrib(marketId);
        if (!_otherDbRepository.LoadOptionAttrib(OptionAttributes))
            return false;

        List<FpBatteryEntity> fpBattery = _dbRepository.LoadFPBattery();
        if (!_otherDbRepository.DelFPBattery())
            return false;
        if (!_otherDbRepository.LoadFPBattery(fpBattery))
            return false;

        .....

After 5-6 step operations are corrupting with "Out of Memory" error. I have to make these all operations step by step. Just wondering that if you have a suggestion to make these operations more fruitful and smooth. How can i solve this memory problem? What happens in back?

Usages;

Windows 7 Operating System, 3Gb Usable Ram, Sybase ASE, Odbc Driver

Do you actually need to load every single row from every single one of those queries into memory at once? Can you not stream the results so that you're only holding a fraction of them in memory at one time? Also, don't use one DB context for your entire application; make a new one for each transaction you make; it will help lower your memory footprint. — Servy, May 06 '13 at 13:57
@servy, streaming results maybe serves the purpose, I'll try it. But my code will be more complicated, will not? — htcdmrl, May 07 '13 at 10:43
Not dramatically, depending on what you're doing. Use `IEnumerable` rather than `List` and turn the methods into iterator blocks (which honestly simplifies them, not complicates them). — Servy, May 07 '13 at 13:54

score 1 · Accepted Answer · answered May 06 '13 at 13:58

1

I would suggest using a DataReader instead of a DataTable. It looks like the way you're doing this there will be two copies of your data in memory.

answered May 06 '13 at 13:58

Seth Moore

3,575
2
23
33

thanks fr res, maybe DataReader will solve my problem but I have to use a dll that gives me "_dataAccessProvider.ExecuteSPDataSet". I dont know why but none of dll methods returns with DataReader :( Maybe I have more usable Ram than 3gb, above code will be executed correctly. I think the person that wrote the dll, have unlimited ram or anything else :) Do you have any different idea? makes me functional – htcdmrl May 07 '13 at 10:35
You could possibly remove the record from the DataTable once you've read it. – Seth Moore May 07 '13 at 19:26

"Out of Memory" error when using store procedures

1 Answers1