Apologies if I should have found an answer to this by searching or
being less naive about SQL!
I'm using 3.3.17 built by me with gcc (3.4.3? can't check just now as
machine is at work - it's the one Sun ship anyway) on a SPARC Solaris
10u3 box.
I don't have any significant experience of SQL databases so I may be
doing something trivially wrong.
I have a table defined by
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.
Thanks
--tim
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
