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: 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
Well, that would be the problem then, start an explicit Transaction, then do all the inserts, then commit it, you should reach
speeds that would make MSSQL seem very last-gen.
BEGIN TRANSACTION;
Insert..
Insert..
Insert..
Insert..
COMMIT;
, 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.
You are very welcome to mention it, we don't hate MSSQL, or any of the others, they are all tools and achieve a purpose in life,
sometimes if you use the wrong tool for the wrong purpose we might point that out, but there is no shame in using any tool!
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'.
Actually, more Indices would make the insert query go slower (only marginally
though).
Good luck!
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
