- Home
- LLBLGen Pro
- Bugs & Issues
System.OutOfMemory exception LLBLGen 3.1
Joined: 20-Jun-2007
Hi,
This is spin-off of ticket: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21601&StartAtMessage=0𞮺
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
Joined: 21-Aug-2005
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.
Joined: 20-Jun-2007
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;
}
Joined: 17-Aug-2003
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
what about saving the data in batches/chunks so you don't have a long-running transaction?
Joined: 20-Jun-2007
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.
Joined: 21-Aug-2005
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. }
Joined: 20-Jun-2007