API or remote database?

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

API or remote database?

Post by davidchang » Sat Sep 16, 2017 10:20 pm

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

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: API or remote database?

Post by bangkok » Sun Sep 17, 2017 3:09 pm

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 ?

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: API or remote database?

Post by jacque » Sun Sep 17, 2017 4:18 pm

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

davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: API or remote database?

Post by davidchang » Sun Sep 17, 2017 6:23 pm

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...

Code: Select all

revexecuteSQL gConnectionID, "BEGIN TRANSACTION;"
--loop goes here
revexecuteSQL gConnectionID, "END TRANSACTION;" 
...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;   
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;"

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: API or remote database?

Post by jacque » Sun Sep 17, 2017 9:19 pm

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

davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: API or remote database?

Post by davidchang » Sun Sep 17, 2017 10:07 pm

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

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: API or remote database?

Post by FourthWorld » Mon Sep 18, 2017 12:48 am

For optimization you may find this thread inspiring:
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

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: API or remote database?

Post by AxWald » Mon Sep 18, 2017 10:26 am

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!
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!

davidchang
Posts: 20
Joined: Wed Jul 08, 2015 12:14 am

Re: API or remote database?

Post by davidchang » Mon Sep 18, 2017 4:11 pm

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!

Post Reply

Return to “Databases”