c# - 从数据库快速加载大对象图

Question

你好
我有自己的用 C# 编写的数据结构，例如:

public class ElectricScheme
{
    public List<Element> Elements { get; set; }
    public List<Net> Nets { get; set; }
}

public class Element
{       
    public string IdName { get; set; }
    public string Func { get; set; }
    public string Name { get; set; }
    public BaseElementType Type { get; set; }
    public List<Pin> Pins { get; set; }
}

public class Pin
{
    public string IdName { get; set; }
    public string Name { get; set; }
    public BasePinType PinType { get; set; }
    public BasePinDirection PinDirection { get; set; }
}

public class Net
{
    public string IdName { get; set; }
    public string Name { get; set; }
    public List<Tuple<Element,Pin>> ConnectionPoints { get; set; }
}

其中元素计数~19000，每个元素包含 >=3 个 Pin，
网数 ~20000，每个网包含 >=3 对(元素、引脚)

解析 txt(文件大小 ~17mb)文件需要 5 分钟。默认序列化程序的序列化/反序列化 ~3 分钟。从数据库加载 20 分钟但未加载...我像使用 Entity Framework

  public ElectricScheme LoadScheme(int schemeId)
  {
    var eScheme = (from s in container.ElectricSchemesSet
                   where s.IdElectricScheme.Equals(schemeId)
                   select s).FirstOrDefault();
    if (eScheme == null) return null;<br/>
    container.LoadProperty(eScheme, "Elements");
    container.LoadProperty(eScheme, "Nets");
    container.LoadProperty(eScheme, "Elements.Pins");
    return eScheme;
  }<p></p>

<p></p>

问题是 Element 和 Pin 之间的依赖关系...(对于 ~19000 个元素~95000 个 pins)

有什么想法吗？

Answer 1

任务解决方案:
数据库结构(MS SQL CE):

-- Creating table 'ElectricSchemesSet'
CREATE TABLE [ElectricSchemesSet] (
    [IdElectricScheme] int  NOT NULL,
    [Name] nvarchar(4000)  NOT NULL,
    [SourceFileHash] nvarchar(4000)  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [LastUpdatedDate] datetime  NOT NULL
);
-- Creating table 'Nets'
CREATE TABLE [Nets] (
    [IdNet] int  NOT NULL,
    [IdName] nvarchar(4000)  NOT NULL,
    [Name] nvarchar(4000)  NOT NULL,
    [ElectricSchemes_IdElectricScheme] int  NOT NULL
);
-- Creating table 'Elements'
CREATE TABLE [Elements] (
    [IdElement] int  NOT NULL,
    [Func] nvarchar(4000)  NULL,
    [IdName] nvarchar(4000)  NOT NULL,
    [Name] nvarchar(4000)  NOT NULL,
    [Type] nvarchar(4000)  NOT NULL,
    [ElectricSchemes_IdElectricScheme] int  NOT NULL
);
-- Creating table 'Pins'
CREATE TABLE [Pins] (
    [IdPin] int  NOT NULL,
    [IdName] nvarchar(4000)  NOT NULL,
    [Name] nvarchar(4000)  NOT NULL,
    [Direction] nvarchar(4000)  NOT NULL,
    [Type] nvarchar(4000)  NOT NULL,
    [ElementsIdElement] int  NOT NULL,
    [ElectricSchemesIdElectricScheme] int  NOT NULL
);
-- Creating table 'NetConnections'
CREATE TABLE [NetConnections] (
    [IdNetConnections] int  NOT NULL,
    [NetsIdNet] int  NOT NULL,
    [ElementsIdElement] int  NOT NULL,
    [PinsIdPin] int  NOT NULL,
    [ElectricSchemesIdElectricScheme] int  NOT NULL
);

使用 Entity Framework 加载(单线程 2.08 分钟，双核 1.48 分钟):

public ElectricScheme LoadScheme(int schemeId)
        {
            var eScheme = (from s in container.ElectricSchemesSet
                            where s.IdElectricScheme.Equals(schemeId)
                            select s).FirstOrDefault();<p></p>

<pre><code>        if (eScheme == null)
        {
            return null;
        }
        TaskFactory tf = new TaskFactory();

        Elements[] elems = null;
        IGrouping<int, Pins>[] pins = null;
        List<Element> mElements = null;

        var loadElements = tf.StartNew(() =>
                                       elems = (from e in container.Elements
                                                where e.ElectricSchemes.IdElectricScheme.Equals(schemeId)
                                                select e).ToArray());

        var loadPins = tf.StartNew(() =>
                                   pins = (from p in container.Pins
                                           where p.ElectricSchemesIdElectricScheme.Equals(schemeId)
                                           select p)
                                           .GroupBy(x => x.ElementsIdElement).ToArray());

        var buildElements = tf.ContinueWhenAll(
            new Task[] {loadElements, loadPins},
            delegate { mElements = Builder.BuildElement(elems, pins); });

        Nets[] net = null;
        IGrouping<int, NetConnections>[] nConn = null;
        List<Net> mNet = null;
        var loadNet =tf.StartNew(() =>
                    net = (from n in container.Nets
                           where n.ElectricSchemes.IdElectricScheme.Equals(schemeId)
                           select n).ToArray());

        var loadConn = tf.StartNew(() =>
                    nConn = (from c in container.NetConnections
                             where c.ElectricScheme.IdElectricScheme.Equals(schemeId)
                             select c)
                             .GroupBy(x => x.NetsIdNet).ToArray());

        var buildNet = tf.ContinueWhenAll(
            new Task[] {loadNet, loadConn},
            delegate { mNet = Builder.BuildNet(net, nConn); });

        ElectricScheme scheme = null;
        var buildScheme = tf.ContinueWhenAll(new Task[] {buildElements, buildNet},
                           delegate { scheme = Builder.BuildScheme(mNet, mElements, eScheme.IdElectricScheme); });

        buildScheme.Wait();
        return scheme;

    }
</code></pre>

<p></p>

保存()(45秒):

 public void SaveUsingPureSql(string connectionString)
        {
            using (SqlCeConnection connection = new SqlCeConnection(connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    SaveSchemeToTable(connection, transaction);
                    SaveElementsAndPinsToTable(connection, transaction);
                    SaveNetWithConnectionsToTable(connection, transaction);<p></p>

                transaction.Commit();
            }
        }
    }
    private void SaveNetWithConnectionsToTable(SqlCeConnection conn, SqlCeTransaction transaction)
    {
        string insertNetsQuery = "INSERT INTO  Nets VALUES (@id,@IdName,@Name,@ElectricSchemes_IdElectricSchemes);";
        string insertNetConnectionQuery = "INSERT INTO  NetConnections VALUES (@id,@NetsIdNet,@ElementsIdElemtnt,@PinsIdPin,@ElectricSchemesIdElectricScheme);";

        SqlCeCommand netsCommand = new SqlCeCommand(insertNetsQuery, conn, transaction);
        SqlCeCommand netConnectionCommand = new SqlCeCommand(insertNetConnectionQuery, conn, transaction);

        netsCommand.Parameters.Add("@id", SqlDbType.BigInt);
        netsCommand.Parameters.Add("@ElectricSchemes_IdElectricSchemes", SqlDbType.BigInt);
        netsCommand.Parameters.Add("@IdName", SqlDbType.NVarChar);
        netsCommand.Parameters.Add("@Name", SqlDbType.NVarChar);

        netConnectionCommand.Parameters.Add("@id", SqlDbType.BigInt);
        netConnectionCommand.Parameters.Add("@NetsIdNet", SqlDbType.BigInt);
        netConnectionCommand.Parameters.Add("@ElementsIdElemtnt", SqlDbType.BigInt);
        netConnectionCommand.Parameters.Add("@PinsIdPin", SqlDbType.BigInt);
        netConnectionCommand.Parameters.Add("@ElectricSchemesIdElectricScheme", SqlDbType.BigInt);

        foreach (var net in scheme.Nets)
        {
            net.Id = lastNetId++;
            netsCommand.Parameters["@id"].Value = net.Id;
            netsCommand.Parameters["@ElectricSchemes_IdElectricSchemes"].Value = lastSchemeId;
            netsCommand.Parameters["@IdName"].Value = net.IdName;
            netsCommand.Parameters["@Name"].Value = net.Name;

            netsCommand.ExecuteNonQuery();

            foreach (var point in net.ConnectionPoints)
            {
                netConnectionCommand.Parameters["@id"].Value = lastPinConnectionId++;
                netConnectionCommand.Parameters["@NetsIdNet"].Value = net.Id;
                netConnectionCommand.Parameters["@ElementsIdElemtnt"].Value = point.Item1.Id;
                netConnectionCommand.Parameters["@PinsIdPin"].Value = point.Item2.Id;
                netConnectionCommand.Parameters["@ElectricSchemesIdElectricScheme"].Value = lastSchemeId;
                netConnectionCommand.ExecuteNonQuery();
            }
        }
    }
    private void SaveSchemeToTable(SqlCeConnection conn, SqlCeTransaction transaction)
    {
        string query = "INSERT INTO  ElectricSchemesSet VALUES (@id,@name,@SourceFileHash,@CreatedDate,@LastUpdatedDate);";
        SqlCeCommand command = new SqlCeCommand(query,conn,transaction);

        ValueType timeNow = DateTime.Now;

        scheme.Id = lastSchemeId;

        command.Parameters.AddWithValue("@id", scheme.Id);
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@SourceFileHash", sourceFileHash);
        command.Parameters.AddWithValue("@CreatedDate", timeNow);
        command.Parameters.AddWithValue("@LastUpdatedDate", timeNow);

        command.ExecuteNonQuery();
    }
    private void SaveElementsAndPinsToTable(SqlCeConnection conn, SqlCeTransaction transaction)
    {
        string insertElementQuery =
            "INSERT INTO Elements VALUES (@id,@Func,@IdName,@Name,@Type,@ElectricSchemes_IdElectricSchemes);";
        string insertPinQuery =
            "INSERT INTO Pins VALUES (@id,@IdName,@Name,@Direction,@Type,@ElementIdElement, @ElectricSchemesIdElectricSchemes);";

        var elementCommand = new SqlCeCommand(insertElementQuery, conn, transaction);
        elementCommand.Parameters.Add("@id", SqlDbType.BigInt);
        elementCommand.Parameters.Add("@ElectricSchemes_IdElectricSchemes", SqlDbType.BigInt);
        elementCommand.Parameters.Add("@Func", SqlDbType.NVarChar);
        elementCommand.Parameters.Add("@IdName", SqlDbType.NVarChar);
        elementCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
        elementCommand.Parameters.Add("@Type", SqlDbType.NVarChar);

        var pinCommand = new SqlCeCommand(insertPinQuery, conn, transaction);
        pinCommand.Parameters.Add("@id", SqlDbType.BigInt);
        pinCommand.Parameters.Add("@ElementIdElement", SqlDbType.BigInt);
        pinCommand.Parameters.Add("@ElectricSchemesIdElectricSchemes", SqlDbType.BigInt);
        pinCommand.Parameters.Add("@IdName", SqlDbType.NVarChar);
        pinCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
        pinCommand.Parameters.Add("@Direction", SqlDbType.NVarChar);
        pinCommand.Parameters.Add("@Type", SqlDbType.NVarChar);

        foreach (var element in scheme.Elements)
        {
            element.Id = lastElementId++;
            elementCommand.Parameters["@id"].Value = element.Id;
            elementCommand.Parameters["@ElectricSchemes_IdElectricSchemes"].Value = lastSchemeId;
            elementCommand.Parameters["@Func"].Value = element.Func;
            elementCommand.Parameters["@IdName"].Value = element.IdName;
            elementCommand.Parameters["@Name"].Value = element.Name;
            elementCommand.Parameters["@Type"].Value = element.Type.ToString();

            elementCommand.ExecuteNonQuery();
            foreach (var pin in element.Pins)
            {
                pin.Id = lastPinId++;
                pinCommand.Parameters["@id"].Value = pin.Id;
                pinCommand.Parameters["@ElementIdElement"].Value = element.Id;
                pinCommand.Parameters["@ElectricSchemesIdElectricSchemes"].Value = lastSchemeId;
                pinCommand.Parameters["@IdName"].Value = pin.IdName;
                pinCommand.Parameters["@Name"].Value = pin.Name;
                pinCommand.Parameters["@Direction"].Value = pin.PinDirection.ToString();
                pinCommand.Parameters["@Type"].Value = pin.PinType.ToString();

                pinCommand.ExecuteNonQuery();
            }
        }

    }