On 18 Jul 2009, at 8:22am, Brian Dantes wrote: > I have a largish DB around 1GB in size. There is a table with 5 > million rows > in it that has a 3-key index on it. This database file is fragmented > -- to > what degree I'm not sure. > > Using sqlite 3.6.14, dropping and recreating the index under WinXP > Pro (on a > local disk) with no other activity takes about 90 seconds. With the > same > database on Red Hat Linux 64-bit with no other activity and a local > disk, > the index recreation takes almost 30 *minutes*. The activity is > completely > I/O bound. If I vacuum the database, the Windows time drops to 70 > seconds, > and the Linux time drops to 7 minutes.
All the long timings you list suggest something is wrong somewhere, just as you thought. When you describe doing the same activity on the two platforms, are you building the database separately on each platform, or are you copying the built database from one platform to the other before recreating the index ? I'm trying to figure out whether the problem might be with the database file. > Conversely, with this same table and index starting from empty, if I > start > inserting rows with the index in place, on Windows the insertion > speed drops > dramatically after about 100-200K rows and takes about 6-7 *hours* to > complete. On Linux 64-bit, the same experiment takes less than an > hour to > complete and the insertion speed seems fairly constant. Are all these INSERT commands done as one transaction (using BEGIN and COMMIT) or separate ones ? If the separate, use a transaction instead. Are you doing these INSERT commands in your own application (presumably compiled both for Windows and Linux) ? If so, try writing them all to a text file, then using the sqlite3 command-line tool to execute the text file. This will take all your own programming and use of APIs out of consideration and say definitely whether the problem is with SQLite or not. You can have the SQLite command-line tool itself prepare the text file for you: see the '.dump' command in http://www.sqlite.org/sqlite.html Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
