哪个是最快的方式来检索SQLite中的所有项目？ [英] Which is the fastest way to retrieve all items in SQLite?

问题描述

我在Windows上编程，我把我的信息存储到sqlite。
但我发现所有的项目都有点慢。

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

我使用以下方式：

select * from XXX;

检索1.7MB SQLite DB中的所有项目大约需要200-400ms。
它太慢了。任何人都可以帮助！

很多谢谢！

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

感谢您的回答！

我必须在数据，所以每次，当我打开应用程序，我需要从DB读取所有信息。

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.

推荐答案

我会尝试以下方式：

1. 运行 vacuum 命令
清空数据库

2. SQLite的默认缓存大小为2000页。 （运行命令 pragma cache_size 来确保每个页面都是512字节，所以看起来你有大约1 MB的缓存，这不足以包含你的数据库。 size通过运行 pragma default_cache_size = 4000 ，这应该可以得到2 Mbytes的缓存，这足以让你的整个数据库进入缓存你可以从sqlite3命令行运行这些pragma命令，


3. 在您要订购的字段上向表格中添加索引。

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.

这篇关于哪个是最快的方式来检索SQLite中的所有项目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！