3

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

I am using the following way:

select * from XXX;

Retrieving 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.

buddemat
  • 4,552
  • 14
  • 29
  • 49
sxingfeng
  • 971
  • 4
  • 15
  • 32
  • In a typical application, you won't be retrieving all items very often. Maybe you should provide more details about why you need to do this. – Matthew Flaschen Jun 01 '10 at 03:54
  • 2
    Can you avoid getting all rows? – lc. Jun 01 '10 at 03:54
  • 2
    and why is 200ms too slow? how often are you reading ALL rows of your database? perhaps you should look into a different approach. – Blindy Jun 01 '10 at 04:01
  • 1.7 million rows in less than a second? That sounds really fast, if you ask me. That's probably only a few times slower than `std::map`, though you'd have to benchmark to see. – Joey Adams Jun 01 '10 at 04:02
  • 1
    1.7 MB not 1.7 million rows :-) – James Anderson Jun 01 '10 at 04:15
  • If the complex operation is expressable in SQLite, do it in SQLite. Databases are data-oriented pieces of software, their job is to process data, don't try to outsmart a database in its niche. But I'm not so sure whether this general rule applies to SQLite as well :). – Pieter Jun 01 '10 at 05:50
  • What are you doing with the results after you retrieve them from the DB? Writing them to a file? Storing them in an array? – dan04 Jun 01 '10 at 13:23
  • @sxingfeng - Can you give me the schema of the data, and approximately how many rows you have in your table, and the sample code that you used to get this 200-400 ms measurement? – Jay Godse Jun 02 '10 at 16:52

2 Answers2

4

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.
Jay Godse
  • 15,163
  • 16
  • 84
  • 131
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?

James Anderson
  • 27,109
  • 7
  • 50
  • 78