Hello folks, I have a database of roads in a SpatiaLite database. It consist of a road-table with ~30 columns, including a BLOB-column for the geometry content. The performance critical operation is a select from a table with ~2 Mio. records, where I try to read a subset of roads from a DVD and insert it into a database on the harddrive. The select looks like this: insert into main.roads select * from external.roads where rowid in (select pkid from external.roads_way where MBRWithin(....));
Originally the road-table was defined as: create table roads (WayID UNSIGNED INTEGER NOT NULL PRIMARY KEY, .... , WAY LINESTRING); The page size is 32K, the cache size is 20000 pages. On my search for a speedup I came across the SQLite-documentation about "ROWIDs and the INTEGER PRIMARY KEY" on http://www.sqlite.org/lang_createtable.html#rowid There it says: "Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value." Applied to my database, I realized, that the data type of WayID results in a separate index for the primary key. I changed that as suggested to get a performance increase to the following creation statement: create table roads (WayID INTEGER PRIMARY KEY, .... , WAY LINESTRING); Suddenly, the database is noticeably smaller, most likely because WayID is the RowID now and needs no further index. Surprisingly, the speed for the select statement above did not increase! The time for the same select-operation doubled! How can this happen? During the test, I had the feeling that in scenario 2 the DVD drive was working like crazy. It sounded like the reading head was repositioning for every single byte. In scenario1 the DVD was read smoothly, like it was reading huge chunks. And dont worry: I tried to eliminate the influence of the drive cache by removing und reinserting the DVD before every test. I did the test several times, so the figures should be real. Anyone who can explain this or has an idea? TeDe _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
