Have you tried running this with script debugger turned on…? which step in the script causes the bottleneck…? is it the flushing of the joins, or the find or…?
Why not just do a direct ODBC import instead? It has to be faster than going to an ESS table and making FileMaker think through all those rows.
1 of 1 people found this helpful
Or you could do an ExecuteSQL without ever going to the ESS layout and then loop through the results and save into your local table. But I still bet the direct ODBC import would be faster.
As of yet, I haven't run into anything different...however, the first thing I would do is see if you can get rid of the "Show All Records" step.
Possibly make a relationship to the WebOrders table and use the Last () function to pick up the ID from the last WebOrder...then:
Enter Find Mode
Go to Layout [ "WebOrders" (WebOrders) ]
Perform Find ( ) //using the ID from the Last Function
Set Field [ Preferences::LastWebOrder ; $LastWebOrderNo ]
At least that way your are avoiding moving a bunch of data from the ODBC source into your local cache. Especially because you Flush Cache earlier in the script.
1 of 1 people found this helpful
+1 on using direct ODBC import instead of ESS (unless there is other functionality that you have in the dB that ESS fits). In the few solutions where we interconnect with a SQL source, direct import has been significantly faster than ESS every time in both FMP11 and FMP12.
We have only retained ESS in one instance where we are showing the user layouts that are directly to linked to SQL data.
Having said this, a couple of things to check with your scripting.
- cart1_order layout: this layout should be a 'blank' one and set to form view only.
- order_id field in your sql table: it is a key field in the sql and thus fully indexed?
- Is the ESS table occurence by chance based on a raw sql table that will continually grow? If so, suggest that your ESS table occurence in FMP be based off of a view in the SQL that would filter the records in some way. Perhaps it would only show orders created in the last week/month/etc. This assumes you have some control over the SQL dB or a good working relationship with the SQL administrators.
I agree I have been doing this for years on the Mac and use the drivers from Actual technologies to do a direct ODBC import from a shopping cart and it works fine. Not sure what advantage ESS gives you in this context.
Many thanks for all of your helpful replies.
With my developer colleague, Bill Fowler, on hand to assist we tackled the whole issue afresh.
Bill discovered that the Actual Technologies driver on the FMS 12 server was the trial version not the licensed version as it was only letting us view the first three records of a newly found set. With the licensed version properly installed and using some blank layouts for our finds ( > Previous Last Web Order Number) we've managed to get our web order downloads working again with no pauses whatsoever.
The worst part of the conversion has been the matching up field names for the several import steps from the "over normalized" set of mySQL data tables. That frustration has provided us with yet more evidence that the original web site designers were not quite smart enough to provide us with the best SQL design for an e-commerce web site.
That trial license has bit me a few times as well. Generally speaking ESS works better in 12 than 11. The SQL buffer is larger, and it will handle larger tables. However, it still is limited. I can import much bigger tables using the same drivers through the straight ODBC import.