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

in Germany we would say, you are barking at the wrong tree, Christian is the one person who enables us to take a one-step-approach of importing JSON and, if needed, creating data structure in FM just like you did in your MariaDB-with-prepared-statement-SQL-method

JSON.Parse is more useful if you pick cherries out of JSON-response from API-calls...

What if you were to use the Data API to insert all the json data into filemaker? Maybe that is faster?

Yeah but that improvement cane from switching to Java/JDBC. :sunglasses:

That's why I don't write most of that code by hand. The script I posted above, about 80% was written for me. Much the same way other languages leverage tools to write code for them.

I hear you but in this case (and most like it for me), I would potentially have other software clients so I try to avoid an FMP only approach such as with a plug-in when writing utility logic.

I’m wondering how long Christian’s corresponding MBS JSON code would take. I’m sure it would be blazingly faster than FMP.

Thanks.

that's what I tested: 15 seconds including download, creating table and fields and importing 5600 rows of data

That’s FMP proprietary. I like to keep my options open. :sunglasses:

It’s all working fine now with Java/JDBC.

I could wrap this code in a microservice and allow callers to specify their database. Same code should work with any JDBC compliant DB.

No vendor lock-in.

The only piece that makes it faster is eliminating the JSON from being run through more than once. Effectively I can do the same thing with a single custom function and eliminate evaluating the JSON more than once. It won't be 2 seconds, but I think it would be around 1 or 2 mins. Which for a single daily event, is just fine for most use case.

I posted mainly to make sure when others read this thread, they realize there are ways to make the code more efficient. Sadly, too many people like dump in inefficient code, and then complain that FM is slow. 5000 records can be created pretty quickly...it all depends on what you are doing with it, and how long you 'need' it to take. That will determine the approach.

2 Likes

just tested MBS JSON.Import on this only doing one day, inserting into preexisting table, took 1368 milliseconds which should be fast enough even being under 2 seconds :slight_smile:

Good to know. Did that also update the index?

  • Others have already highlighted one important optimization point in the original script. I won't go over it again, but I will say I agree with it.

  • Related to what @jormond said about the use of inefficient code: The cases that bum me out are the ones where devs and users are living with the unnecessary slow-down without knowing any better. It is good to have some threads like these around which can shine some light on how to work with the grain of the product, as opposed to against it.

  • I haven't ever had a compelling need to use an external tool for JSON work in FMP, but since people are mentioning MBS, I will mention that I recently watched over someone else's shoulder while they used the JSON Import feature from MBS, and I have to say it was very impressive to see what it did. It left me with even more appreciation for what @MonkeybreadSoftware has been making possible for all these years.

1 Like

Nice. That's FMP only though which probably doesn't matter to most here.

Would you post your script?

If I time just the looping portion (I'm not trying to make this a performance contest as MBS is already extremely fast), I get:

Time to add JSON records to DB from JSON: 0.976867 seconds.


Another consideration would be the dates. Dates in a feed like this (and most I see) are ISO format. Of course you can write additional code to handle that in FMP, but with MariaDB, ISO dates are understood automatically with the data as imported.

how can I test that one? Since that is a table automatically created by the MBS function all fields are set to index:none, automatical creation if needed..

FileMaker has an interesting feature. A date formated as 2020+06+18, or similar, FileMaker recognizes. So often, I don't need to do any more than substitute the "-" for a "+".

1 Like

it is the same script I posted this morning, only with the URL for the recent day and loading data directly into a $variable

...and what is playing to your advantage here is recognition of datatypes which has to be adjusted afterwards

for me it was just proof-of-concept: can it be done within FM with sufficient performance, just tweaked a bit more putting the pause to 0.1 second which got speed to 264ms now, should be OK

Go to Layout [ “CoronaData” (CoronaData) ; Animation: None ]
Go to Record/Request/Page [ Last ]
Set Variable [ $StartTS ; Value: Get( CurrentTimeUTCMilliseconds ) ]
Set Variable [ $jsonfromURL ; Value: "" ]
Insert from URL [ Select ; With dialog: Off ; $jsonfromURL ; "https://covidtracking.com/api/states" ]
Set Variable [ $Row0006TS ; Value: Get( CurrentTimeUTCMilliseconds ) ]
Set Variable [ $r ; Value: MBS("[JSON.Import]"; $jsonfromURL; ""; "Import") ]

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" ]
  End Loop

Set Variable [ $StoppTS ; Value: Get( CurrentTimeUTCMilliseconds ) ]
End If

1 Like

why do you need to pause the script .1 seconds every loop iteration?

this is no loop in the usual way, the function JSON.Import.Status looks for the state of the import, so if the internal process is finished it doesn't say "working" anymore and import is done

http://www.mbsplugins.eu/JSONImport.shtml
has the explanation...

The (inefficient?) code approach I used was from the FMP 19 help examples. :innocent:

My “inefficient” approach was from an example in the FMP help itself. :frowning:

Can you post your app?