I do 12.3 million inserts with a single commit in approx 1 minute using prepared statements and parameters in accordance with the "Getting the best performance out of SQLite" section of the SQLite.NET.chm help file, which is available here:
http://system.data.sqlite.org/index.html/doc/trunk/Doc/SQLite.NET.chm?mimetype=application/x-chm Below is my actual code. It's C#, but you should have no trouble doing the equivalent in VB.NET. Good Luck, -Bill ---START--- using System; using System.IO; using System.Data.SQLite; using System.Diagnostics; namespace Spurs { class Program { static void Main(string[] args) { /* * Spur database has the following structure: * * CREATE TABLE [spurs] ( * [FundamentalFreq] FLOAT, * [SpurFreq] FLOAT, * [SpurdBm] FLOAT); * * * .CSV file is 12.3 million entries like this: * 1075.100000,2150.449990,-49.192 * 1075.100000,2150.449990,-49.278 * 1886.900000,1887.197613,-54.701 * * */ // Timer stuff Stopwatch stopWatch = new Stopwatch(); TimeSpan insertTime, commitTime; const string timePattern = "{0:00}:{1:00}:{2:00}.{3:00}"; string elapsedTime; // File names string fileName = "raw_data.csv"; // Input file name. string dbFileName = "raw_data.db"; // Database file (output file) name. // Input file read-loop variables StreamReader myReader; // A reader to read the file. int lineCount = 0; // Total number of lines in input file. int reportInterval = 100000; // Update progress every reportInterval lines. int intervalCount = 0; // Number of lines since last update report. string line = ""; // Holds 1 line from file. string[] lineCSV = new string[3]; // Holds the values from each line. // Database variables string connectionString; // Database connection string. string commandString; // Database SQL command string. connectionString = String.Format("Data Source={0}", dbFileName); commandString = "INSERT INTO spurs VALUES (?, ?, ?)"; SQLiteConnection connection; // Active database connection. SQLiteTransaction transaction; // Active database transaction. SQLiteCommand SQLCommand; // Active database command. SQLiteParameter @FundamentalFreq; // UUT Test frequency (MHz). SQLiteParameter @SpurFreq; // Frequency of largest spur (MHz). SQLiteParameter @SpurAmp; // Amplitude of largest spur (dBm). @FundamentalFreq = new SQLiteParameter(); @SpurFreq = new SQLiteParameter(); @SpurAmp = new SQLiteParameter(); // Process .csv file. System.Console.WriteLine("Reading file: " + fileName); using (connection = new SQLiteConnection(connectionString)) { using (SQLCommand = new SQLiteCommand(connection)) { SQLCommand.CommandText = commandString; SQLCommand.Parameters.Add(@FundamentalFreq); SQLCommand.Parameters.Add(@SpurFreq); SQLCommand.Parameters.Add(@SpurAmp); connection.Open(); using (transaction = connection.BeginTransaction()) { using (myReader = new StreamReader(fileName)) { stopWatch.Start(); while ((line = myReader.ReadLine()) != null) { // Get values from one line in the .csv file. lineCSV = line.Split(new char[] { ',' }); @FundamentalFreq.Value = double.Parse(lineCSV[0]); @SpurFreq.Value = double.Parse(lineCSV[1]); @SpurAmp.Value = double.Parse(lineCSV[2]); // Insert them into the database. SQLCommand.ExecuteNonQuery(); // Print progress every reportInterval lines. lineCount++; intervalCount++; if (intervalCount == reportInterval) { System.Console.Write("Processing line " + lineCount + '\r'); intervalCount = 0; } }// End while. stopWatch.Stop(); insertTime = stopWatch.Elapsed; stopWatch.Reset(); System.Console.Write("Processed " + lineCount + " lines.\r\n"); }// End myReader. System.Console.WriteLine("\nCommiting data..."); stopWatch.Start(); transaction.Commit(); stopWatch.Stop(); commitTime = stopWatch.Elapsed; }// End transaction. }// End SQLCommand. }// End connection. elapsedTime = String.Format(timePattern, insertTime.Hours, insertTime.Minutes, insertTime.Seconds, insertTime.Milliseconds / 10); System.Console.WriteLine("Insert time: " + elapsedTime); elapsedTime = String.Format(timePattern, commitTime.Hours, commitTime.Minutes, commitTime.Seconds, commitTime.Milliseconds / 10); System.Console.WriteLine("Commit time: " + elapsedTime); } } } ---END--- -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Trahair Sent: Monday, April 07, 2014 2:33 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to speed up a bulk import Hi I am using SQLite running under VB.net in a import routine of a series of csv files, but it seems to be slow, and I don't mean microseconds, I mean minutes. A typical INSERT might be: INSERT INTO AllSales (Source, MachineName, Location, UserPIN, TariffName, CustomerID, DateOfSale, TimeOfSale, TicketGroup, Action, HasBeenVerified, EPOSRecNo, CrossingName, QuantitySold) VALUES ('Terminal', 'HH098016', 'Test Company', '6992', 'Test Company', '20140326-135946', '2014-03-26', '13:59:46', 'Test Company', 'Logout: 1#867.90$2#$3#$<Skpr=<DPu1=0<DPu2=0<DP1=419', 0, 0, '', 0)" The import only does inserts, it doesn't do any other SQL-type transaction during this sequence, and I use the same VB.net code as I use for SQL Server (sorry to mention that here!), but SQL Server imports the same data twice as fast. Would having a field index or key help? I tried doing a Transaction Commit, but it seems to fail after so many thousand inserts due to 'database being locked'. I test it on my Windows 8 machine, but the database would have to be on the customer's Windows 7 desktop or server. Any suggestions welcome. Thank you. Jonathan Trahair _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
