[sqlite] index creation 30x slower on Mac than on Linux
We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. In order to make our lookups fast, we're creating an index table in the database. On Linux, creating the table takes about 200 seconds. On the Mac the same operation takes 6,400 seconds. Here's the "CREATE INDEX" statement we're using: CREATE INDEX Full_Closure_Index ON Full_Closure(Client,Dependency) Why is the Mac 30 times slower than Linux? They're both on the same network, accessing the same network drive location, so neither has the advantage of local disk access. Any suggestions on how to improve the performance here would be very welcome. FYI, we're using different version of SQLite on each architecture: v 3.6.23.1 on Linux and v 3.7.7 on the Mac. Thanks very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] primary key with bulk insert (UNION SELECT)
I ended up modifying the statement to add NULL to the ROWID column for each row. SQLite silently replaces the NULL with an automatically generated row id. This works even in older versions, so this was the solution I ended up using. Thanks, Alan -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies Sent: Friday, October 12, 2012 8:42 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] primary key with bulk insert (UNION SELECT) On 11 October 2012 15:07, Alan Frankel <alan.fran...@mathworks.com> wrote: > I have a table that uses an autogenerated id as primary key. I want to do > bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I > specify an id for each row: > > sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name > sqlite> VARCHAR(25), distance REAL); insert into CelestialObject > sqlite> select 'Betelguese' as name, 200 as distance UNION SELECT > sqlite> 'Procyon', 500; > Error: table CelestialObject has 3 columns but 2 values were supplied > > If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY > AUTOINCREMENT") when I create the table, the error is the same. Can anyone > tell me whether there's a way to use a bulk insert without specifying an id > for each row? insert into CelestialObject( name, distance ) select 'Betelguese' as name, 200 as distance UNION SELECT 'Procyon', 500; > > Thanks, > Alan > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] primary key with bulk insert (UNION SELECT)
I have a table that uses an autogenerated id as primary key. I want to do bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I specify an id for each row: sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name VARCHAR(25), distance REAL); sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as distance UNION SELECT 'Procyon', 500; Error: table CelestialObject has 3 columns but 2 values were supplied If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY AUTOINCREMENT") when I create the table, the error is the same. Can anyone tell me whether there's a way to use a bulk insert without specifying an id for each row? Thanks, Alan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users