AnsweredAssumed Answered

Slow ODBC import into local table

Question asked by andyw88 on Sep 18, 2017
Latest reply on Nov 14, 2017 by TSGal

Slow performance importing from an ESS table into a local table in the same database.

 

Product and version: Filemaker Pro Advanced 15 and 16  32bit (all versions as far as I know)

OS and version: Windows 10 64 bit (all recent)

Hardware: PC

ODBC Driver: 32bit MySQL ODBC 5.3 Unicode Driver

 

Description:

We have some cloud based databases and use ODBC connections to enable us to analyse and process this data in Filemaker. One method we often use is to download a copy of the external data into a local table with all the same field names and then work with the data offline. We will have an external table e.g. externaldata, and a local table e.g. LocalData. A typical script will look something like:

Go to Layout [externaldata]

Show All Records

Go to Layout [LocalData]

Show All Records

Delete All Records

Import Records [ from "this file", from table: externaldata, all fields, matching names ]

 

Some of these tables have 350,000 records.

 

We found the import very very slow with time calculating as up to 2 days. But we have found a workaround that if we first export the data to a flat file (tab or csv) directly from the externaldata layout, it speeds up the subsequent import over 100 times faster.

 

Would be interested to know why this is. It looks like a bug in the way ESS tables work in Filemaker.

Outcomes