Hi, After upgrading my OS I noticed a huge delay loading my application, I narrowed it down to my system libsqlite3 being upgraded from 3.7.4 to 3.7.7 I did some quick testing with different versions and it seems the change comes about between 3.7.5 and 3.7.6.2 The query difference is huge:
Open database successful, SQLite version 3.7.5 ... loaded 8 rows, 11300352 bytes, 0.2000 seconds ... loaded 2 rows, 160000 bytes, 0.0100 seconds ... loaded 2 rows, 3390352 bytes, 0.0500 seconds ... loaded 2 rows, 1131968 bytes, 0.0000 seconds ... loaded 2 rows, 1126736 bytes, 0.0100 seconds ... loaded 0 rows, 0 bytes, 0.0000 seconds 0.2800 seconds to load 16 geometry records from database Open database successful, SQLite version 3.7.6.2 ... loaded 8 rows, 11300352 bytes, 5.4300 seconds ... loaded 2 rows, 160000 bytes, 5.2200 seconds ... loaded 2 rows, 3390352 bytes, 5.2100 seconds ... loaded 2 rows, 1131968 bytes, 5.0000 seconds ... loaded 2 rows, 1126736 bytes, 4.9900 seconds ... loaded 0 rows, 0 bytes, 4.9800 seconds 30.8400 seconds to load 16 geometry records from database As you can see from the last row the same delay occurs even when no records are found! Queries to other tables in the db cause no problem, it's only the table that holds the majority of the data (in blob fields), example query: SELECT timestep,rank,idx,type,data_type,size,count,width,minimum,maximum,dim_factor,units,labels,data FROM geometry WHERE object_id=1 AND timestep=0 ORDER BY idx,rank; The delay occurs in the read loop on: sqlite3_step(statement); Same delay if I issue this query in the sqlite3 shell (~ 5 seconds). Table schema: CREATE TABLE geometry (id INTEGER PRIMARY KEY ASC, object_id INTEGER, timestep INTEGER, rank INTEGER, idx INTEGER, type INTEGER, data_type INTEGER, size INTEGER, count INTEGER, width INTEGER, minimum REAL, maximum REAL, dim_factor REAL, units VARCHAR(32), labels VARCHAR(2048), properties VARCHAR(2048), data BLOB, FOREIGN KEY (object_id) REFERENCES object (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (timestep) REFERENCES timestep (id) ON DELETE CASCADE ON UPDATE CASCADE); Any ideas what has changed that could cause this and if there's a way I can work around it? Thanks in advance, Owen. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
