Ways of storing and reading large volumes of data in a Java application under Android

Good day, dear.

After reading various articles I found information only about what is locally Android allows you to use SQLite from all possible database and no mention of others. Since SQLite has a reduced functionality, then a reasonable question arises — are there more advanced counterparts or other methods of information storage with fast access read/write?
In particular, more just interested in the topic of multi-inserts. Since they are not available in SQLite, what with the amount of data in the 20-30 thousand rows? Is the number of inserts into the database takes a very long time, and when the day of these procedures you need to perform 3-5, the usability of the application for Android is drastically reduced.
The only thing acceptable at the moment the method of storing data and chose the XML file and, accordingly, the XPath for fetching, however, such requests are significantly slower than when the same sample of the database (for example MySQL).
What are the ways of implementing functionality for reading/writing large amounts of tabular data in the Java application under Android you can recommend?
October 3rd 19 at 03:51
6 answers
October 3rd 19 at 03:53
What is the speed in SQLite?
Inserts are in one transaction?

Did you use prepared statements? It allows 1 time to parse your query and then only the data for the insert change.

PS strange decision to parse XML in cases where little SQLite.
XPath in my opinion will be much longer + can memory not be enough.

For what purpose will use the data?
If the sample conditions are necessary, then SQLite will not go away.
Generally SQLite — depositories, i.e., its speed is unlikely to be bypassed.
Only if the use of these specific data, only then has the opportunity to build a special vault for speed will avoid SQLite.

In one project used protobuf, but not in order to speed up sampling, for the purpose of prototyping — you can just generate the classes for working with data. The result remained on protobuf — speed was quite acceptable.
On account of XML. Reading from it using XPath occurs, of course, slower than SQLite, but the recording is faster. The fact is that to insert data into sql to do a bunch of inserts, it is long. But the XML I get from the remote server and save in the applications folder, and then turn directly to him, that is for saving the information on the tablet does not have to perform any inserts to the database, so it is done almost instantly, depends on the speed of the Internet, i.e., time for which will download an XML file of several megabytes. - Presley.Russel47 commented on October 3rd 19 at 03:56
Then I advise you to try protobuf — server is possible from it and to give, not XML. And stored in the file. For example, 1 object — 1 line.
Advantage with respect to XML:
1. Takes up less space. Which is good for faster data transfer.
2. Should be parsed faster than XML. Though, because its structure he "knows".

Disadvantages:
1. Worse shrinks, because the format is binary. But on the other hand not the fact that compressed XML will be easier. - whitney_Schimm commented on October 3rd 19 at 03:59
So get ready database from the server is XML, it is then more pleasant to work with. - Toy.Ko commented on October 3rd 19 at 04:02
Is it possible? There is no difference in file formats SQLite under Android and Linux?
But the main question is — is it possible on the server side to quickly generate this file, bypassing a lot of inserts? If inserts into the table to produce the same means SQLite, then this is not an option because the table must be created on the fly, so time to create it will take, again, a lot. And the server load such treatments, as I work with it about 150 devices with this app. Imagine how much he will have a day to do these in batches of insert queries, if for every 10-30 thousand and 3-5 times a day. - Presley.Russel47 commented on October 3rd 19 at 04:05
As far as I know, no one does not guarantee binary compatibility of SQLite files. - whitney_Schimm commented on October 3rd 19 at 04:08
SabMakc, on the website of SQLite write the opposite:
The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. Big-endian or little-endian, 32-bit or 64-bit does not matter. All machines use the same file format. Furthermore, the developers have pledged to keep the file format stable and backwards compatible, so newer versions of SQLite can read and write older database files.

sqlite.org/different.html#onefile - Thaddeus_Me commented on October 3rd 19 at 04:11
Thank you Maccimo for details.
Just seen a mention (on StackOverflow) that the format of the SQLite files from different manufacturers may be different.
And then she SQLite guarantees compatibility. - whitney_Schimm commented on October 3rd 19 at 04:14
October 3rd 19 at 03:55
Well, for SQLITE when inserting large amounts of play with PRAGMA.
PRAGMA synchronous=OFF — disables verification of the write data to the file system
well, the transfer of the transaction file and temporary buffer in memory (save on disk accesses (internal/memory card)
PRAGMA journal_mode=MEMORY
PRAGMA temp_store=MEMORY

Read more sqlite.org/pragma.html
October 3rd 19 at 03:57
Thank you in advance for given solutions. Will test them and update my results.
October 3rd 19 at 03:59
Ways of storing and reading large volumes of data
This is exactly what developed and the DBMS, and that they should be used.
The only thing acceptable at the moment the method of storing data and chose the XML file
It is unclear, where does all the XML. The standard was developed to standardize formats for the exchange of information, with a bias in chelovechnosti, therefore redundant, resource-intensive and is not suitable for optimal data storage. Even if you have decided to reinvent the wheel, XML is perhaps the worst of all possible options. Then drain all in a delimited file and write your own mechanisms of preservation and sampling. Will work faster than XML, and resources will require many times less. That XML, delimited files, anything — if you don't use a DBMS for "storing and retrieving large amounts of data", then with 90% probability we can assume that you are doing something wrong (unless you're Google, of course).
And the server load such treatments, as I work with it about 150 devices with this app. Imagine how much he will have a day to do these in batches of insert queries, if for every 10-30 thousand and 3-5 times a day
Most modern DBMS being installed on a average modern server can easily swallow a couple of million inserts a day. Yes there is a day, the day before yesterday I Oracle for an hour worked 15 million inserts into a table with three dozen fields, and in the test environment.
on the tablet...... 10-30 thousand and 3-5 times a day
If it's a tablet, then the data generated by the user and not some automated sensor. The question here is, what is it such can generate the user? Maybe you should reconsider the architecture of your application? Or to normalize the data to be stored? I was just just in case you ask, and then who knows... because you Have a competent architect on the project, Yes?
October 3rd 19 at 04:01
SQLite needs without tension, insert 20-30 thousand records. Don't remember the exact numbers, but to add a million records, it took me about ten seconds — minutes (though on the desktop, not on tablet). Depends on the structure of the database, etc., but — as written above — prepared statement + all the add in one transaction. You can then discuss specific numbers — how much time is spent, and how much should be.

One more thing — what fields need to search, and what not. Was experience to the table wrote a few fields (not even so for every "main" entry was added a few records in the secondary table), but was written/shall be deducted the object with all its economy as a whole. In the end, left several indexed fields on which queries are built, and all the rest hidden in the BLOB. The difference — on the order.
October 3rd 19 at 04:03

Find more questions by tags JavaDatabasesSQLAndroid