On 28 Aug 2010, at 9:14am, Paul Sanderson wrote: > I need very fast access to various subsets of data from a table so I > am trying to write a cache for a subset of the rows. The cache must > be “sort” aware. The table is read only do I dfont need to worry about > keeping the cache upto date. > > The table itself has about 30 columns and I need to cache all of them.
I recommend that you do not try to do fancy caching until you know that the simplest way of using SQLite is too slow. SQLite does a very good job itself in doing things as fast as possible. You can spend 10 or 20 hours on devising a clever caching system only to find that it saves you 0% of your execution time in real life situations. > However, for various reasons there are implementation issues that mean > that my starting point is a query that includes the primary index (id) > and additional columns that are the columns on which the main table is > currently sorted. SQLite has an unusual optimization. If it uses a particular index to do a SELECT, then it will pull all available data straight from that index. So if all the columns the SELECT asks for are present in the index it uses, it will never need to go to the primary data storage for the table row. So once you have worked out your INDEX tuned to the specific SELECT command you're doing, add in all the columns your SELECT asks for that the INDEX doesn't already mention. You should find that this speeds up your query. However, this technique may not be worth using if your SELECT depends only on your primary key index. If you are unusure whether you have worked out the best index for your SELECT command, post them both here and we'll criticize them for your. But remember not to do 'SELECT * ...' unless you really do need all the columns. > What I want to do is a second query on the dataset selecting 100 rows > before and after the current row and place them into a second > (temporary) table. > > For an unsorted table (or rather sorted by ID) I could do the followingFor an > unsorted table (or rather sorted by ID) I could do the following > > “select * from table where ID >= (refID-100) limit 200 According to a thread last month LIMIT doesn't get optimized as well as we thought, so don't use it for speed. Doesn't matter. If you have defined 'id' as your INTEGER PRIMARY KEY column, then that's what the table is sorted by. You write that your table is pre-sorted, and has never had rows deleted. So to find the hundred rows each side of a particular row do this SELECT * FROM table WHERE id BETWEEN (currentid-100) AND (currentid+100) This should be sufficiently fast that you don't need to do anything special. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
