Dear list, I am writing small program for large-ish databases involving multiple (nested) comparisons of time references (REAL) against time intervals (also REAL) stored as max and min points (i.e. "is this time point within the intervals I have stored in table X?")
At what point, in terms of database size or otherwise, would it be sensible (if at all) to move into using the R*Tree module for the time reference comparisons? I have tried generating som data to do comparisons, but I get lots of warning messages about constrain violations, so I am not so sure that the index is working correctly. Anyway, with 30 000 rows and my possibly faulty index it seems that merging negates any positive effects of R*Tree. This is my setup: CREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX,); CREATE TABLE maintab (id INTEGER PRIMARY KEY AUTOINCREMENT, lab TEXT); CREATE TABLE merged (id INTEGER PRIMARY KEY, lab TEXT, minX REAL, maxX REAL); -- Data inserted into the database by a bunch of insert into maintab values ( 1 ,'mmm'); insert into demo_index values ( 1 , (random()+0.0)/(random()/1000), (random()+0.0)/(random()/1000)); ..... (30 000 of them) Typically select * from maintab m, demo_index d where d.maxX >= 100.0 and d.minX <= 100.0 and d.id = m.id; runns in > CPU Time: user 0.074044 sys 0.035214 and (where "merged" is a table with everything in it) select * from merged where maxX >= 100.0 and minX <= 100.0; in > CPU Time: user 0.053880 sys 0.031010 Does this seem reasonable? Am I doing something stupid? /Fredrik -- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it." _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
