Performance tuning when working with huge data sets

[eljefejb]

Hello,

 

I am working on a solution for my boss.  He wants to be able to take all of the data from a large SQL Server 7 database, put it into one FileMaker file (for ease of viewing) and be able to load it all onto a USB stick so he can take it to go.  Ordinarily, ODBC shadow tables would be the way to go, but we have no external access (from outside the building) to our network/databases for security reasons.

 

I have written a PHP script which iterates over each table in the database, retrieves all the records, and uses the FM PHP API to stuff it all into like tables in a FileMaker database.  It works, but in my test case, I have only a handful of tables over 50,000 records, one of which is 2 million+.  I am processing the result set of each query in batches of 10,000 records at a time, and the script took 8 hours to complete!  I can't imagine how long it might take when running against the full database!

 

Anyone have any ideas on how to improve the performance?  As near as I can tell, the bottleneck is stuffing the records into FileMaker.  I think the query to the SQL database is fast.

 

Thanks,

 

Jeff

[webko]

I wouldn't do it that way - FileMaker's web stuff is all XML based, and the web side is single-threaded. It's 'wordy' and inefficient for very large data sets.

 

Can you create ESS connections in a file, and then import the tables to real FileMaker tables? Off line, the ESS tables won't work, but the imported data should be available.

[eljefejb]

That was my original thought, but I was trying to automate the whole process to make a "one-click" solution.  There are almost 375 tables in the original database, and I don't want to have to go through them one at a time.  I did that just to set up the ESS table instances in the relationships graph, and it took forever!    I copied/pasted all of those table instances in the tables tab to create local FileMaker copies, then I tried creating a script that would iterate over all the tables and import records from the ESS shadow tables.  The problem I ran into was that the script only remembers the most recent table when specifying the import options in the script step dialog.  There isn't a way (so far as I can tell) to programatically set the table to import to and specify the import options ("matching names") at the same time.  As such, the script kept trying to stuff the data into the same table, even though I was switching layouts for each.

 

At this point, though, after seeing how long it takes to retrieve/store all the data via the PHP API, it might be much easier to go through the instances by hand!

 

Sorry if that rambled or didn't make sense . . . got a bit of a cold today. 

[webko]

Next question - do they really need all 375 tables for an on-the-go version?

 

And you would need 375 scripts (or script steps) for the import process - re-using the same script doesn't work, as you have found out... Tedious to set up, but should only need to be done once. I'd start with 10 of the most used tables and test that before expanding to all tables.

[dansmith65]

Here is a product that can help you automate the setup and the import process: http://www.goya.com.au/refreshfm

 

On a mac, I think it can create all the table occurrences and layouts for you, but if not, I wrote some AutoHotKey scripts to do it on Windows: https://github.com/dansmith65/FileMaker-Automation

[eljefejb]

Next question - do they really need all 375 tables for an on-the-go version?

 

And you would need 375 scripts (or script steps) for the import process - re-using the same script doesn't work, as you have found out... Tedious to set up, but should only need to be done once. I'd start with 10 of the most used tables and test that before expanding to all tables.

 

That's the way I'm heading.  With the test database, it finishes the import in 9 minutes, instead of 8 hours!  Much faster this way!  Guess I'll endure the tedium of setting it all up.  Not an elegant solution, but effective!  Thanks for the advice.