JSON too slow in loop (or loops too slow in general)

anon45965781 · June 17, 2020, 6:40pm

Using only FMP functionality, I was importing all the historical data (about 5,700 JSON array values) from here:

https://covidtracking.com/api/states/daily

I went away from the computer for an hour and only 400 or so had been imported! This is a 2019 iMac with 128 GB RAM, i9 processor, etc.

Fortunately, I can just import the historical data and then only add the daily data for each day, but it had been so long since I'd attempted anything in a FMP loop that I had forgotten what a show-stopper that approach can be.

Using FMP to import these data would take about 14 hours... I recall there are some performance workarounds, but I didn't take the time to find those again and I really don't want to have to change my workflow to accommodate FMP.

If I didn't already have the historical data, I would have to, at this point, write yet another JDBC program + Java JSON parsing and have that program stuff the data into FMP which would probably take about a minute or two (plus the time to write the code -- probably 30 mins).

If anyone is interested, I can post the CSV I converted from the JSON at the URL above.

(Spoiler alert: The same logic in Java to parse the JSON and to write all data to Maria DB takes less than 3 milliseconds)

anon45965781 · June 17, 2020, 7:43pm

Update-- Decided not to use FMP for this project.

Since the GROUP BY queries were nearly hanging FMP, I moved the project to MariaDB using a data tool which has all the graphics tools built in. That tool can also import JSON.

jormond · June 17, 2020, 9:03pm

How were you creating the records? I just tested, and while not earth-shattering numbers, I created 400 records in less than 2 mins.

jormond · June 17, 2020, 9:04pm

What are you doing that you need group by?

anon45965781 · June 17, 2020, 9:08pm

In a loop with JsonGetElement().

Did you write the JSON for the URL above?

Here's my script:

Get Daily US Data -- all states

Insert from URL [ Select ; With dialog: Off ; Target: $json ; "https://covidtracking.com/api/states" ]
Go to Layout [ “covid_history” (covid_history) ; Animation: None ]

make sure we haven't already downloaded today's data

Set Error Capture [ On ]
Set Variable [ $today ; Value: JSONGetElement($json; "[0].date") ]
Enter Find Mode [ Pause: Off ]
Set Field [ covid::thedate ; $today ]
Perform Find []

Set Variable [ $loopCounter ; Value: 0 ]
Set Variable [ $numValues ; Value: // get total number of JSON elements. Pick one that's not likely to have dup in text PatternCount ( $json ; "inICUCumulative" ) ]

If [ Get (FoundCount) = 0 ]
Loop
# new record
New Record/Request
Set Field [ covid_history::hospitalizedCurrently ; $today ]
Set Field [ covid_history::date ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".date") ]
Set Field [ covid_history::state ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".state") ]
Set Field [ covid_history::positive ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".positive") ]
Set Field [ covid_history::negative ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".negative") ]
Set Field [ covid_history::pending ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".pending") ]
Set Field [ covid_history::hospitalizedCurrently ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".hospitalizedCurrently") ]
Set Field [ covid_history::hospitalizedCumulative ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".hospitalizedCumulative") ]
Set Field [ covid_history::inIcuCurrently ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".inIcuCurrently") ]
Set Field [ covid_history::inIcuCumulative ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".inIcuCumulative") ]
Set Field [ covid_history::onVentilatorCurrently ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".onVentilatorCurrently") ]
Set Field [ covid_history::onVentilatorCumulative ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".onVentilatorCumulative") ]
Set Field [ covid_history::dataQualityGrade ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".dataQualityGrade") ]
Set Field [ covid_history::lastUpdateEt ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".lastUpdateEt") ]
Set Field [ covid_history::dateModified ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".dateModified") ]
Set Field [ covid_history::checkTimeEt ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".checkTimeEt") ]
Set Field [ covid_history::death ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".death") ]
Set Field [ covid_history::hospitalized ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".hospitalized") ]
Set Field [ covid_history::dateChecked ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".dateChecked") ]
Set Field [ covid_history::totalTestsViral ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".totalTestsViral") ]
Set Field [ covid_history::positiveTestsViral ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".positiveTestsViral") ]
Set Field [ covid_history::negativeTestsViral ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".negativeTestsViral") ]
Set Field [ covid_history::positiveCasesViral ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".positiveCasesViral") ]
Set Field [ covid_history::fips ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".fips") ]
Set Field [ covid_history::positiveIncrease ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".positiveIncrease") ]
Set Field [ covid_history::negativeIncrease ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".negativeIncrease") ]
Set Field [ covid_history::total ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".total") ]
Set Field [ covid_history::totalTestResults ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".totalTestResults") ]
Set Field [ covid_history::totalTestResultsIncrease ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".totalTestResultsIncrease") ]
Set Field [ covid_history::posNeg ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".posNeg") ]
Set Field [ covid_history::deathIncrease ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".deathIncrease") ]
Set Field [ covid_history::hospitalizedIncrease ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".hospitalizedIncrease") ]
Set Field [ covid_history::hash ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".hash") ]
Set Field [ covid_history::commercialScore ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".commercialScore") ]
Set Field [ covid_history::negativeRegularScore ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".negativeRegularScore") ]
Set Field [ covid_history::negativeScore ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".negativeScore") ]
Set Field [ covid_history::positiveScore ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".positiveScore") ]
Set Field [ covid_history::score ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".score") ]
Set Field [ covid_history::grade ; JSONGetElement ( $json ; "[" & $loopCounter & "]" & ".grade") ]
#
Set Variable [ $loopCounter ; Value: $loopCounter +1 ]
# are we done?
Exit Loop If [ $loopCounter > $numValues ]
End Loop
Else
Show Custom Dialog [ "Already Imported" ; "Data for today already imported." ]
End If

anon45965781 · June 17, 2020, 9:08pm

Doing sums by state and other fields.

jormond · June 17, 2020, 9:30pm

I did a couple alterations to my first test, which created 600 records in about 3 mins. This version created 300+ records in about 5 secs.

# 
Freeze Window
# 
Go to Layout [ “globals” (globals) ; Animation: None ]
Set Variable [ $json ; Value: Substitute ( globals::json ; [ "[" ; "" ] ; [ "]" ; "" ] ; [ "},{" ; "}¶{" ] ) ] 
Set Variable [ $rows ; Value: ValueCount ( $json ) //GetAsNumber ( RightValues ( JSONListKeys ( globals::json ; "" ) ; 1 ) ) ] 
# 
Loop
	# 
	Set Variable [ $i ; Value: $i + 1 ] 
	Set Variable [ $currentJSON ; Value: GetValue ( $json ; $i ) // JSONGetElement ( $json ; $i ) ] 
	Set Field [ globals::create_key ; "" ] 
	# 
	# 
	# 
	Set Variable [ $checkTimeEt ; Value: JSONGetElement ( $currentJSON ; "checkTimeEt" ) ] 
	Set Variable [ $commercialScore ; Value: JSONGetElement ( $currentJSON ; "commercialScore" ) ] 
	Set Variable [ $dataQualityGrade ; Value: JSONGetElement ( $currentJSON ; "dataQualityGrade" ) ] 
	Set Variable [ $date ; Value: JSONGetElement ( $currentJSON ; "date" ) ] 
	Set Variable [ $dateChecked ; Value: JSONGetElement ( $currentJSON ; "dateChecked" ) ] 
	Set Variable [ $dateModified ; Value: JSONGetElement ( $currentJSON ; "dateModified" ) ] 
	Set Variable [ $death ; Value: JSONGetElement ( $currentJSON ; "death" ) ] 
	Set Variable [ $deathIncrease ; Value: JSONGetElement ( $currentJSON ; "deathIncrease" ) ] 
	Set Variable [ $fips ; Value: JSONGetElement ( $currentJSON ; "fips" ) ] 
	Set Variable [ $grade ; Value: JSONGetElement ( $currentJSON ; "grade" ) ] 
	Set Variable [ $hash ; Value: JSONGetElement ( $currentJSON ; "hash" ) ] 
	Set Variable [ $hospitalized ; Value: JSONGetElement ( $currentJSON ; "hospitalized" ) ] 
	Set Variable [ $hospitalizedCumulative ; Value: JSONGetElement ( $currentJSON ; "hospitalizedCumulative" ) ] 
	Set Variable [ $hospitalizedCurrently ; Value: JSONGetElement ( $currentJSON ; "hospitalizedCurrently" ) ] 
	Set Variable [ $hospitalizedIncrease ; Value: JSONGetElement ( $currentJSON ; "hosptializedIncrease" ) ] 
	Set Variable [ $inIcuCumulative ; Value: JSONGetElement ( $currentJSON ; "inIcuCumulative" ) ] 
	Set Variable [ $inIcuCurrently ; Value: JSONGetElement ( $currentJSON ; "inIcuCurrently" ) ] 
	Set Variable [ $lastUpdateEt ; Value: JSONGetElement ( $currentJSON ; "lastUpdateEt" ) ] 
	Set Variable [ $negative ; Value: JSONGetElement ( $currentJSON ; "negative" ) ] 
	Set Variable [ $negativeIncrease ; Value: JSONGetElement ( $currentJSON ; "negativeIncrease" ) ] 
	Set Variable [ $negativeRegularScore ; Value: JSONGetElement ( $currentJSON ; "negativeRegularScore" ) ] 
	Set Variable [ $negativeScore ; Value: JSONGetElement ( $currentJSON ; "negativeScore" ) ] 
	Set Variable [ $negativeTestsViral ; Value: JSONGetElement ( $currentJSON ; "negativeTestsViral" ) ] 
	Set Variable [ $onVentilatorCumulative ; Value: JSONGetElement ( $currentJSON ; "onVentilatorCumulative" ) ] 
	Set Variable [ $onVentilatorCurrently ; Value: JSONGetElement ( $currentJSON ; "onVentilatorCurrently" ) ] 
	Set Variable [ $pending ; Value: JSONGetElement ( $currentJSON ; "pending" ) ] 
	Set Variable [ $posNeg ; Value: JSONGetElement ( $currentJSON ; "posNeg" ) ] 
	Set Variable [ $positive ; Value: JSONGetElement ( $currentJSON ; "positive" ) ] 
	Set Variable [ $positiveCasesViral ; Value: JSONGetElement ( $currentJSON ; "positiveCasesViral" ) ] 
	Set Variable [ $positiveIncrease ; Value: JSONGetElement ( $currentJSON ; "positiveIncrease" ) ] 
	Set Variable [ $positiveScore ; Value: JSONGetElement ( $currentJSON ; "positiveScore" ) ] 
	Set Variable [ $positiveTestsViral ; Value: JSONGetElement ( $currentJSON ; "positiveTestsViral" ) ] 
	Set Variable [ $recovered ; Value: JSONGetElement ( $currentJSON ; "recovered" ) ] 
	Set Variable [ $score ; Value: JSONGetElement ( $currentJSON ; "score" ) ] 
	Set Variable [ $state ; Value: JSONGetElement ( $currentJSON ; "state" ) ] 
	Set Variable [ $total ; Value: JSONGetElement ( $currentJSON ; "total" ) ] 
	Set Variable [ $totalTestResults ; Value: JSONGetElement ( $currentJSON ; "totalTestResults" ) ] 
	Set Variable [ $totalTestResultsIncrease ; Value: JSONGetElement ( $currentJSON ; "totalTestResultsIncrease" ) ] 
	Set Variable [ $totalTestsViral ; Value: JSONGetElement ( $currentJSON ; "totalTestsViral" ) ] 
	# 
	# 
	Set Field [ covid_data::checkTimeEt ; $checkTimeEt ] 
	Set Field [ covid_data::commercialScore ; $commercialScore ] 
	Set Field [ covid_data::dataQualityGrade ; $dataQualityGrade ] 
	Set Field [ covid_data::date ; $date ] 
	Set Field [ covid_data::dateChecked ; $dateChecked ] 
	Set Field [ covid_data::dateModified ; $dateModified ] 
	Set Field [ covid_data::death ; $death ] 
	Set Field [ covid_data::deathIncrease ; $deathIncrease ] 
	Set Field [ covid_data::fips ; $fips ] 
	Set Field [ covid_data::grade ; $grade ] 
	Set Field [ covid_data::hash ; $hash ] 
	Set Field [ covid_data::hospitalized ; $hospitalized ] 
	Set Field [ covid_data::hospitalizedCumulative ; $hospitalizedCumulative ] 
	Set Field [ covid_data::hospitalizedCurrently ; $hospitalizedCurrently ] 
	Set Field [ covid_data::hospitalizedIncrease ; $hospitalizedIncrease ] 
	Set Field [ covid_data::inIcuCumulative ; $inIcuCumulative ] 
	Set Field [ covid_data::inIcuCurrently ; $inIcuCurrently ] 
	Set Field [ covid_data::lastUpdateEt ; $lastUpdateEt ] 
	Set Field [ covid_data::negative ; $negative ] 
	Set Field [ covid_data::negativeIncrease ; $negativeIncrease ] 
	Set Field [ covid_data::negativeRegularScore ; $negativeRegularScore ] 
	Set Field [ covid_data::negativeScore ; $negativeScore ] 
	Set Field [ covid_data::negativeTestsViral ; $negativeTestsViral ] 
	Set Field [ covid_data::onVentilatorCumulative ; $onVentilatorCumulative ] 
	Set Field [ covid_data::onVentilatorCurrently ; $onVentilatorCurrently ] 
	Set Field [ covid_data::pending ; $pending ] 
	Set Field [ covid_data::posNeg ; $posNeg ] 
	Set Field [ covid_data::positive ; $positive ] 
	Set Field [ covid_data::positiveCasesViral ; $positiveCasesViral ] 
	Set Field [ covid_data::positiveIncrease ; $positiveIncrease ] 
	Set Field [ covid_data::positiveScore ; $positiveScore ] 
	Set Field [ covid_data::positiveTestsViral ; $positiveTestsViral ] 
	Set Field [ covid_data::recovered ; $recovered ] 
	Set Field [ covid_data::score ; $score ] 
	Set Field [ covid_data::state ; $state ] 
	Set Field [ covid_data::total ; $total ] 
	Set Field [ covid_data::totalTestResults ; $totalTestResults ] 
	Set Field [ covid_data::totalTestResultsIncrease ; $totalTestResultsIncrease ] 
	Set Field [ covid_data::totalTestsViral ; $totalTestsViral ] 
	# 
	Exit Loop If [ $i ≥ $rows ] 
	# 
	If [ not Mod ( $i ; 100 ) ] 
		Commit Records/Requests [ With dialog: Off ] 
	End If
	# 
	# 
End Loop
# 
Commit Records/Requests [ With dialog: Off ] 
# 
# 
Exit Script [ Text Result:    ] 
# 
# 
#

jormond · June 17, 2020, 9:44pm

I also think, that if you were to reduce the list of elements as you go through it, it will get faster. I was able to create all 5000 records in about 3-4 mins. At the same time, rewriting the variable every time may not actually make it faster.

jormond · June 17, 2020, 9:47pm

Just use a summary field, and don't put it on the layout. Grab that value as needed. With only 5000 records, it should be instant.

jwilling · June 17, 2020, 9:57pm

I got similar results to @jormond. It's not blazing, but i'm seeing it write 100 records every 10-15 seconds.

bdbd · June 18, 2020, 3:22am

You will gain an important speed improvement if you first isolate the loopCounter structure before you extract the structure's elements. FileMaker's JSONGetElement function parses the JSON from the beginning every time it is called.

As for deleting the structure every time you process it as @jormond suggests… I don't know. I suppose it depends on the difference between the time it takes FileMaker to write the new JSON to memory and the time saved barely parsing the JSON.

harvest · June 18, 2020, 7:35am

so now the 15sec cheat sheet with MBS?!

thanks to Christian you can just implement this or take one of his sample files and be done in 15 seconds including creating the table and the fields for the results. URL can afterwards be shortened to only get the data set of the current day.

One ceveat though: it's only imported as text, so field types have to be set afterwards

I shortened the Script for readability but is's all here

http://www.mbsplugins.eu/JSONImport.shtml

Go to Layout [ “CoronaData” (CoronaData) ; Animation: None ]
Insert from URL [ Select ; With dialog: Off ; CoronaData::gJsonImport ; "https://covidtracking.com/api/states/daily" ]

setup progress bar

[...]
Pause/Resume Script [ Duration (seconds): ,1 ]

read JSON from file

Set Variable [ $text ; Value: CoronaData::gJsonImport ]
Set Variable [ $r ; Value: MBS("[JSON.Import]"; $text; ""; "Import") ]
Set Variable [ $text ; Value: "" ]

Now loop until complete

If [ MBS("[IsError]") = 0 ]
Set Variable [ $total ; Value: MBS("[JSON.Import.Total]") ]
Loop
Set Variable [ $todo ; Value: MBS("[JSON.Import.Todo] ") ]
[...]
Pause/Resume Script [ Duration (seconds): 1 ]

    Set Variable [ $s ; Value: MBS("[JSON.Import.Status]") ]

Exit Loop If [ $s ≠ "Working" ]
If [ MBS("[ProgressDialog.GetCancel]") = 1 ]
Set Variable [ $r ; Value: MBS("[ProgressDialog.Hide]") ]
Set Variable [ $r ; Value: MBS("[JSON.Import.Cancel]") ]
Exit Script [ Text Result: ]
End If
End Loop

Set Variable [ $r ; Value: MBS("[ProgressDialog.Hide]") ]
Set Variable [ $StoppTS ; Value: Get( CurrentTimeUTCMilliseconds ) ]
Show Custom Dialog [ "JSON Import" ; $r & " " & $s ]
End If

Cleanup prorgress bar

Set Variable [ $r ; Value: MBS("[ProgressDialog.Hide]") ]

MonkeybreadSoftware · June 18, 2020, 7:56am

You can speed up a lot with JSON when you use JSON.Parse with our MBS FileMaker Plugin.

Parse once and query often.

harvest · June 18, 2020, 7:58am

...but I really wanted to try that JSON.Import thing
and that is blazingly fast in this situation...
I use JSON.Parse a lot too and it is really great stuff

anon45965781 · June 18, 2020, 11:52am

I like MBS a lot, but don't want to be dependent on it or need to pay hundreds of dollars for a server version. And, I'm no longer doing this particular project using FileMaker.

Take a look at the video output below where I did the import into MariaDB using Java/JDBC and a (compiled) prepared statement -- from the URL directly.

The video shows, even with outputting progress to the screen (for the video), which takes some time, the entire JSON process, including reading from the URL and INSERTing all historical data into MariaDB takes >>> 2 seconds <<< .

Covid-Java-Import-to-MariaDB.mp4.zip (273.6 KB)

If I just time the loop that parses the JSON and then writes the data, currently about 5,600 rows and 39 fields, to MariaDB, that takes less than 3 milliseconds.

Now that these data are in MariaDB, all SQL queries happen in about 1/10th of a second or less.

Since it doesn't use any proprietary SQL, this code is (mostly, if not all) database independent. Therefore, by just changing the login user/password, the same code should work with FileMaker, SQL Server, Oracle, etc.

harvest · June 18, 2020, 12:05pm

it's all about kaizen

MonkeybreadSoftware · June 18, 2020, 1:58pm

If you have a specific use cases where our license prices don’t fit, you can always ask us for something special.

anon45965781 · June 18, 2020, 2:10pm

Well, maybe not....You would think after four releases, FMP 16, FMP 17, FMP 18, and now, FMP 19, that JSON handling would be improved. If it has been, I don't see it or recall any feature improvement announcements. JSON in FMP still feels half done (at most) to me.

anon45965781 · June 18, 2020, 2:15pm

I'm sure this works well, but with 39 fields, any code like this is a pain in the script-only workspace. (need an expert mode in FMP!).

harvest · June 18, 2020, 2:29pm

I actually wasn't thinking of FM and its JSON handling but of your thread coming from hours of processing down to two seconds in your last post

Topic		Replies	Views
Found set being in a sorted state causes performance degradation in Loop sequences of scripts Questions scripting , loop-performance , design-limitation	97	1216	January 15, 2020
Slow WAN import vs ? <your suggestion goes here> Questions	3	285	November 27, 2019
Unresponsive replace operation - extremely slow performance Questions performance , filemaker	8	194	January 12, 2024
Creating an FM db from an Excel binder Heads-Up! fields , print-area , preparation	14	590	November 16, 2020
Need Help with Optimizing FileMaker Script Performance Questions	2	93	December 27, 2024