如何提高ADO.NET中大规模INSERT的速度？

Question

我下载了高级数据库服务器 (ADS) 10.1 的试用版，以及 ARC 和 ADO.NET 提供程序。我的主要目的是了解与 SQLite .NET (http://sqlite.phxsoftware.com/) 相比，从数百万条记录中进行大规模插入的性能。

在 ADS 中，30 分钟内加载了 700 万条条目。

在 Sqlite for.NET 中，同样的 700 万个条目在不到 3 分钟内就被加载了！

为什么？我可以采取哪些措施来提高 .NET 提供商的 ADS 速度？

问候 。

编辑

感谢您的建议，在ADS代码中我错误地包含了一些索引的创建，当我抑制它时，加载经过的时间是10分钟。

让我添加一些示例代码和示例数据（您可以将其相乘，直到获得 700 万个条目）。如果您能找到增强和优化性能的方法，请告诉我。

SYBASE ADS 代码：

 public void LoadAds(string opt, string file)
    {
        AdsConnection conn = new AdsConnection(@"data source=C:\apps\dataApps\cmpExistenc\inv.ads;" +
            "ServerType=local; TableType=ADT");
        conn.Open();
        var stV = new st();
        var dicTxt = new Dictionary<object, st>();
        if (opt.ToUpper() == "C")
        {
            using (AdsCommand cmd = conn.CreateCommand())
            {
                try
                {
                    cmd.CommandText = "DROP TABLE lbl; DROP TABLE almlbl";
                    cmd.ExecuteNonQuery();
                }
                catch { }
                cmd.CommandText =
                    "CREATE TABLE lbl (alm varchar(4), alm2 varchar(4), " +
                       "mat varchar(18), ser varchar(20), fac varchar(18), almlbl varchar(10), " +
                       "cant integer, sts varchar(1), ser_2_20 varchar(20), rowid_sap integer, stsmat varchar(100));";
                cmd.ExecuteNonQuery();
                cmd.CommandText =
                    "CREATE TABLE almlbl (almlbl varchar(10), almlbltxt varchar(100), " +
                       "ciudad varchar(50));";
                cmd.ExecuteNonQuery();
            }
        }
        else
            using (AdsCommand cmdTxt = conn.CreateCommand())
            {
                cmdTxt.CommandText = "SELECT * from almlbl";
                AdsDataReader drT = cmdTxt.ExecuteReader();
                while (drT.Read())
                    dicTxt[drT[0]] = new st() { almlblTxt = drT[1], ciudad = drT[2] };
                drT.Dispose();
                cmdTxt.CommandText = "DELETE FROM almlbl";
                cmdTxt.ExecuteNonQuery();
            }


        using (AdsTransaction transac = conn.BeginTransaction())
        {
            AdsCommand cmd = conn.CreateCommand();
            cmd.Transaction = transac;
            AdsParameter param = cmd.CreateParameter();
            cmd = LibCorp.Ads.buildParmsFromTable("lbl", conn, transac);
            long regLei = 0;
            List<object> cols;
            try
            {
                StreamReader sr = new StreamReader(file, Encoding.Default);
                sr.ReadLine(); // Ignore title
                string line;
                while ((line = sr.ReadLine()) != null)
                {
                    if ((++regLei % 1000000) == 0)
                        o.show(string.Format(" lbl:{0}", regLei), tbx);
                    cols = new List<object>(line.Split('|'));
                    if (!dicTxt.ContainsKey((string)cols[5]))
                    {
                        stV.almlblTxt = cols[6];
                        stV.ciudad = cols[8];
                        dicTxt[cols[5]] = stV;
                    }
                    if (!cols[3].Equals("") && cols[3].ToString().Length > 18)
                        cols.Add(cols[3].ToString().Substring(1));
                    else
                        cols.Add(DBNull.Value);

                    cmd.Parameters[0].Value = cols[0];       // alm
                    cmd.Parameters[1].Value = cols[1];       // alm2
                    cmd.Parameters[2].Value = cols[2];       // mat
                    cmd.Parameters[3].Value = cols[3];       // ser
                    cmd.Parameters[4].Value = cols[4];       // fac
                    cmd.Parameters[5].Value = cols[5];       // almlbl
                    cmd.Parameters[6].Value = cols[7];       // cant
                    cmd.Parameters[7].Value = DBNull.Value;  // sts
                    cmd.Parameters[8].Value = cols[10];      // ser_2_20
                    cmd.Parameters[9].Value = DBNull.Value;  // rowid_sap
                    cmd.Parameters[10].Value = cols[9];      // stsmat
                    cmd.ExecuteNonQuery();
                }

                foreach (KeyValuePair<object, st> pair in dicTxt)
                {
                    cmd.CommandText = string.Format("INSERT INTO almlbl VALUES('{0}','{1}','{2}')",
                            pair.Key, pair.Value.almlblTxt, pair.Value.ciudad);
                    cmd.ExecuteNonQuery();
                }

                transac.Commit();
            }
            catch (Exception ex)
            {
                o.notify(string.Format("{0}\n\rSitio->{1}", ex, ex.TargetSite.Name));
            }
            finally
            {
                conn.Close();
            }

        }
    }

.NET SQLite 代码：

public void LoadSQLITE(string opt, string file)
    {
        conn = new SQLiteConnection
            (@"Data Source=inv.db3; Page Size=65536; Cache Size=65536; Synchronous=Off; Journal Mode=Off;");
        conn.Open();
        var stV = new st();
        var dicTxt = new Dictionary<object, st>(); // faster than SortedDictionary
        if (opt.ToUpper() == "C")
        {
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                try
                {
                    cmd.CommandText = "DROP TABLE lbl; DROP TABLE almlbl";
                    cmd.ExecuteNonQuery();
                }
                catch { }
                cmd.CommandText =
                    "CREATE TABLE lbl (alm varchar(4), alm2 varchar(4), " +
                       "mat varchar(18), ser varchar(20), fac varchar(18), almlbl varchar(10), " +
                       "cant integer, sts varchar(1), ser_2_20 varchar(20), rowid_sap integer, stsmat varchar);" +
                    "CREATE TABLE almlbl (almlbl varchar(10), almlbltxt varchar(100), " +
                       "ciudad varchar(50));"; //+
                cmd.ExecuteNonQuery();
            }
        }
        else
            using (SQLiteCommand cmdTxt = new SQLiteCommand(conn))
            {
                cmdTxt.CommandText = "SELECT * from almlbl";
                SQLiteDataReader drT = cmdTxt.ExecuteReader();
                while (drT.Read())
                    dicTxt[drT[0]] = new st() { almlblTxt = drT[1], ciudad = drT[2] };
                drT.Dispose();
                cmdTxt.CommandText = "DELETE FROM almlbl";
                cmdTxt.ExecuteNonQuery();
            }

        using (SQLiteTransaction transac = conn.BeginTransaction())
        {
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                SQLiteParameter param = new SQLiteParameter();
                SQLiteCommand cmdAux = LibCorp.Lite.buildParmsFromTable("lbl", conn);
                cmd.CommandText = cmdAux.CommandText;
                foreach (SQLiteParameter sp in cmdAux.Parameters)
                    cmd.Parameters.Add(sp);
                long regLei = 0;
                try
                {
                    StreamReader sr = new StreamReader(file, Encoding.Default);
                    sr.ReadLine(); // Ignore title
                    List<object> cols;
                    string line;
                    while (!string.Equals(line = sr.ReadLine(), null)) // Fastest way
                    {
                        if ((++regLei % 1000000) == 0)  //Diff of only 1 or 2 secs if omitted
                            o.show(string.Format(" lbl:{0}", regLei), tbx);
                        cols = new List<object>(line.Split('|')); // Fastest way
                        if (!dicTxt.ContainsKey((string)cols[5])) // diff of only 1 sec if commented
                        {
                            stV.almlblTxt = cols[6];
                            stV.ciudad = cols[8];
                            dicTxt[cols[5]] = stV;
                        }

                        if (!cols[3].Equals("") && cols[3].ToString().Length > 18)
                            cols.Add(cols[3].ToString().Substring(1));
                        else
                            cols.Add(DBNull.Value);

                        cmd.Parameters[0].Value = cols[0];       // alm
                        cmd.Parameters[1].Value = cols[1];       // alm2
                        cmd.Parameters[2].Value = cols[2];       // mat
                        cmd.Parameters[3].Value = cols[3];       // ser
                        cmd.Parameters[4].Value = cols[4];       // fac
                        cmd.Parameters[5].Value = cols[5];       // almlbl
                        cmd.Parameters[6].Value = cols[7];       // cant
                        cmd.Parameters[7].Value = DBNull.Value;  // sts
                        cmd.Parameters[8].Value = cols[10];      // ser_2_20
                        cmd.Parameters[9].Value = DBNull.Value;  // rowid_sap
                        cmd.Parameters[10].Value = cols[9];      // stsmat
                        cmd.ExecuteNonQuery();
                    }

                    foreach (KeyValuePair<object, st> pair in dicTxt)
                    {
                        cmd.CommandText = string.Format("INSERT INTO almlbl VALUES('{0}','{1}','{2}')",
                                pair.Key, pair.Value.almlblTxt, pair.Value.ciudad);
                        cmd.ExecuteNonQuery();
                    }

                    transac.Commit();
                }
                catch (Exception ex)
                {
                    o.notify(string.Format("{0}\n\rSitio->{1}", ex, ex.TargetSite.Name));
                }
                finally
                {
                    conn.Close();
                }
            }

        }
    }

以及一些测试数据（乘以 700 万，请包括标题行）：

COD_ALMACEN_SAP|COD_ALMACEN_SAP2|CODIGO_SAP|NRO_SERIE_INICIAL|NRO_INICIO_FACTURA|COD_ALMACEN|NOMBRE_ALMACEN|CANTIDAD|CIUDAD|NOMBRE_ARTICULO

1030|8030|ADAJKIUSD66K|||16|ALMACEN DANSA PRUZ TESULARES|4|BANDA PRUZ|ADA -KI-SD66K 适配器 MAIDIUM SD-66K 1030|8030|BAT-KI-BPA101|||16|阿尔马森兰塔克鲁斯特苏拉雷斯|5|班达普鲁兹|BAT-KI-BPA101 电池 1020|8020|TARGESA/P_PC-GC79|||17|周边中心|9|POCHASALTA|TARGESA/P_PC-GC79 TARGESA 通用 P PC GPRS Y WL 1010|8010|TARJETA/P_PC-GC79|||1014|领土北 1|5|PATPAZ|TARGESA/P_PC-GC79 TARJETA 通用 P PC GPRS Y WL 1060|8060|TARJETA/P_PC-GC79|||1095|ALMACEN SUNY|1|TRONOSAD|TARGESA/P_PC-GC79 TARGESA 通用 P PC GPRS Y WL

enter code here

问候。

问候。

Answer 1

首先确保在这两种情况下您使用的事务和查询是相同的。第二，确保您以相同的方式调用插入查询 - 不要在一种情况下创建冗余（对于每个插入调用）连接、查询或适配器对象，而在另一种情况下则不然。差别太……显着了。

Answer 2

尝试删除交易。 ADS 事务与传统的 RDBMS 不同，需要更多的操作系统刷新操作（ADS 不使用检查点）。如果没有事务，性能会更好。

编辑注意到您正在使用本地服务器，因此我对交易的评论将不相关。当当！

另外，我不知道第二个循环（INSERT INTO almlbl）涉及多少个循环，但更改为带有参数的准备好的查询会有所帮助。

所有这些 SQLite 行很可能尚未位于磁盘上。我假设有相当多的内容在内存中并且还没有被刷新。