On 8 Jan 2015, at 10:04am, Максим Гумеров <mgume...@gmail.com> wrote:
> When I put it on HDD and try to make 10000 queries (extracting values for > 10000 different keys) with some additional processing of extracted values, > it takes about 4 seconds on my PC on any run except the first, with maybe > half of that time being the "additional processing" mentioned; even when I > perform every query 3 times in a row (making it 30000 queries), this time > does not change. On the first run, thought, 10000 queries take about 30 > seconds! This suggests that you are filling up a cache, especially if your BLOBs are large. In terms of overall time, 10000 queries in 2 seconds is 5000 queries a second. If you are using a rotating hard disk then this is not unexpected, given that your disk probably rotates at 5,400 rpm, that gives an average latency of 5.55ms /per read/. > CREATE TABLE global ( > > [key] VARCHAR (1024), > value BLOB, > level INTEGER NOT NULL, > original_name VARCHAR (1024), > id INTEGER PRIMARY KEY AUTOINCREMENT, > parent_id REFERENCES global (id) > > ); SQLite will interpret "VARCHAR (1024)" as TEXT and will not truncate. I'm just warning you. > value is a BLOB (and for 50% keys is just NULL) Is there a difference between a key being present in your database with NULL value, and the key not being present ? > And there are 3 separate indices: by level, key, and parent_id. An index which is only on level would probably be pointless. I suspect you're more likely to want to have that index include the key, too. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
