Hi,

version: LLBLGen 3.1, Adapter, dated December 2012 (latest, I believe).

I have a bulk importer utility for importing an XML represented set of Employee records (each Employee has associated child entity collections) into my database.

For each Employee object in the XML, the current Employee object in the database is fetched, then synchonized with the represented Employee object from the XML (there's some necessary synchronization logic for the Employee's child entity collections).

Then that Employee entity (and child entities) are saved.

All works fine for some or some thousand employees.

But when importing 100,000 Employees, we get a "system out of memory" exception (each of the Employee entities could have 100 child entities).

Upon some investigation with with a memory monitoring utility, and posts on your forum. I believe the issue is that I was putting all the Employee records into one transaction...and the references to all the records was held until the .commit is done (and we don't get to .commit because of running out of memory).

The suggestion was to chunk the transaction into smaller chunks. This technically works find and resolves the "system out of memory". From the posting, I believe I understand that llblgen does this so, if there is a .rollback, that the entities aren't left in an incorrect state...which makes sense to have the entities not in this ambiguous state.

But the issue I have is that, if some of these transaction chunks commit, and later ones don't (because of some kind of exception). Then I've lost the atomic "XML bulk import", and my database has some of the data commited from the import, but some of it not: It leaves the database in an ambiguous state (regarding the imported data).

Any suggestions on how to use a large synchronizing XML import, and keeping it atomic (by "atomic", I mean to take advantage of the database's single .commit or .rollback feature)?

side note: I use either Oracle and SQL Server as the database (customer gets to choose for their server), and have a parallel Database-Specific assembly for each database type.

Thanks!

• Do you fetch each entity, then save it and then you garbage-collect it?
• Do you load the full XML to memory or just node by node?

Please post the code you use to do that import. At least a simplistic representation of what you are really doing.

Hi Daelmo,

-Yes, I fetch each entity, then save it. I don't do any manual garbage collection, as I believe the .NET run-time should handle that (although I have experimented with forcing garbage collection--but still have the "system out of memory" error.

-I fully load all the XML into memory (so the XML representation of the Employee objects are all in memory), and I see how this would likely lead to another bottleneck for a larger set of Employees, but right now I'm focusing on the bottleneck of having all the LLBLGen Employee entities (and their child Salary entity collection) in memory.

-Here's a slimmed down represenation of the code:

' Array profileList is an array of Employee objects that are deserialized from XML. Currently, all employee objects are brought into memory, then processed one at a time. ' GetUniqueEmployeeProfileBySsn() uses the llblgen fetch call to get the Employee entity. ' ' In this code, when the following code is commented out, the application crashes "System out of memory". When have this .commit/.starttransaction, the application completes ok: ' If i Mod ProfilesPerChunkInTransaction = 0 AndAlso i > 0 Then ' adapter.Commit() ' adapter.StartTransaction(IsolationLevel.ReadCommitted, "EmpImport" + i.ToString(System.Globalization.CultureInfo.InvariantCulture)) ' End If '

Const ProfilesPerChunkInTransaction as Integer = 5000

' Get adapter from either Oracle or SQL Server (based on .config connection setting of specified database provider) Using adapter As SD.LLBLGen.Pro.ORMSupportClasses.IDataAccessAdapter = Grb.Framework.Business.Lower.FactoryAdapter.FactoryAdapter.GetDataAccessAdapter(m_adapterConnection)

adapter.StartTransaction(IsolationLevel.ReadCommitted, "EmpImport" + i.ToString(System.Globalization.CultureInfo.InvariantCulture))

If profileList IsNot Nothing AndAlso profileList.Length > 0 Then

For i As Integer = 0 To profileList.Length - 1

' Chunk the employee profiles being committed to the database
If i Mod ProfilesPerChunkInTransaction = 0 AndAlso i > 0 Then
  adapter.Commit()
  adapter.StartTransaction(IsolationLevel.ReadCommitted, "EmpImport" + i.ToString(System.Globalization.CultureInfo.InvariantCulture))
End If

  ' General
  'get employee General Entity 
  Dim empGeneralEntity As Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity = Nothing
  empGeneralEntity = Me.m_BusinessManager.GetUniqueEmployeeProfileBySsn(profileList(i).Ssn, domainId, -1, True, True, True, True, True, True, True, True, adapter)

  If empGeneralEntity Is Nothing Then
    empGeneralEntity = New Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity
  End If

  LoadGeneralData(empGeneralEntity, profileList(i), domainId, importedFromPreviousProduct, loginId, selectedReportId, systemEntity)

  ' Salary History
  LoadSalaryData(employeeValidationData(localValidationIndex + i), profileList(i), empGeneralEntity)

  Dim response As Grb.PlugIn.Assist.Business.ProfileResponse = m_BusinessManager.SetEmployeeEntity(empGeneralEntity, adapter, True)

End If

Next

adapter.Commit()

End Using

' Employee Object

'------------------------------------------------------------------------------ ' <auto-generated> ' This code was generated by a tool. ' Runtime Version:4.0.30319.269 ' ' Changes to this file may cause incorrect behavior and will be lost if ' the code is regenerated. ' </auto-generated> '------------------------------------------------------------------------------

' 'This source code was auto-generated by xsd, Version=4.0.30319.1. ' Namespace Xml.Employee

'''<remarks/>
<System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.1"),  _
 System.SerializableAttribute(),  _
 System.Diagnostics.DebuggerStepThroughAttribute(),  _
 System.ComponentModel.DesignerCategoryAttribute("code"),  _
 System.Xml.Serialization.XmlRootAttribute("Import", [Namespace]:="", IsNullable:=false)>  _
Partial Public Class ImportType

    Private profilesField() As ProfileType

    Public Sub New()
        MyBase.New
    End Sub

    '''<remarks/>
    <System.Xml.Serialization.XmlArrayItemAttribute("Profile", IsNullable:=false)>  _
    Public Property Profiles() As ProfileType()
        Get
            Return Me.profilesField
        End Get
        Set
            Me.profilesField = value
        End Set
    End Property

...
End Class

'''<remarks/>
<System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.1"),  _
 System.SerializableAttribute(),  _
 System.Diagnostics.DebuggerStepThroughAttribute(),  _
 System.ComponentModel.DesignerCategoryAttribute("code")>  _
Partial Public Class ProfileType

    Private employeeIdField As String

    Private domainIdField As String

    Private ssnField As String

    Private ssnIncludedField As Boolean

    Private namePrefixField As String

    Private namePrefixIncludedField As Boolean

    Private lastNameField As String

    Private firstNameField As String

    Private middleInitialField As String

    Private middleInitialIncludedField As Boolean

    Private nameSuffixField As String

    Private nameSuffixIncludedField As Boolean

    Public Sub New()
        MyBase.New
        Me.ssnIncludedField = true
        Me.namePrefixIncludedField = true
        Me.middleInitialIncludedField = true
        Me.nameSuffixIncludedField = true

    '''<remarks/>
    <System.Xml.Serialization.XmlElementAttribute(DataType:="positiveInteger")>  _
    Public Property EmployeeId() As String
        Get
            Return Me.employeeIdField
        End Get
        Set
            Me.employeeIdField = value
        End Set
    End Property

    '''<remarks/>
    Public Property Ssn() As String
        Get
            Return Me.ssnField
        End Get
        Set
            Me.ssnField = value
        End Set
    End Property

    '''<remarks/>
    <System.ComponentModel.DefaultValueAttribute(true)>  _
    Public Property SsnIncluded() As Boolean
        Get
            Return Me.ssnIncludedField
        End Get
        Set
            Me.ssnIncludedField = value
        End Set
    End Property

    '''<remarks/>
    Public Property NamePrefix() As String
        Get
            Return Me.namePrefixField
        End Get
        Set
            Me.namePrefixField = value
        End Set
    End Property

    '''<remarks/>
    <System.ComponentModel.DefaultValueAttribute(true)>  _
    Public Property NamePrefixIncluded() As Boolean
        Get
            Return Me.namePrefixIncludedField
        End Get
        Set
            Me.namePrefixIncludedField = value
        End Set
    End Property

    '''<remarks/>
    Public Property LastName() As String
        Get
            Return Me.lastNameField
        End Get
        Set
            Me.lastNameField = value
        End Set
    End Property

    '''<remarks/>
    Public Property FirstName() As String
        Get
            Return Me.firstNameField
        End Get
        Set
            Me.firstNameField = value
        End Set
    End Property

    '''<remarks/>
    Public Property MiddleInitial() As String
        Get
            Return Me.middleInitialField
        End Get
        Set
            Me.middleInitialField = value
        End Set
    End Property

    '''<remarks/>
    <System.ComponentModel.DefaultValueAttribute(true)>  _
    Public Property MiddleInitialIncluded() As Boolean
        Get
            Return Me.middleInitialIncludedField
        End Get
        Set
            Me.middleInitialIncludedField = value
        End Set
    End Property

    '''<remarks/>
    Public Property NameSuffix() As String
        Get
            Return Me.nameSuffixField
        End Get
        Set
            Me.nameSuffixField = value
        End Set
    End Property

    '''<remarks/>
    <System.ComponentModel.DefaultValueAttribute(true)>  _
    Public Property NameSuffixIncluded() As Boolean
        Get
            Return Me.nameSuffixIncludedField
        End Get
        Set
            Me.nameSuffixIncludedField = value
        End Set
    End Property

...
End Class

Do you get the same memory exception if you don;t use an explicit transaction, but rather save the entire graph in a recursive one go (which uses an implecit transaction)?

I just ran a test, and I do get the System.OutOfMemory error when I don't use an explicit transaction.

It appears the only way I don't get the error is by using a series of explicit transactions (chunking the groups of entities into separate transactions).

What does this?

LoadGeneralData(empGeneralEntity, profileList(i), domainId, importedFromPreviousProduct, loginId, selectedReportId, systemEntity)

     ' Salary History
     LoadSalaryData(employeeValidationData(localValidationIndex + i), profileList(i), empGeneralEntity)

Does it work if you remove those lines?

Hi Daelmo,

Following are the LoadGeneralData() and LoadSalaryData() methods.

I commented out these methods, and it seems to run through ok without the "System OutOfMemory" error.

Could the issue have to do with the RemoteEntitiesTracker related code?

Private Shared Sub LoadGeneralData(ByVal employeeGeneralEntity As Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity, _ ByVal currentProfile As Grb.PlugIn.Assist.Business.Import.Xml.Employee.ProfileType, ByVal domainId As Long, _ ByVal importedFromPreviousProduct As Boolean, ByVal loginId As Integer, ByVal selectedReportId As Integer, ByVal systemEntity As _ Grb.Framework.Business.Lower.EntityClasses.SystemEntity)

employeeGeneralEntity.DomainId = Convert.ToInt32(domainId, System.Globalization.CultureInfo.InvariantCulture)
employeeGeneralEntity.NamePrefix = currentProfile.NamePrefix
employeeGeneralEntity.FirstName = currentProfile.FirstName
employeeGeneralEntity.MiddleInitial = currentProfile.MiddleInitial
employeeGeneralEntity.LastName = currentProfile.LastName
employeeGeneralEntity.NameSuffix = currentProfile.NameSuffix

If String.IsNullOrEmpty(currentProfile.Ssn) = False Then
  employeeGeneralEntity.Ssn = currentProfile.Ssn.Replace("-", "").Replace(" ", "")
End If

employeeGeneralEntity.CustomId = currentProfile.CustomId
employeeGeneralEntity.DateOfBirth = currentProfile.DateOfBirth

currentProfile.GenderIncluded = True
If currentProfile.Gender = Import.Xml.Employee.GenderType.Male Then
  employeeGeneralEntity.Gender = Grb.PlugIn.Assist.Business.Constants.GenderMale
ElseIf currentProfile.Gender = Import.Xml.Employee.GenderType.Female Then
  employeeGeneralEntity.Gender = Grb.PlugIn.Assist.Business.Constants.GenderFemale
ElseIf currentProfile.Gender = Import.Xml.Employee.GenderType.Unknown Then
  employeeGeneralEntity.Gender = Grb.PlugIn.Assist.Business.Constants.GenderUnknown
ElseIf currentProfile.Gender = Import.Xml.Employee.GenderType.NotSet Then
  employeeGeneralEntity.Gender = Grb.PlugIn.Assist.Business.Constants.GenderNotSet
Else
  currentProfile.GenderIncluded = False
End If

If currentProfile.ProfileLastUpdatedTimeStamp = System.DateTime.MinValue Then
  employeeGeneralEntity.ProfileLastUpdatedTimeStamp = System.DateTime.Now
Else
  employeeGeneralEntity.ProfileLastUpdatedTimeStamp = currentProfile.ProfileLastUpdatedTimeStamp
End If

employeeGeneralEntity.HomePhone = currentProfile.HomePhone
employeeGeneralEntity.Address1 = currentProfile.AddressLine1
employeeGeneralEntity.Address2 = currentProfile.AddressLine2
employeeGeneralEntity.Address3 = currentProfile.AddressLine3
employeeGeneralEntity.City = currentProfile.City
employeeGeneralEntity.State = currentProfile.State
employeeGeneralEntity.Zip = currentProfile.ZipCode
employeeGeneralEntity.ZipFour = currentProfile.ZipPlusFour
employeeGeneralEntity.County = currentProfile.County
employeeGeneralEntity.Country = currentProfile.Country
employeeGeneralEntity.EmailAddress = currentProfile.Email

...

End Sub

Private Shared Sub LoadSalaryData(ByVal currentEmployeeValidationData As Object, _ ByVal currentProfile As Grb.PlugIn.Assist.Business.Import.Xml.Employee.ProfileType, ByVal empGeneralEntity As Grb.Framework.Business.Lower.EntityClasses.EmpGeneralEntity)

Dim validationData As ImportValidationStatus = CType(currentEmployeeValidationData, ImportValidationStatus)
If (validationData.SalaryHistoryValidationReport IsNot Nothing) AndAlso validationData.SalaryHistoryValidationReport.IsValid Then

  If Not empGeneralEntity.IsNew Then
    Dim fromDateCollection As New System.Collections.Generic.List(Of DateTime)

    For i As Integer = 0 To currentProfile.ProfileSalaryPeriods.Length - 1
      fromDateCollection.Add(currentProfile.ProfileSalaryPeriods(i).FromDate)
    Next

    ' Delete the salary history records from the database that are not present in the Profile package from the imported file
    Dim employeeSalaryEntityCollection As New Grb.Framework.Business.Lower.HelperClasses.EntityCollection(Of Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity)
    employeeSalaryEntityCollection.AddRange(empGeneralEntity.EmpSalaries)

    ' Instantiate a new RemovedEntitiesTracker instance to add salary history records for deleting from database
    empGeneralEntity.EmpSalaries.RemovedEntitiesTracker = New Grb.Framework.Business.Lower.HelperClasses.EntityCollection(Of Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity)

    For Each salaryEntity As Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity In employeeSalaryEntityCollection
      If Not fromDateCollection.Contains(salaryEntity.FromDate) Then
        empGeneralEntity.EmpSalaries.RemovedEntitiesTracker.Add(salaryEntity)
        empGeneralEntity.EmpSalaries.Remove(salaryEntity)
      End If
    Next
  End If

  For i As Integer = 0 To currentProfile.ProfileSalaryPeriods.Length - 1
    Dim empSalaryEntity As Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity = Nothing
    If Not empGeneralEntity.IsNew Then

      If empGeneralEntity.EmpSalaries IsNot Nothing AndAlso empGeneralEntity.EmpSalaries.Count > 0 Then
        For Each salaryEntity As Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity In empGeneralEntity.EmpSalaries
          If salaryEntity.FromDate = currentProfile.ProfileSalaryPeriods(i).FromDate Then
            empSalaryEntity = salaryEntity
            Exit For
          End If
        Next
      End If
    End If

    ' Insert the new salary Entity
    If empSalaryEntity Is Nothing Then
      empSalaryEntity = New Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity
      empSalaryEntity.FromDate = currentProfile.ProfileSalaryPeriods(i).FromDate
      If currentProfile.ProfileSalaryPeriods(i).FromDate = DateTime.MinValue Then
        empSalaryEntity.FromDate = Nothing
      End If
    End If

    empSalaryEntity.ToDate = currentProfile.ProfileSalaryPeriods(i).ToDate
    If currentProfile.ProfileSalaryPeriods(i).ToDate = DateTime.MinValue Then
      empSalaryEntity.ToDate = Nothing
    End If
    empSalaryEntity.BiweeklyTod = CType(currentProfile.ProfileSalaryPeriods(i).BiweeklyTod, Long)
    empSalaryEntity.PaySchedule = currentProfile.ProfileSalaryPeriods(i).PaySchedule
    empSalaryEntity.PayRate = CType(currentProfile.ProfileSalaryPeriods(i).PayRate, Decimal)

    If empSalaryEntity.IsNew Then
      empGeneralEntity.EmpSalaries.Add(empSalaryEntity)
    End If
  Next

End If

End Sub

Hi Andy,

Indeed it looks like the problem could be on LoadGeneralData. I don't know where exactly. I see some things. For instance here...

    For Each salaryEntity As Grb.Framework.Business.Lower.EntityClasses.EmpSalaryEntity In employeeSalaryEntityCollection
         If Not fromDateCollection.Contains(salaryEntity.FromDate) Then
            empGeneralEntity.EmpSalaries.RemovedEntitiesTracker.Add(salaryEntity)
            empGeneralEntity.EmpSalaries.Remove(salaryEntity)
         End If
        Next
     End If

... RemovedEntitiesTracker.Add(...) is unnecessary, as the EmpSalaries.Remove is adding the entity to the tracking collection (ref...).

You also are not deleting the contents of empGeneralEntity.EmpSalaries.RemovedEntitiesTracker. In theory you should call theTracker.DeleteMulti() eventually. I don't know if this is the issue because the variable is local and you are passing all params as ByVal. Anyway, something is wrong there.

If a were you, I would download a .Net memory profiler to get more clues on where is really the problem, otherwise is a guess-work.

Hi Daelmo,

Thanks for the catching the extra: empGeneralEntity.EmpSalaries.RemovedEntitiesTracker.Add(salaryEntity)

I removed it, and the code still seems to (still) work correctly (aside from the "out of memory" issue we're talking about).

I think you meant the problem could be in LoadSalaryData (not LoadGeneralData) right? All LoadGeneralData does is a simple mapping the fields of the EmployeeEntity to values.

I'm also using Adapter, not Self-Service. From looking at the llblgen docs, the DeleteMulti() is for self-service, right? For Adapter, I need to delete the entities in the RemovedEntitiesTracker. I believe I'm doing that correctly (as it seems to work fine), with the following code:

If employeeEntity.EmpSalaries.RemovedEntitiesTracker IsNot Nothing Then externalAdapter.DeleteEntityCollection(employeeEntity.EmpSalaries.RemovedEntitiesTracker) End If

The code sample I gave was not complete enough for you to see this.

I had run a memory profiler (from Redgate) and here's what it showed me: *A big chunk of memory is taken by serializing the XML into a class (the class of employee records/salary records). The program progressses past this, and this memory is moved into Generation 2 managed memory.

*Then the Llblgen "adapter" transaction is commenced, and the associated Employee/Salary period instances continually grow for each snapshot taken (until it crashes with an "out of memory" error)

When the case is run where the transaction is "chunked" (into 5000 employee groups), the memory appears to be released after the commit.

Is there a reason you believe the latest build of llblgen 3.1 doesn't retain memory like described in the post (to my reading, this post would suggest there is a limit to the maximum number of entities that can remain in a trasaction before a commit is done)? http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16665&HighLight=1

Thanks!

Do you have a stack trace to the OutOfMemoryException?

Sure, thre's the stack track for the out-of-memory error:

Program exited with error: The following exception occured at System.String InternalSubString(Int32, Int32, Boolean): Exception of type 'System.OutOfMemoryException' was thrown.

Stack Trace: at System.String.InternalSubString(Int32 startIndex, Int32 length, Boolean fAlwaysCopy) at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy) at System.String.Substring(Int32 startIndex, Int32 length) at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.GetNewSchemaName(String currentName) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\OracleDQE\DynamicQueryEngine.cs:line 522 at SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator.CreateObjectName(IFieldPersistenceInfo persistenceInfo) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\OracleDQE\OracleSpecificCreator.cs:line 376 at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean appendAlias, String containingObjectName, String actualContainingObjectName) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:line 253 at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:line 955 at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Boolean applyAggregateFunction) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:line 286 at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendResultsetFields(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRelationCollection relationsToWalk, DelimitedStringList projection, Boolean sortClausesSpecified, Boolean allowDuplicates, Boolean allowAliasesInSubQuery, UniqueList1 distinctViolatingTypes, IRetrievalQuery query, UniqueList1& fieldNamesInSelectList, Boolean& distinctViolatingTypesFound, Boolean& pkFieldSeen) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1864 at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\OracleDQE\DynamicQueryEngine.cs:line 327 at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1088 at SD.LLBLGen.Pro.DQE.Oracle.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\OracleDQE\DynamicQueryEngine.cs:line 472 at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1156 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4445 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfos, IRelationPredicateBucket filter) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4882 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4823 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1830 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1751 at Xxx.PlugIn.Assist.Business.BusinessManager.SetEmployeeEntity(EmpGeneralEntity employeeEntity, IDataAccessAdapter externalAdapter, Boolean importFlag) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 5832 at Xxx.PlugIn.Assist.Business.BusinessManager.SetEmployeeEntity(EmpGeneralEntity employeeEntity, IDataAccessAdapter externalAdapter, Boolean importFlag) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 5948 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.WriteProfileData(ArrayList employeeValidationData, Int32 validationIndex, ProfileType[] profileList, DomainType[] domainList, FormTemplateType[] formTemplateList, Int32 domainId, Int32 domainCustomIdType, Boolean importedFromPreviousProduct, Boolean partialReportImportAllowed, Boolean partialCollectionImportAllowed, IDataAccessAdapter adapter, Boolean insertFlag, Boolean updateFlag, Int32 userId, Boolean isImportReportAndFormAllowed, String zipBaseDirectory) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 4611 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.WriteProfileData(ArrayList employeeValidationData, Int32 validationIndex, ProfileType[] profileList, DomainType[] domainList, FormTemplateType[] formTemplateList, Int32 domainId, Int32 domainCustomIdType, Boolean importedFromPreviousProduct, Boolean partialReportImportAllowed, Boolean partialCollectionImportAllowed, IDataAccessAdapter adapter, Boolean insertFlag, Boolean updateFlag, Int32 userId, Boolean isImportReportAndFormAllowed, String zipBaseDirectory) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 4649 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.CompleteProductEmployeeXmlImport(Stream xmlStream, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 724 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.CompleteProductEmployeeXmlImport(Stream xmlStream, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 748 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.CompleteProductEmployeeXmlImport(Stream xmlStream, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 758 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.CompleteProductEmployeeXmlImport(Stream xmlStream, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 763 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.ProcessProductEmployeeXmlImport(String originalFileName, Stream fileStream, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 438 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.ProcessProductEmployeeXmlImport(String originalFileName, Stream fileStream, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 445 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.ProcessProductEmployeeXmlImport(String originalFileName, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 375 at Xxx.PlugIn.Assist.Business.ProductEmployeeImportManager.ProcessProductEmployeeXmlImport(String originalFileName, String filePathAndName, String physicalApplicationPath, Int32 userId, String zipBaseDirectory, IDataAccessAdapter adapter) in C:\source\production\AssistWeb\PlugIn\Assist\Business\ProductEmployeeImportManager.vb:line 378 at Xxx.PlugIn.Assist.Business.BusinessManager.RunProductEmployeeImport(ProductEmployeeImportManager importManager1, String filePathAndName, String originalFileName, String xsdFolderPath, Boolean testMode, Int32 userId) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 7289 at Xxx.PlugIn.Assist.Business.BusinessManager.RunProductEmployeeImport(ProductEmployeeImportManager importManager1, String filePathAndName, String originalFileName, String xsdFolderPath, Boolean testMode, Int32 userId) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 7331 at Xxx.PlugIn.Assist.Business.BusinessManager.RunProductEmployeeImport(ProductEmployeeImportManager importManager1, String filePathAndName, String originalFileName, String xsdFolderPath, Boolean testMode, Int32 userId) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 7349 at Xxx.PlugIn.Assist.Business.BusinessManager.DoProductEmployeeImport(String filePathAndName, String originalFileName, String xsdFolderPath, String productDomainName, String frameworkDomainName, AddLogMessageMethod addMessageDelegate, ClearLogMessagesMethod clearMessageDelegate, Int32 userId, Boolean verboseLogging, Boolean validateAllFormsAndReportsInZipFile, Boolean logDetails) in C:\source\production\AssistWeb\PlugIn\Assist\Business\BusinessManager.vb:line 7239 at Import.Main.Main() in C:\source\production\AssistWeb\Utilities\Assist\AssistWebImport\Main.vb:line 335.

greenstone wrote:

I think you meant the problem could be in LoadSalaryData (not LoadGeneralData) right? All LoadGeneralData does is a simple mapping the fields of the EmployeeEntity to values.

Yes, I meant LoadSalaryData.

greenstone wrote:

I'm also using Adapter, not Self-Service. From looking at the llblgen docs, the DeleteMulti() is for self-service, right? For Adapter, I need to delete the entities in the RemovedEntitiesTracker. I believe I'm doing that correctly (as it seems to work fine), with the following code:

If employeeEntity.EmpSalaries.RemovedEntitiesTracker IsNot Nothing Then externalAdapter.DeleteEntityCollection(employeeEntity.EmpSalaries.RemovedEntitiesTracker) End If

The code sample I gave was not complete enough for you to see this.

Is there something in the complete code that may be involved in the OutOfMemory exception? Where did you call this DeleteEntityCollection?

greenstone wrote:

I had run a memory profiler (from Redgate) and here's what it showed me: *A big chunk of memory is taken by serializing the XML into a class (the class of employee records/salary records). The program progressses past this, and this memory is moved into Generation 2 managed memory.

*Then the Llblgen "adapter" transaction is commenced, and the associated Employee/Salary period instances continually grow for each snapshot taken (until it crashes with an "out of memory" error)

When the case is run where the transaction is "chunked" (into 5000 employee groups), the memory appears to be released after the commit.

I don't understand where exactly the program is serializing XML into classes. Do you have that information from Redgate's memory profiler? It also seems that the big memory increase is happening just before the commit. Is that right?

Dividing the transaction in chunks could be that it sanitizes some blocked entity references, by applying different loops instead of one, but still, I fail to see where exactly is the problem.

I also don't understand why the stackTrace doesn't show your LoadSalaryData method, but if you comment that method call, everything works. It could be that your code are holding references to other objects that could not be liberated because they are actually referenced somewhere else as well. Could you debug and investigate on the memory profiler to reduce the possibilites?

Hi Daelmo,

Here's the basic structure of the complete code:

1.) De-serialize the XML into a hierarchical class (this does take a big chunk of memory that is be elevated...later on...into Gen 2 of the Managed Memory). The XML contains a long list of "Employee" objects (with associated array of child "Salary" objects)

2.) Create an adapter object

3.) Start a transaction on that adapter

4.) Loop through the Employee objects (and sub-loop through child Salary objects), fetch the LLBLGen Employee entity from the database (and Salary entties).

5.) Synchronize the Employee Entity with the (XML represented) Employee object (mapping fields based on a field-by-field synchronization mapping set of rules).

6.) Continue loop to the next Employee Entity.

-->When get through a few million entities, get the system-out-of-memory error.

From looking at the memory profiler, the only memory that is building up (when starting to loop through the Employee objects, and fetching the Employee Entities) are the LLBLGen generated Employee/(child)Salary Entities. The memory profiler seems to suggest these entities are not released for garbage collection until the .commit is done. The memory profiler also lists the transaction object as the root reference holder for these entities.

This is what I thought I had read on another/other LLBGen posts, and this would seem to explain the out-of-memory error. But I guess you're suggesting that the LLBGen adapter does not retain the memory of the Entity instances until the .commit is done?

The LoadSalaryMethod being commented out reduces the number of LLBLGen entities created by 85% (as the Employee entity is still created, but the 6 child Salary entities are no longer created with it being commented out). So it seems to make sense to me that commenting this out might cause it to skip the out-of-memory error.

Thanks

greenstone wrote:

Hi Daelmo,

Here's the basic structure of the complete code:

1.) De-serialize the XML into a hierarchical class (this does take a big chunk of memory that is be elevated...later on...into Gen 2 of the Managed Memory). The XML contains a long list of "Employee" objects (with associated array of child "Salary" objects)

2.) Create an adapter object

3.) Start a transaction on that adapter

4.) Loop through the Employee objects (and sub-loop through child Salary objects), fetch the LLBLGen Employee entity from the database (and Salary entties).

5.) Synchronize the Employee Entity with the (XML represented) Employee object (mapping fields based on a field-by-field synchronization mapping set of rules).

6.) Continue loop to the next Employee Entity.

-->When get through a few million entities, get the system-out-of-memory error.

From looking at the memory profiler, the only memory that is building up (when starting to loop through the Employee objects, and fetching the Employee Entities) are the LLBLGen generated Employee/(child)Salary Entities. The memory profiler seems to suggest these entities are not released for garbage collection until the .commit is done. The memory profiler also lists the transaction object as the root reference holder for these entities.

Correct. Entities which are part of a transaction are kept alive, because when the transaction rolls back, the values in the entities are rolled back as well.

This is what I thought I had read on another/other LLBGen posts, and this would seem to explain the out-of-memory error. But I guess you're suggesting that the LLBGen adapter does not retain the memory of the Entity instances until the .commit is done?

It does.

The LoadSalaryMethod being commented out reduces the number of LLBLGen entities created by 85% (as the Employee entity is still created, but the 6 child Salary entities are no longer created with it being commented out). So it seems to make sense to me that commenting this out might cause it to skip the out-of-memory error.

My question is: what are you doing to millions of entities in a transaction? It in general will be faster to create update queries which work directly on the DB (called through UpdateEntitiesDirectly()) so you don't have to fetch them (and thus keep them in memory till the transaction is over).

Hi Otis,

I took a look at the UpdateEntitiesDirectly, and it seem like much more efficient way for doing updates of existing records.

I'm not sure it will will help for my case. Here's some sample data for my case. First listed is the data that is assumed to exist in the database, second is the new XML formatted data to be imported.

IN DATABASE:

Employee Table: ID, FirstName, LastName, EmailAddress 111223333, Joe, Smith, joe.smith@test.com 111224444, Bill, Jones, bill.jones@test.com

Salary Table: EmployeeId, StartDate, EndDate, Salary 111223333, 01/01/2010, 01/15/2012, 50000 111223333, 01/16/2010, 01/31/2012, 50000 111224444, 01/01/2010, 01/15/2012, 40000 111224444, 01/16/2010, 01/31/2012, 40000

NEW XML DATA:

Employee Table: ID, FirstName, LastName, EmailAddress 111223333, Joe, Smith, jsmith@test.com 111224444, Bill, Jones, bjones@test.com 111225555, Otis, Williams, owilliams@test.com

Salary Table: EmployeeId, StartDate, EndDate, Salary 111223333, 01/01/2010, 01/15/2012, 50000 111223333, 01/16/2010, 01/31/2012, 50000 111224444, 01/01/2010, 01/15/2012, 40000 111224444, 01/16/2010, 01/31/2012, 40000 111225555, 01/01/2010, 01/15/2012, 30000 111225555, 01/16/2010, 01/31/2012, 30000

Would the UpdateEntitiesDirectly help the case where the import is doing both an UPDATE of existing employee records, and an INSERT on new records?

The import utility also needs to have the following logic:

*If salary records exist in the XML for an employee, synchronize the salary records (delete records in database that don't exist in xml, insert new records, update existing records -- Salary table unique-keyed on EmployeeId + StartDate)

*If no salary records exist in the XML for an employee, leave the Salary table records in the database untouched (but update/insert the Employee records).

I believe I have two memory bottlenecks in how the application is implemented right now. With either of them greatly reduced, I'm guessing I probably wouldn't be seeing this issue at the number of records i'm trying to process. To make the application able to process a much larger number of records, I will probably need to address both.

1.) I am deserializing the Employee/Salary data (from the xml) into a monolithic hierarchical class (with child Employee arrays and sub-child Salary arrays).

To greatly optimize memory consumption, I should probably programmatically split the xml into separate temporary files, then process one file at a time.

2.) The LLBLGen adapter's transaction is retaining memory for each Employee/Salary entity until the commit is completed. The chunking of the entities into seprate .commits does seem to alleviate the issue, but the downside is that the import process is not atomic (if it fails somewhere along the way, some of the data is committed, and some is not).

You could, (as a workaround), create an 'import catalog', an empty database on the same server which is only used for imports. You can then import the millions of rows in there, using multiple transactions. if one fails, clear the DB, start over (as it's only used for imports anyway). When everything succeeds, call a stored procedure which performs the real updates on the server, by updating rows in the real DB with data from the import DB. This is fast as no data has to be loaded from the DB into memory of the client. Then, when that succeeds, clear the import DB, done.

Thanks for the suggested work around. It certainly seems concepually feasible, but a fair amount of work--creating a hierarchy of temporary tables all with the proper foreign key constraints (and Employee has service periods which have service period details, etc...). A smaller issue is that we would need to adjust the tightend-down users that are auto-created to all stored procedure access again (we got rid of all the stored procs when we went to LLBLGen...and that was a great thing...so would be sad to see that come back--from a maintenance point of view). There are also multiple users who can concurrently do imports, so we would need to keep track of user session in these tables also.

We have many customers using our database schema in Oracle or SQL Server (we keep one LLBLGen projet that maps to either...with a factory for giving the right llbgen adapter). So whatever work-around with stored procedures, we have to do for both SQLServer and for Oracle.

With all that said, temporarily, we are just doing a bunch of commits along the way. This is a not-pretty (I hope!) temporary solution.

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

greenstone wrote:

With all that said, temporarily, we are just doing a bunch of commits along the way. This is a not-pretty (I hope!) temporary solution.

Mmm. Ok.

greenstone wrote:

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.

Please open a full new thread for this new issue. That way we can start over in a cleaner way and leave this thread. Post all relevant information so we can understand/reproduce the issue and suggest a workaround if needed.

Hi,

Finally back to this issue and trying to come up with a longer-term solution...

Will LLBLGen 4.0's enhanced memory footprint help with this this issue?

Otis said: _Entities which are part of a transaction are kept alive, because when the transaction rolls back, the values in the entities are rolled back as well. _

Might it be possible 4.0 has an option added/can be added, for a write-only situation like this (where we don't care about the state of the entities after the transaction), where the entity's memory is not held...so a large transaction can be performed?

kind regards,

Andy

_

greenstone wrote: Hi Daelmo,

Here's the basic structure of the complete code:

1.) De-serialize the XML into a hierarchical class (this does take a big chunk of memory that is be elevated...later on...into Gen 2 of the Managed Memory). The XML contains a long list of "Employee" objects (with associated array of child "Salary" objects)

2.) Create an adapter object

3.) Start a transaction on that adapter

4.) Loop through the Employee objects (and sub-loop through child Salary objects), fetch the LLBLGen Employee entity from the database (and Salary entties).

5.) Synchronize the Employee Entity with the (XML represented) Employee object (mapping fields based on a field-by-field synchronization mapping set of rules).

6.) Continue loop to the next Employee Entity.

-->When get through a few million entities, get the system-out-of-memory error.

From looking at the memory profiler, the only memory that is building up (when starting to loop through the Employee objects, and fetching the Employee Entities) are the LLBLGen generated Employee/(child)Salary Entities. The memory profiler seems to suggest these entities are not released for garbage collection until the .commit is done. The memory profiler also lists the transaction object as the root reference holder for these entities.

Correct. Entities which are part of a transaction are kept alive, because when the transaction rolls back, the values in the entities are rolled back as well.

Quote:

This is what I thought I had read on another/other LLBGen posts, and this would seem to explain the out-of-memory error. But I guess you're suggesting that the LLBGen adapter does not retain the memory of the Entity instances until the .commit is done?

It does. _

v4 has a much lower memory footprint, but it still keeps entities alive which participate in a transaction.

The thing is that if a rollback happens, and you say 'the entity state is not important', what is the entity state then? Except for some edge cases, there's little use of a system which leaves the entity state as 'undefined' when a rollback takes place.

So you either save an employee and its associated salary entities without a transaction, then throw the entity away (and don't cache the salary entities), which creates an implicit transaction, or save each entity individually, with the recurse parameter of 'SaveEntity' set to false, which implies that there's no transaction started.

I'd go for the first, save employee and its salary entities without a transaction, which means it will start a new one for those entities: if something goes wrong, you can roll back that transaction, and retry if needed.

The question of course is: does the whole batch as a whole require rollback if 1 entity fails? Because on the server, the DB also keeps rows locked during the transaction till it completes, which might hurt performance.

There's a 3rd option, which is to alter the runtime sourcecode for this. If this is an importer, you might consider this, and use a special build of the ormsupportclasses dll. The code you need to alter is in DataAccessAdapterBase.AddTransactionParticipant. There, the entity participating in the transaction is added to a dictionary. Simply remove all code there, and the entity won't backup its changed fields, and the entity isn't logged in a dictionary to be notified when the transaction completes.

Keep in mind that you do things in a loop and the GC will likely not clean up every iteration of that loop, so it might be you still require significant amounts of memory, as the loop likely will burn through a lot of objects before the GC will notice there's memory pressure and it has to collect objects: you can force it to collect with GC.Collect, however use that only as a last resort.

Hi Otis,

Thanks for the response.

Yes, this is for an data import service. For most customers, the import is run on a nightly basis with "tonight's" data. The issue we're trying to address is that, if there is an error in the import, we don't want some of last night's data, and some of tonight's data. We want all of last night's (when fails) , or all of tonight's (when successful). Having a mix of tonight's and last night's leads to an ambiguous situation for the application.

Thanks for detailing the option of modifying the run-time library for the AddTransactionParticipant...and the caution about the garbage collection.

I'll need to think about this...