API or remote database?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
API or remote database?
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
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
Re: API or remote database?
What is slow ? To parse and write the JSON data into the local SQLite DB ?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?
Speaking about "500 iterations", could you say how many records and columns you get in the SQLite ?
Last question : "slow" how ? How many seconds ?
-
- VIP Livecode Opensource Backer
- Posts: 7233
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: API or remote database?
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.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: API or remote database?
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.
Code: Select all
revexecuteSQL gConnectionID, "BEGIN TRANSACTION;"
--loop goes here
revexecuteSQL gConnectionID, "END TRANSACTION;"
Code: Select all
revexecuteSQL gConnectionID, "BEGIN TRANSACTION;
repeat with x = 1 to the number of lines of (the keys of tAPIArray)
--SQL insert statement
put "INSERT into dbname (col1, col1, .... col20) values ('" & tAPIArray[x][element1] &"'," & tAPIArray[x][element2] &","....& tAPIArray[x][element20] &"')" into tSQL
revExecuteSQL gConnectionID, tSQL
end repeat
revexecuteSQL gConnectionID, "END TRANSACTION;"
-
- VIP Livecode Opensource Backer
- Posts: 7233
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: API or remote database?
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.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: API or remote database?
Thanks for the suggestion though, i'll use repeat for each in other code snippets for speed.
-
- VIP Livecode Opensource Backer
- Posts: 9834
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: API or remote database?
For optimization you may find this thread inspiring:
viewtopic.php?f=8&t=24945
viewtopic.php?f=8&t=24945
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: API or remote database?
Hi,
have you read this already? Obviously:
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!
have you read this already? Obviously:
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!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;"
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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
-
- Posts: 20
- Joined: Wed Jul 08, 2015 12:14 am
Re: API or remote database?
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!