Using PHP to output MySQL query as SQLite

229

We have an iOS app which must download a large amount of user data from a remote server (in JSON format) and then insert this data into the local SQLite database. Because there is so much data, the insertion process takes more than 5 mins to complete, which is unacceptable. The process must be less than 30 seconds.

We have identified a potential solution: get the remote server to store the user's data in an SQLite database (on the remote machine). This database is compressed and then downloaded by the app. Therefore, the app will not have to conduct any data insertion, making the process much faster.

Our remote server is running PHP/MySQL.

My question:

  • What is the fastest and most efficient way to create the SQLite database on the remote server?
  • Is it possible to output a MySQL query directly into an SQLite table?
  • Is it possible to create a temporary MySQL database and then convert it to SQLite format?
  • Or do we have to take the MySQL query output and insert each record into the SQLite database?

Any suggestions would be greatly appreciated.

613

Answer

Solution:

I think it's better to have a look at why the insert process is taking 5 minutes.

If you don't do it properly in SQLite, every insert statement will be executed in a separate transaction. This is known to be very slow. It's much better to do all the inserts in one single SQLite transaction. That should make the insert process really fast, even if you are talking about a lot of records.

In pseudo code, you will need to the following:

SQLite.exec('begin transaction');
for (item in dataToInsert) {
    SQLite.exec('insert into table values ( ... )');
}
SQLite.exec('end transaction');

The same applies by the way if you want to create the SQLite database from PHP.

You can read a lot about this here: Improve INSERT-per-second performance of SQLite?

People are also looking for solutions to the problem: php - 403 Error - Wordpress link not working when permalink setting is set to 'post name'

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.