On 10/17/2011 11:05 AM, Owen Kaluza wrote:
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
0.2800 seconds to load 16 geometry records from database
Open database successful, SQLite version 3.7.6.2
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;
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);
Likely you are hitting a problem causing SQLite to create an automatic
index for this type of query. Fixed here:
http://www.sqlite.org/src/ci/27c65d4d9c?sbs=0
Updating to 3.7.8 should fix it.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
