In your Exit Loop if statement you're missing the $ sign in .... $counter > listCount.
It may put you in a long loop until it craps out.
I see this is a screenshot so not a typo.
You may also try an import instead.
That was a typo - sorry. I've corrected the original post.
FWIW I set up a test file and can create 30,000 records in 82 seconds.
Further testing of the script showed that the bottleneck is in the script step:
Set Field [ Prospects::id_customer ; GetValue ( $recordsIDs ; $counter ) ]
You can test this yourself by disabling or deleting that script step and running the script.
A much faster method is to import the found set of customer records into the prospects table and then Replace Field Contents on the Prospects::id_prospectList field. Using this method, I can create 30,000 records in 7 seconds.
You seems forget to add
step before loop.
I tested with and without the Freeze Window before the loop and found no difference in this case.
His result is too slow, so I guessed there are some unstored calc or summary field in layout.
Stored calc also can be the reason, in the case freezing window will have no effect for it.
Yes, import is better than create loop, if able.
Thanks for your replies everyone!
davidhead I had no idea that importing could be done across tables!!! This, for me, is a huge discovery. Thank you for this little gem! I guessed the bottle neck was using GetValue but just had no idea what to use as an alternative.
HammerOz Thanks for that link. That's a really useful article - one I haven't read yet.
Just for reference, the Prospects table is a join table so very narrow and no calcs whatsoever. There were a few fields for date created and modified but those aren't necessary anyway so I'll remove them.
I think I'm going to go down the 'import' route and run it using PSOS so I'm hoping to see significant improvements.
Are both tables in same file?
There are other gotcha's with PSoS and multi-file solutions. Otherwise it's not relevant.
Also Freeze Window step is not available on Server and you will get error 3 ( command unavailable ) in the Server Log.
If you want to keep it clean observe the compatibility.
Just to add to others comments, indexing settings in the table's fields may slow things down a bit too.
Because it takes time to index.
The tables are in the same file. I haven't ventured into multi-file solutions yet - the Separation Model frightens me!!!
Thanks for the heads up on Freeze Window. I always observe compatibility so probably would have spotted it but thanks all the same
Good call on indexing as well. This is another thing I need to do more research on. Since I start hosting my files on FM Server I'm realising that there are many areas of FM that I now have to learn more about and optimize my files accordingly.
I appreciate your input.
I wonder if any of these are tested with PSOS (perform script on server) or just scheduled script. I find looping to be fast on scheduled script, but often I loop after an import. So, who knows?!