Databases
StackOverflow2013
Postsdbo
POHow can I enhance the speed of massive INSERT in ADO.NET?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I enhance the speed of massive INSERT in ADO.NET?
    primarykey
    data
    text
    <p>I downloaded a trial of Advanced Database Server (ADS) 10.1, together with ARC and ADO.NET provider. My main intention was to know the performance of massive INSERTS from millions of records compared to SQLite .NET (http://sqlite.phxsoftware.com/).</p> <p>In ADS, 7 millions entries were loaded in 30 minutes.</p> <p>In Sqlite for.NET, the same 7 millions entries were loaded in less than 3 minutes!!</p> <p>Why? What can I do to enhance the speed in ADS with the .NET provider?</p> <p>Regards .</p> <h1>EDIT</h1> <p>Thanks for your recommendations, In ADS code I mistakenly included the creation of some indexes, when I suppressed this, the loading elapsed time was 10 minutes.</p> <p>Let me add some example code and sample data (which you can multiply until getting 7 million entries). If you can find ways to enhance and optimize performance, please let me know.</p> <p>CODE FOR SYBASE ADS:</p> <pre><code> 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&lt;object, st&gt;(); 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&lt;object&gt; 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&lt;object&gt;(line.Split('|')); if (!dicTxt.ContainsKey((string)cols[5])) { stV.almlblTxt = cols[6]; stV.ciudad = cols[8]; dicTxt[cols[5]] = stV; } if (!cols[3].Equals("") &amp;&amp; cols[3].ToString().Length &gt; 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&lt;object, st&gt; 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-&gt;{1}", ex, ex.TargetSite.Name)); } finally { conn.Close(); } } } </code></pre> <p>CODE FOR SQLITE FOR .NET:</p> <pre><code>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&lt;object, st&gt;(); // 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&lt;object&gt; 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&lt;object&gt;(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("") &amp;&amp; cols[3].ToString().Length &gt; 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&lt;object, st&gt; 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-&gt;{1}", ex, ex.TargetSite.Name)); } finally { conn.Close(); } } } } </code></pre> <p>AND SOME DATA FOR TESTING (MULTIPLY UNTIL 7 MILLIONS, PLEASE INCLUDE TITLE LINE):</p> <pre><code>COD_ALMACEN_SAP|COD_ALMACEN_SAP2|CODIGO_SAP|NRO_SERIE_INICIAL|NRO_INICIO_FACTURA|COD_ALMACEN|NOMBRE_ALMACEN|CANTIDAD|CIUDAD|NOMBRE_ARTICULO </code></pre> <p>1030|8030|ADAJKIUSD66K|||16|ALMACEN DANSA PRUZ TESULARES|4|BANDA PRUZ|ADA-KI-SD66K ADAPTADOR MAIDIUM SD-66K 1030|8030|BAT-KI-BPA101|||16|ALMACEN LANTA CRUZ TESULARES|5|BANDA PRUZ|BAT-KI-BPA101 BATERIESAPS 1020|8020|TARGESA/P_PC-GC79|||17|PERRITORIAL CENTER|9|POCHASALTA|TARGESA/P_PC-GC79 TARGESA UNIVERSAL P PC GPRS Y WL 1010|8010|TARJETA/P_PC-GC79|||1014|TERRITORIES NORTH 1|5|PATPAZ|TARGESA/P_PC-GC79 TARJETA UNIVERSAL P PC GPRS Y WL 1060|8060|TARJETA/P_PC-GC79|||1095|ALMACEN SUNY|1|TRONOSAD|TARGESA/P_PC-GC79 TARGESA UNIVERSAL P PC GPRS Y WL</p> <pre><code>enter code here </code></pre> <p>Regards.</p> <p>Regards.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USvizcayno
    1. USvizcayno
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I enhance the speed of massive INSERT in ADO.NET?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I enhance the speed of massive INSERT in ADO.NET?
      1. USandora
      1. VTFavorite
    1. COCan you post your code?
      singulars
      1. POHow can I enhance the speed of massive INSERT in ADO.NET?
      1. USJuliet
    2. COHello Juliet, I posted the code.
      singulars
      1. POHow can I enhance the speed of massive INSERT in ADO.NET?
      1. USvizcayno
    3. COOne thing worth trying is to limit the size of the transaction. I have seen one example where a large change was so big that it took ages to write a huge entry the transaction log. (not posted as answer because this is hearsay).
      singulars
      1. POHow can I enhance the speed of massive INSERT in ADO.NET?
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload