In the past month we have been tasked to repair / rewrite a solution for an Oil & Gas Service client. The FileMaker solution had been previously developed by another FileMaker Development firm. Unfortunately this previous firm has failed to have an effective and highly standardized design and development document, a.k.a. "Design & Naming Conventions". Failure in not having such a best practice for all developers in there group lead to difficulty for there people to follow and understand what was built, why and how it all connected. It further confused and complicated matters around the use of Execute SQL within the FileMaker solution. I could go on about how much of a headache and how badly designed this solution was but the main reason for this post is not to trash the previous developer. The main reason for this post is to point out an extremely bad choice in programming that lead there client to come knock on Terra Software's door.
The previously developer (Team) seemed to think that the use of the Execute SQL command in FileMaker was going to be a great and effcient way to have FileMaker process data. Truly this was a mistake and frankly a missunderstanding of why, how and when to use the Execute SQL. In short DO NOT Execute SQL Commands from within FileMaker back onto itself to process data,it taxes the FileMaker client and if hosted it can bog down the server if a large data set is in play. Use FileMaker and proper relationship structures, along with effective and intelligent scripting and or calculations to call data from the same or other source tables within the file. Execute SQL is truly meant for calling / performing external SQL activity, not within the FileMaker solution.
Having said what I just stated there will be some out there that will think this is wrong. True there can be very specific reasons under properly formatted and specific conditions to use Execute SQL to call / perform Execute SQL on other FileMaker files, but calling back on the same file is a big mistake and resource depletor.
This one mistake by the previous development house has costed the client an undisclosed amount of extra expenditures and 1 year of headaches. Added to that the clients solution is actually not very complicated at least from our perspective, having removed the Execute SQL and rebuilt the solution from the ground up they are gaining real-time processing with no delays.