DataSet Cascade and Memory consumption [closed]

Question

I have an application which uses DataSet.WriteXML to export data and DataSet.ReadXML to import data. During the import process I need to change certain primary keys as part of the application logic.

When there are over 500K records, it writes to XML and read from XML successfully. Once I change the primary key it waits some time and throws an OutOfMemory exception. The reason as I believe is, it has to do a lot of cascade updates. I tried BeginEdit and EndEdit during primary key change, but still failing in EndEdit in that case.

As I have understood, DataSets keeps some of previous data also in memory. Is there any way to optimize DataSet update operations in a way it consumes minimum memeory?

Answer 1

If you need more control then you'll need to remove some of the functionality that the dataset gives you. One way of reducing memory caused by cascades is simple Don't Cascade. Update the tables ID's manually using the tables schema.

The idea is you can then control which rows are updated, AcceptChanges at any point, force GC mid update or anything else you might want to control.

I've created a simple test scenario which shows what I mean:

Schema:

<?xml version="1.0"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Planet">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Continent">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="PlanetID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Country">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="ContinentID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="County">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="CountryID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="City">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="CountyID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Street">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="CityID" type="xs:int" minOccurs="0" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="People">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="StreetID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Job">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="PeopleID" type="xs:int" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Pets">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" />
              <xs:element name="PeopleID" type="xs:int" minOccurs="0" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1">
      <xs:selector xpath=".//Planet" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="Continent_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//Continent" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="Country_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//Country" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="County_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//County" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="City_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//City" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="Street_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//Street" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="People_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//People" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="Job_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//Job" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:unique name="Pets_Constraint1" msdata:ConstraintName="Constraint1">
      <xs:selector xpath=".//Pets" />
      <xs:field xpath="ID" />
    </xs:unique>
    <xs:keyref name="Relation8" refer="People_Constraint1">
      <xs:selector xpath=".//Pets" />
      <xs:field xpath="PeopleID" />
    </xs:keyref>
    <xs:keyref name="Relation7" refer="People_Constraint1">
      <xs:selector xpath=".//Job" />
      <xs:field xpath="PeopleID" />
    </xs:keyref>
    <xs:keyref name="Relation6" refer="Street_Constraint1">
      <xs:selector xpath=".//People" />
      <xs:field xpath="StreetID" />
    </xs:keyref>
    <xs:keyref name="Relation5" refer="City_Constraint1">
      <xs:selector xpath=".//Street" />
      <xs:field xpath="CityID" />
    </xs:keyref>
    <xs:keyref name="Relation4" refer="County_Constraint1">
      <xs:selector xpath=".//City" />
      <xs:field xpath="CountyID" />
    </xs:keyref>
    <xs:keyref name="Relation3" refer="Country_Constraint1">
      <xs:selector xpath=".//County" />
      <xs:field xpath="CountryID" />
    </xs:keyref>
    <xs:keyref name="Relation2" refer="Continent_Constraint1">
      <xs:selector xpath=".//Country" />
      <xs:field xpath="ContinentID" />
    </xs:keyref>
    <xs:keyref name="Relation1" refer="Constraint1">
      <xs:selector xpath=".//Continent" />
      <xs:field xpath="PlanetID" />
    </xs:keyref>
  </xs:element>
</xs:schema>

And some code which generates a test case

    private void CreateRows(Int32 MaxBaseRows, Int32 MaxChildRows)
    {
        dataSet1.Clear();
        Int32 RowCount = 0;
        Random R = new Random();
        foreach (DataTable DT in dataSet1.Tables)
        {
            Int32 NewCount = R.Next(1, MaxBaseRows);
            foreach (var FK in DT.Constraints.OfType<ForeignKeyConstraint>())
            {
                NewCount = NewCount * R.Next(1, MaxChildRows);
            }
            for (int i = 0; i < NewCount; i++)
            {
                DataRow DR = DT.NewRow();
                foreach (DataColumn DC in DT.Columns)
                {
                    if (DC.ColumnName == "ID")
                    {
                        DR[DC] = DT.Rows.Count;
                    }
                    else if (DC.DataType == typeof(Int32))
                    {
                        Boolean ValueSet = false;
                        foreach (var FK in DT.Constraints.OfType<ForeignKeyConstraint>())
                        {
                            if (FK.Columns.Contains(DC))
                            {
                                DR[DC] = R.Next(0, FK.RelatedTable.Rows.Count);
                                ValueSet = true;
                            }
                        }
                        if (!ValueSet)
                        {
                            DR[DC] = R.Next(0, 10000);
                        }
                    }
                    else if (DC.DataType == typeof(String))
                    {
                        DR[DC] = String.Format("{0}{1}", DT.TableName, DT.Rows.Count);
                    }
                }
                DT.Rows.Add(DR);
                RowCount++;
            }
        }
        label19.Text = RowCount.ToString();
        dataSet1.AcceptChanges();
    }


    private void UpdateUsingCascade()
    {
        EnableRelations();
        GC.Collect();
        long Mem = System.GC.GetTotalMemory(false);
        if (dataSet1.Tables["Planet"].Rows.Count > 0)
        {
            dataSet1.Tables["Planet"].Rows[0]["ID"] = new Random().Next(BaseRowCount, BaseRowCount + 10);
        }
        Mem = System.GC.GetTotalMemory(false) - Mem;
        DataSet ds = dataSet1.GetChanges();
        Int32 Changes = ds.Tables.OfType<DataTable>().Sum(DT => DT.Rows.Count);
        label19.Text = Changes.ToString();
        label21.Text = Mem.ToString();
        dataSet1.AcceptChanges();
    }

    private void UpdateManually()
    {
        DisableRelations();
        GC.Collect();
        long Mem = System.GC.GetTotalMemory(false);

        DataTable DT = dataSet1.Tables["Planet"];
        Int32 ChangeCount = 0;
        if (DT.Rows.Count > 0)
        {
            DataColumn DC = DT.Columns["ID"];
            Int32 oldValue = Convert.ToInt32(DT.Rows[0][DC]);
            DT.Rows[0][DC] = new Random().Next(BaseRowCount + 20,BaseRowCount + 30);
            Int32 newValue = Convert.ToInt32(DT.Rows[0][DC]);
            foreach (DataRelation Relation in DT.ChildRelations)
            {
                if (Relation.ParentColumns.Contains(DC))
                {
                    foreach (DataColumn CC in Relation.ChildColumns)
                    {
                        foreach (DataRow DR in Relation.ChildTable.Rows)
                        {
                            if (Convert.ToInt32(DR[CC]) == oldValue)
                            {
                                DR[CC] = newValue;
                                ChangeCount++;
                                dataSet1.AcceptChanges();
                                GC.Collect();
                            }
                        }
                    }
                }
            }
        }
        Mem = System.GC.GetTotalMemory(false) - Mem;
        label20.Text = ChangeCount.ToString();
        label22.Text = Mem.ToString();
        dataSet1.AcceptChanges();
    }

    private void EnableRelations()
    {
        dataSet1.EnforceConstraints = true;
        foreach (DataRelation Relation in dataSet1.Relations)
        {
            Relation.ChildKeyConstraint.UpdateRule = Rule.Cascade;
        }
    }

    private void DisableRelations()
    {
        dataSet1.EnforceConstraints = false;
        foreach (DataRelation Relation in dataSet1.Relations)
        {
            Relation.ChildKeyConstraint.UpdateRule = Rule.None;
        }
    }