ODBC to SQL tables disk space error and performance

[bac mac]

I am accessing SQL tables from FMP 12 via ODBC and this has mostly been working fine, except for some performance issues that crop up now and then, e.g., Not Responding appearing at the top of the window for a few minutes now and then, but then it always clears and continues to function.

 

I don't fully understand exactly how the connection operates or the best practices.

 

For some of the connections, I just obtain a total count of the records and the names of all of the fields in the remote table. The remote table may contain 2-40 fields and about 80 records to 150 million records. Since the last time I added one of the remote tables to FMP, I keep getting an error in a FileMaker Pro popup window: "There is not enough room on the disk to complete this operation. Make more room on the disk, then try again." And the only option is to click OK. When I do, it goes away, but then reappears about 3 minutes later.  This last table has 50 million records and about 30 fields.  Unlike the other tables, I could not identify a unique field or a unique pair of fields. After trial and error, I ended up selecting all 30 fields to get it the table to appear.

 

1. Does the fact that I selected 30 fields when adding the table through the ODBC result in a performance hit affecting memory and/or disk space?  (And, I have not had luck yet having a simple unique key added on the remote.)

 

2. What happens when I access SQL data through ODBC?  Is the entire remote table brought into FMP memory?  Regardless of what subset of the data I may actually be using?  Would it be significantly more efficient if I did have a single unique key field in the remote table to use?

 

3. Any other advice about optimizing use of SQL data by FMP 12?  Would there be significant benefits if we switched to FMP 13?

 

Thank you,

Bruce

[Matthew F]

I think its a good idea to have unique record IDs in the SQL table that you can use to create a relationship to your filemaker tables.  I'm a SQL dummy, but here's a variety of suggestions on to do this:  http://www.deep-data-mining.com/2012/06/five-ways-of-creating-unique-record.html