Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

can sqlite load 10M data into the memory

Tags:

If it's possible, how ?

I want to speed up the readings (not writings) in sqlite

Thanks

like image 634
zjm1126 Avatar asked Feb 02 '10 08:02

zjm1126


People also ask

How much load can SQLite handle?

SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit of 1 MB for the results.

Can SQLite handle large data?

SQLite supports databases up to 281 terabytes in size, assuming you can find a disk drive and filesystem that will support 281-terabyte files. Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database.

Does SQLite load database into memory?

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory. The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:".

How much memory does SQLite use?

SQLite will refuse to allocate more than about 2GB of memory at one go. (In common use, SQLite seldom ever allocates more than about 8KB of memory at a time so a 2GB allocation limit is not a burden.) So the 64-bit size parameter provides lots of headroom for detecting overflows.


1 Answers

Yes.

SQLite loads data into memory in pages. The default page size is 1024 bytes. You can change the page size using this command.

PRAGMA page_size = bytes; 

But you have to do this before you create the database (or in 3.5.8, you can change it by running a VACUUM after issuing the new page size pragma).

The cache is based on number of pages. The default cache size is 2000 pages. You can change it using this command.

PRAGMA cache_size = Number-of-pages; 

So to store 10MB of data in memory, either increase the page size to 5120 or increase the cache size to 10000.

More info on pragmas is here.

like image 158
Samuel Neff Avatar answered Nov 15 '22 17:11

Samuel Neff