On 7/19/07, Veikko Mäkinen <[EMAIL PROTECTED]> wrote:
Alberto Simões wrote:
> Hi
>
> I have a file (big file with 16 000 000 lines) with records like
>
> 2 3 4
> 4 3 2
> 5 4 387
> 5 8 5473
> ...
>
> and I want to import this to an SQLite table.
> Although I can replace all this to INSERT commands very easily, I
> would like to ask first if there is any faster method.
I insert 150 000 records with a prepared statement in one single
transaction and it takes about 12 secs. I think that's fast enough :)
Mind you my table doesn't have indexes. Indexes make inserting notably
slower.
337 seconds on my Macbook Pro
lucknow:~/Data/temp punkish$ sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table test (a, b, c);
sqlite> .q
lucknow:~/Data/temp punkish$ vim test.pl
#!/usr/local/bin/perl -w
use strict; use DBI; use Benchmark;
my $t0 = new Benchmark;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1, AutoCommit => 0 }
);
my $sth = $dbh->prepare(qq{INSERT INTO test (a, b, c) VALUES (?, ?, ?)});
for (1 .. 16000000) { $sth->execute($_, $_, $_); }
$dbh->commit;
my $t1 = new Benchmark;
print "This took " . timestr( timediff($t1, $t0) ) . "\n";
lucknow:~/Data/temp punkish$ ls
test.db test.pl
lucknow:~/Data/temp punkish$ perl test.pl
This took 337 wallclock secs (279.02 usr + 15.67 sys = 294.69 CPU)
lucknow:~/Data/temp punkish$ ls -l
-rw-r--r-- 1 punkish punkish 542086144 Jul 19 11:29 test.db
-rw-r--r-- 1 punkish punkish 432 Jul 19 11:23 test.pl
lucknow:~/Data/temp punkish$ sqlite3 test.db
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> select count(*) from test;
16000000
sqlite>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
S&T Policy Fellow, National Academy of Sciences http://www.nas.edu/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
