> create table filemap(id integer primary key, > uid integer, gid integer, mtime integer, > vol integer, > path varchar(1024)); > > It has no indices built yet. > > I'm adding quite a lot of records to it using a perl script which > generates SQL like this: > > begin; > insert into filemap values(null, 1, 1, 1111, 0, "/path/to/file"); > ... 9999 more like this ... > commit; > ... repeat above ... > > The uid, gid and mtime fields vary obviously, but there are very many > paths for each uid/gid pair. The idea is that I need to be able to > say `show me all the files owned by UID x on volume y?', and we have > enough data that awk can't hack it. > > before doing this I've done a > > pragma synchronous=off; > > All this is just being piped into sqlite3 (I know I should use the > proper interface, but it's a very quick & dirty hack). > > I have about 16,000,000 records. When adding them it goes really > quickly for about the first 1,000,000 (using the big transaction > trick made it much faster, as did the synchronous=off thing). But > then it slows down dramatically, perhaps by a factor of 100 or 1000 > or something. I've actually left it running, but I'm not convinced > it will have done all 16 million by Monday. > > I have not looked at what the program is doing in the sense of system > calls or any more detailed profiling. It is clear that disk activity > falls right off when it becomes slow. > > Am I doing anything obviously stupid here? I suspect I must be.
The batch insert you describe ought to be pretty fast since you're only appending data. This is a guess - instead of inserting NULL for the INTEGER PRIMARY KEY column try assigning an increasing number for each new row. This would avoid an OP_NewRowid per insert, which I would not think to be slow, but it's worth trying. You might also experiment with the sqlite3 import facility which should be slightly faster. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
