LiveCode Forums

Hi everyone,

I have a scraper that gets new data daily from my source and packages that data in an API.With that API, I have been able to parse the JSON and load the data to a SQLite database in the mobile app whenever the user fires up the app. However, it is slower than I would like. I use JSONtoARRAY and then loop the array one at a time into the database (over 500 iterations right now). Is there a better way to filter and consume the API JSON data in app? Would a remote SQLite database connection be more efficient? Or would copying a remote SQLite database into the local database be faster?

Thanks,
David

davidchang wrote:I have a scraper that gets new data daily from my source and packages that data in an API.With that API, I have been able to parse the JSON and load the data to a SQLite database in the mobile app whenever the user fires up the app. However, it is slower than I would like. I use JSONtoARRAY and then loop the array one at a time into the database (over 500 iterations right now). Is there a better way to filter and consume the API JSON data in app? Would a remote SQLite database connection be more efficient? Or would copying a remote SQLite database into the local database be faster?

What is slow ? To parse and write the JSON data into the local SQLite DB ?

Speaking about "500 iterations", could you say how many records and columns you get in the SQLite ?

Last question : "slow" how ? How many seconds ?

What form of "repeat" do you use in the loop? Anything other than "repeat for each" will slow down if there are more than a few records.

Slow, in this case, is about 15 seconds to execute the calling of the API, parsing the API and importing to a local SQLite, with the parsing and importing causing the bottleneck. 500 iterations is 500 records of 20 variables each. I've been able to significantly reduce the execute time down to around 5 seconds (but hoping to reduce further) by using... ...outside of the loop. As for the repeat, I am using a repeat for not a repeat each. I'm not sure how to use the repeat for each in this case. I have 500 keys (x) with 20 elements each key.

Yeah, normally keeping your own counter and using "for each" would speed things up, but in this case it looks like your array keys are numbers already so that wouldn't help.

Thanks for the suggestion though, i'll use repeat for each in other code snippets for speed.

For optimization you may find this thread inspiring:
viewtopic.php?f=8&t=24945

Hi,

have you read this already? Obviously:

davidchang wrote:

Code: Select all

   
revexecuteSQL gConnectionID, "BEGIN TRANSACTION;   
repeat with x = 1 to the number of lines of (the keys of tAPIArray)
      put "INSERT into dbname (col1, col1, .... col20) values [...] into tSQL
      revExecuteSQL gConnectionID, tSQL    
 end repeat     
 revexecuteSQL gConnectionID, "END TRANSACTION;"

With this code, you still have to send many, many small queries. With a local SQLite, not this much of a problem. When accessing a db over the web, it means a heavy slowdown!

I usually construct a "INSERT INTO table VALUES (var_a1, var_a2, var_a3), (var_b1, var_b2, var_b3);" query in LC, and "fire & forget" it to the db with a single revExecuteSQL. So I can even save the "BEGIN" and "COMMIT" statements, SQLite does this for me anyways.
This has proven to be fast & reliable, and, since it's very easy to verify the resulting SQL, easy to debug.

Have fun!

Thanks AxWald, I have seen that document. At this point, any more gains would probably need to come from the parsing of the json data, the SQLite load is fairly quick. I am using jsontoarray, is there a faster method, fastjson? I can live with the overall execution time now (around 5 seconds) but interested to see if more optimization is possible in the json parse. Thanks for all the suggestion everyone!