SQLite vs Text file Database - Size comparision?

Question

I am going to convert a text file in the SQLite db form; I am concerned about these points because giving any effort to write code for it:

• Will both text file or its corresponding sqlite db be of same size?
• SQLite would take less space than text file?
• Or text file db is the one with lowest space?

Answer 1

Some things to keep in mind:

(NOTE about this answer: Files here references to internal/external storage, not SharedPrefs)

SQL:

• Databases have overheads, which does take up size
• If the database or a table goes corrupt, all data is lost(how bad this is depends on your app. Losing several thousand pictures: bad. Losing deletion log: not very bad)
• Databases can be compressed(see this)
• You can split up data into different tables, if you have issues with ID(or whatever way you identify row X), meaning one database can have several tables for each object where object X have identification conflicts with object Y. That basically means you can keep everything in one file, and still avoid conflicts with names. (Read more at the bottom of the answer)

Files:

• Every single file has to be defined as its own separate file, which takes up space(name of the file)
• You cannot store all attributes in one file without having to set up an advanced reader that determines the different types of data. If you don't do that, and have one file for each attribute, you will use a lot of space.
• Reading thousands of lines can go slow, especially if you have several(say 100+) very big files

The OS uses space for each file, excluding the content. The name of the file for an instance, that takes up space. But something to keep in mind is you can keep all the data of an app in a single file. If you have an app where objects of two different types may have naming issues, you create a new database.

---

Naming conflicts

Say you have two objects, object X and Y.

Scenario 1'

Object X stores two variables. The file names are(x and y are in this case coordinates):

x.txt
y.txt

But in a later version, object Y comes in with the same two files. So you have to assign an ID to object X and Y:

0-x.txt
0-y.txt

Every file uses 3 chars(7 total, including extension) on the name alone. This grows bigger the more complex the setup is. See scenario 2

But saving in the database, you get the row with ID 0 and find column X or Y. You do not have to worry about the file name.

Further, if every object saves a lot of files, the reference to load or save each file will take up a lot of space. And that affects your APK file, and slowly pushes you up towards the 50 MB limit(google play limit).

You can create universal methods, but you can do the same with SQL and save space in the APK file. But compared to text files, SQL does save some space in terms of name.

Note though, that if you save 2-3 files(just to take a number) those few bytes going to names aren't going to matter

It is when you start saving hundreds of files, long names to avoid naming conflicts, that is when SQL saves you space. And if the table gets too big, you can compress it. You can zip text files to maybe save some space, but with one-liner files, there is not much to save.

Scenario 2

Object X and Y has three children each.

Every child has 3 variables it saves to the file system. If there was only one object with 3 children, it could have saved it like this

[id][variable name].txt

But because there is another parent with 3 children(of the same type, and they save the same files) the object's children who get saved last are the ones that stay saved. The first 3 get overwritten.

So you have to add the parent ID:

[parent ID][child ID][variable name].txt

And keep in mind, these examples are focused on a few objects. The amount of space saved is low, but when you save hundreds, if not thousands of files, that is when you start to save space.

Now, if you create a table, you can store your main objects(X and Y in this case). Then, you can either create the first table in a way that makes it recognisable whether the object is the parent or child, or you can create a second table. The second table have two ID values; One to identify the parent and one to identify the child. So if you want to find all the children of object 436, you simply write this query:

SELECT * FROM childrentable WHERE `parent_id`='436'

And that will give you all the attributes for all the children with object 436 as its parent.

And everything is stored in the Cursor when returned.

If you were to do the same with a file, this line(where Saver is the file saving and loading class):

Saver.load("0-436-file_name", context);

It is, of course, possible to use a for-loop to cycle the children ID(the 0 at the start), but you would also have to save how many children there are: You cannot get the files as easily, so you have to store values about thee amount of objects and the objects children.

This meaning you have to save more values in more files to be able to get the files you saved in the first place. And this is a really hard way to do things. A database would help you not have to write files to keep track of how many files you saved. The database would return [x] results on each query. So if object 436 has no children, SQL returns 0 rows. But in files, you would have to save 0 as the amount of children. Guessing file names lead to I/O exceptions.

---

Answer 2

"Hardware is cheap" - I'd strongly recommend not worrying about size differences, which will likely be insignificant anyway, and instead pick whichever solution best meets the rest of your needs. A text file can work just fine for simple projects, but a database has many more features that can help you organize, backup, and query your data much more efficiently and robustly.

For a more in-depth look at the pros and cons of both options, check out: database vs. flat files

Answer 3

I would expect the text file to be smaller as it has no overhead: all the things a Database gives you have a cost in terms of space.

It sounds like space is the only thing that matters to you, and that you expect to change the contents of the text file often (you call it a 'text file db'). Please note that there is no such thing as a 'text file db'. Reading and writing to it will be very slow compared to a proper db (such as SQLite). Adding different record types (Tables in a db) will complicate your like and I wouldn't want to try to maintain any sort of referential links between record types in a text file.

Hope that helps -