Yes, there are 3 indexes being created. For 2 indexes, the data is in order. For the 3rd index, the data is not in order. I can understand how that will cause extra seeking. It's a good point.
I just ran another test after downloading the latest SQLite version, changed the page size to 4K on the main database (the 230MB file), kept pragma synchronous=off, and changed the commit interval back to 5 seconds (even though it shouldn't be doing anything because of synchronous=off). This improved performance from 190 minutes to 106 minutes, and disk I/O and CPU overlap was fairly steady throughout, with the CPU waiting for I/O about 30% of the time. That's higher than the sequential file case because of the extra seeking needed to build the trees/indexes, and it's less than double the best-case sequential time, so I'm okay with that. With the slower times I reported before, there would be long periods of time, like 10-15 seconds, where the CPU was completely idle while data was being written at relatively slow speeds, like < 1000 I/O's per second. I'm assuming these were mostly random I/O, hence the slow rate. With the lastest changes, it doesn't do that. I'll post again after figuring out which of the changes improved the performance. Thanks for the clues! Jim On 2/4/09, Gerry Snyder <mesmerizer...@gmail.com> wrote: > Jim Wilcoxson wrote: >> I am creating an SQLite database via Python, and trying to understand >> some performance issues. > > Wild guess--are you creating an index (or indices) on data being entered > out of order? > > HTH, > > > Gerry _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
