Hello,
After re-compiling my Windows application (compiled with Visual C++ 2012, 32
Bit application) with the latest SQLite version (3.8.6) I noticed a severely
degraded performance with certain queries. The new version is 10 or more times
slower than the previous build I used (3.8.4.3).
1. The table schemas are:
CREATE TABLE stack (oid INTEGER PRIMARY KEY, rtype INTEGER, toid INTEGER, state
INTEGER, color INTEGER);
CREATE INDEX idx_stack_toid_rtype ON stack(toid,rtype);
CREATE TABLE stack_elem (soid INTEGER, oid INTEGER, FOREIGN KEY(soid)
REFERENCES stack(oid) ON DELETE CASCADE);
CREATE INDEX idx_stack_elem_oid ON stack_elem(oid);
CREATE INDEX idx_stack_elem_soid ON stack_elem(soid);
CREATE UNIQUE INDEX idx_stack_elem_soid_oid ON stack_elem(soid,oid);
2. The queries to run are:
2.1
SELECT DISTINCT e.oid FROM stack_elem e
INNER JOIN stack s ON e.soid = s.oid
INNER JOIN _temptable _t ON e.oid = _t.oid
INNER JOIN _temptable _t2 ON s.toid = _t2.oid
WHERE s.state = ?1 AND s.toid <> e.oid
UNION
SELECT DISTINCT loid FROM rel_rel r
INNER JOIN stack s ON r.moid = s.toid
INNER JOIN _temptable _t ON s.toid = _t.oid WHERE s.rtype = ?3 AND s.state = ?1
ORDER BY 1
2.2
DELETE FROM stack WHERE oid IN (
SELECT stack.oid FROM stack
LEFT JOIN stack_elem ON stack_elem.soid = stack.oid AND stack_elem.oid
<> stack.toid
WHERE stack_elem.soid IS NULL)
AND stack.rtype =?1
Especially the 2.2 query has become an order of magnitude slower.
Reverting back to the 3.8.4.3 build immediately solves the problem and restores
performance for the same database.
Thanks for looking into this.
If more information or sample data is needed, let me know.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
