Menu
I have a sqlite database where all the primary keys are GUIDs. Currently they are stored as fixed length strings but I want to store them as blobs because it simplifies the code for storing and retrieving data. I converted part of the database and everything is functioning as expected. However, I'm not sure if I will run into performance issues.
For example, would a statement like this be faster on strings than blobs?
SELECT * FROM table1 t1, table2 t2 WHERE t1.id = t2.parent_id
My intuitions says no, but that doesn't really mean anything.
979
(10) Does SQLite support a BLOB type? SQLite allows you to store BLOB data in any column, even columns that are declared to hold some other type. BLOBs can even be used as PRIMARY KEYs.
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.
A million records is no problem. Implementation Limits For SQLite says: The theoretical maximum number of rows in a table is 2^64 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 140 terabytes will be reached first.
BLOBs can only be stored using the data portion of a key/data pair. They are supported only for Btree, Hash, and Heap databases, and only so long as the database is not configured for checksums, encryption, duplicate records, or duplicate sorted records.
The best way to find out is to run the queries against a profiler/SQLite's timer. Setup a test and run the query 1000 times with string, then 1000 times as a blob. Winner is the fastest.
Intuition is one thing, hard data is another.
90
I think AIEE and if I were you I'd be storing GUIDs in a pair of Integer types on SQLITE (SQLITE INTEGER is 64 bits).
However in this case blob might actually work better.
LFSR is right, profile it.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
