Which is the fastest way to retrive all items in SQLite?

Question

I am programming on windows, I store my infors into sqlite. However I find to get all items is a bit slow.

I am using the following way:

select * from XXX;

retriving all items in 1.7MB SQLite DB takes about 200-400ms. It is too slow. Can Anyone Help!
Many Thanks!

Thanks for your answers!
I have to do a complex operation on the data, so everytime, when I open the app, I need to read all information from DB.

Answer 1

You could possibly speed it up slightly by selecting only those columns you want, but otherwise nothing will beat an unordered select with no where clause for getting all the data.

Other than that a faster disk/cpu is your only option.

What type of hardware is this on?

Answer 2

I would try the following:

1. Vacuum your database by running the "vacuum" command
2. SQLite starts with a default cache size of 2000 pages. (Run the command "pragma cache_size" to be sure. Each page is 512 bytes, so it looks like you have about 1 MByte of cache, which is not quite enough to contain your database. Increase your cache size by running "pragma default_cache_size=4000". That should get you 2 Mbytes cache, which is enough to get your entire database into the cache. You can run these pragma commands from the sqlite3 command line, or through your program as if it were another query.
3. Add an index to your table on the field you are ordering with.