System.OutOfMemory exception LLBLGen 3.1

Posts   
 
    
greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 04-Nov-2013 20:23:32   

Hi,

This is spin-off of ticket: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21601&StartAtMessage=0&#125882

Per Daelmo, have split it into a separate thread, as it's a different area of the application.

However, I believe it has the same root cause of the thread 21601.

Thoughts on how to address this?

...

Today, have come across another issue with the LLBLGen classes consuming too much memory in a single transaction. We have a "database utility" (C# program) that has an XML file holding SQL Statements (for both Oracle and SQL Server). The utility gets the "version" of the database from a table, then sequentially updates from that version to the latest version.

One feature we added to the utility was to (via a config setting) encrypt/decrypt a pre-specified set of columns in the database. The encryption key is held in the .NET application and an LLBLGen type converter (we wrote) does the encryption/decryption of database before doing to/from the database.

note: we did app level encryption -vs- database level encryption, because the customers are trying to protect their data from being easily viewed by their DBAs.

All works well the the encryption in the web application/import utilty. However, our "database utility", which does an encryption/decryption on all records in the datbase, appears to be running out of memory for large number of database records. The case is we have some millions of records in the database that it is trying to encrypt/decrypt.

I suppose a work-around would be to bypass using the LLBLGen code and write straight ADO.NET code to do this, but then we have to write (probably) special code for Oracle and for SQL Server...and have the code loose all the cleanliness/elegance of using the LLBLGen entities.

Any chance of making a "write only" mode for LLBLGen fetching enties, where memory is not being held for each entity in a transaction until it's committed?

Thanks,

Andy

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 04-Nov-2013 23:21:22   

However, our "database utility", which does an encryption/decryption on all records in the datbase, appears to be running out of memory for large number of database records. The case is we have some millions of records in the database that it is trying to encrypt/decrypt.

How are you performing this? Simple code snippet or pseudo code will do.

Fetching all records in a collection to encrypt the fields and then save the entire collection back wouldn't be the best thing to do.

I suggest doing this in chunks either by fetching and modifying pages of entities (say 100 each), using an EntityCollection.

Or using a dataReader to read data and update one entity at a time.

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 05-Nov-2013 16:03:49   

Hi Walaa,

Here's a code segment where we get the issue.

    private static string ProcessEncryption(string databaseConnectionText, Common.ILogging logger, string encryptionMode, string createOrUpdate, 
      Grb.Framework.Business.Lower.FactoryAdapter.AdapterConnection adapterConnection)
    {
      string errorMessage = string.Empty;

        using (SD.LLBLGen.Pro.ORMSupportClasses.IDataAccessAdapter adapter = Grb.Framework.Business.Lower.FactoryAdapter.FactoryAdapter.GetDataAccessAdapter(adapterConnection))
        {
          //Check for valid database connection
          try 
          {
            adapter.OpenConnection();
          } 
          catch (Exception)
          {
            errorMessage = string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.OpenDatabaseConnectionError1, databaseConnectionText);
          }     

          // Start transaction
          if (string.IsNullOrEmpty(errorMessage))
          {
            try
            {
              adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, StringConstant.Encryption.EncryptionTransactionString);
            }
            catch (Exception ex)
            {
              errorMessage = string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.ErrorCreatingTransaction1, Grb.Core.Exceptions.ExceptionCrawler.GetExceptionReport(ex));
            }
          }

          //Encryption/Decryption
          if (string.IsNullOrEmpty(errorMessage))
          {
              errorMessage = ProcessEmpGeneral(logger, adapter, system, desiredEncryptionVersion);
          }

          // Close transaction/connection
          if (string.IsNullOrEmpty(errorMessage))
          {
            LogMessage(logger, string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.BeginToCommitTransactionAt1, System.DateTime.Now), Common.LoggingCategory.Information);

            try
            {
              adapter.Commit();
            }
            catch (Exception)
            {
              adapter.Rollback();
              errorMessage = string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.ErrorCommitingTransaction);
            }
            finally
            {
                adapter.CloseConnection();
                adapter.Dispose();
            }

            LogMessage(logger, string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.SuccessfullyCommittedTransactionAt1, System.DateTime.Now), Common.LoggingCategory.Error);

          }
          else if (adapter.IsTransactionInProgress)
          {
            adapter.Rollback();
            LogMessage(logger, EncryptionResource.ErrorCommitingTransaction, Common.LoggingCategory.Error);
          }
        }
      return errorMessage;
    }






   private static string ProcessEmpGeneral(Common.ILogging logger, SD.LLBLGen.Pro.ORMSupportClasses.IDataAccessAdapter adapter, Grb.Framework.Business.Lower.EntityClasses.SystemEntity system, int desiredEncryptionVersion)
    {
      LogMessage(logger, string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.FetchingEmpGeneral1, System.DateTime.Now), Common.LoggingCategory.Information);

      string errorMessage = String.Empty;
      Grb.Framework.Business.Lower.HelperClasses.EntityCollection<Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity> empGeneralEntityCollection 
        = new Grb.Framework.Business.Lower.HelperClasses.EntityCollection<Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity>();

      //fields to be included
      //Version 1
      SD.LLBLGen.Pro.ORMSupportClasses.IncludeFieldsList includeFieldsList = new SD.LLBLGen.Pro.ORMSupportClasses.IncludeFieldsList();
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.CustomId);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.FirstName);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.LastName);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.MiddleInitial);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseFirstName);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseLastName);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseMiddleInitial);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseSsn);
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.Ssn);   
      //Version 2
      includeFieldsList.Add(Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.BankAccountNumber);
    
      int numEntities = adapter.GetDbCount(empGeneralEntityCollection, null);
      int numPages = numEntities / Constants.Encryption.PageSize;
      if (numEntities % Constants.Encryption.PageSize > 0) 
        numPages += 1;

      for (int i = 1; i <= numPages; i++)
      {
        
        try
        {
          adapter.FetchEntityCollection(empGeneralEntityCollection, null, Constants.Encryption.PageSize, null, null, includeFieldsList, i, Constants.Encryption.PageSize);
        }
        catch (Exception ex)
        {
          errorMessage = string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.ErrorFetchingEntity1, Grb.Core.Exceptions.ExceptionCrawler.GetExceptionReport(ex));
          break;
        }
        
        foreach (Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity entity in empGeneralEntityCollection)
        {
          entity.IsDirty = true;

          //Version Encryption 1
          if (((desiredEncryptionVersion > Constants.Encryption.UnencryptedVersion) && (system.VersionEncryption < 1)) //Encrypting
            || ((desiredEncryptionVersion == Constants.Encryption.UnencryptedVersion) && (system.VersionEncryption >= 1)))  //Decrypting
          {
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.CustomId.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.FirstName.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.LastName.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.MiddleInitial.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseFirstName.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseLastName.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseMiddleInitial.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.SpouseSsn.FieldIndex].IsChanged = true;
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.Ssn.FieldIndex].IsChanged = true;     
          }

          //Version Encryption 2
          if (((desiredEncryptionVersion > Constants.Encryption.UnencryptedVersion) && (system.VersionEncryption < 2)) //Encrypting
            || ((desiredEncryptionVersion == Constants.Encryption.UnencryptedVersion) && (system.VersionEncryption >= 2)))  //Decrypting
          {
            entity.Fields[Grb.Framework.Business.Lower.HelperClasses.EmpGeneralFields.BankAccountNumber.FieldIndex].IsChanged = true;
          }
        }

        try
        {
          adapter.SaveEntityCollection(empGeneralEntityCollection);
        }
        catch (Exception ex)
        {
          errorMessage = string.Format(System.Globalization.CultureInfo.InvariantCulture, EncryptionResource.ErrorSavingEntity1, Grb.Core.Exceptions.ExceptionCrawler.GetExceptionReport(ex));
          break;
        }
        empGeneralEntityCollection.Clear();

        //Log Progress
        LogMessage(logger, String.Format(System.Globalization.CultureInfo.InvariantCulture,
          EncryptionResource.ProgressStatus4, (i - 1) * Constants.Encryption.PageSize + 1,
          (i == numPages ? numEntities : i * Constants.Encryption.PageSize), numEntities, System.DateTime.Now), Common.LoggingCategory.Information);
      }

      return errorMessage;
    }
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Nov-2013 17:13:11   

You're currently doing all work in 1 transaction, which will run out of memory if you process a tremendous amount of entities, as described in the other thread simple_smile

what about saving the data in batches/chunks so you don't have a long-running transaction?

Frans Bouma | Lead developer LLBLGen Pro
greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 05-Nov-2013 18:32:49   

Hi Otis,

This code is run against the database to encrypt/decrypt the data. Right now, we store one flag that specifies if the database is encrypted (or not). It would be a real problem if half-way through encrypting the fields of the records, then the encryption failed: We would wind up half done and half not. Then when the application ran it would be expecting all the data as encrypted (or not), and half would work, and half would fail encryption.

I suppose I could either try to look at the piece of data, and try to determine if it's encrypted. Or maybe add another column to the record to flag if it's encrypted or not.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 05-Nov-2013 18:55:20   

You should move the GetDBCount and the FetchEntityCollection out of the Transaction. Just the SaveEntityCollection should be inside a transaction.

Also fetching and updating chunks would be better.

Logic would be as follows.

While (entities exists (Fetch Top 100 entities where encryptedFlag = false) ) { Incrypt data and set the encryptedFlag = true Save entities. }

greenstone
User
Posts: 132
Joined: 20-Jun-2007
# Posted on: 05-Nov-2013 20:27:14   

Thanks for the ideas and options!