Re: [sqlite] How to speed up a bulk import

[Drago, William @ MWG - NARDAEAST]

rvalCount = 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#$

Re: [sqlite] How to speed up a bulk import

[RSmith]

On 2014/04/07 20:33, J Trahair wrote:

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: 

Re: [sqlite] How to speed up a bulk import

[Simon Slavin]

On 7 Apr 2014, at 7:33pm, J Trahair  wrote:

> Would having a field index or key help?

Adding more indexes and keys make searches faster, at the expense of making the 
original INSERT slower.

As a test, instead of executing the INSERT commands, write the commands 
themselves to a text file.  Then add a BEGIN and the beginning and an END at 
the end.  Then use the shell tool to .read that file.  Is is faster or slower 
than your VB code ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up a bulk import

[Petite Abeille]

On Apr 7, 2014, at 8:33 PM, J Trahair  wrote:

> Any suggestions welcome. Thank you.

One word: transaction.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to speed up a bulk import

[J Trahair]

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: