1 2 Previous Next 19 Replies Latest reply on Apr 3, 2017 1:06 PM by fmpdude

    Fastest way to "put data" inside filemaker

    Vincent_L

      Hi

       

      For years I'm thinking that the fastest way to put data in filemaker is the import function. By putting data I'm referring to update or create new records. But maybe it's not true (anymore), as 360's MirrorSync3 seems to suggest.

       

      MirrorSync3 can allow filemaker clustering, which would imply ultra-fast updates, which seems not possible to me given Imports, Filemaker SQL.

      It seems to use JDBC, but to me JDBC is just a layer on Filemaker Engine, and theoretically should be as fast or a bit slower than Filemaker's native functions.

       

      So does someone has experience with JDBC updates or ultra-fast method to create or update records ?

      Thanks

        • 1. Re: Fastest way to "put data" inside filemaker
          fmpdude

          I've used JDBC extensively with FMP.

           

          JDBC has many, many, many advantages over FMP alone:

           

          1.  With JDBC logic, you keep looping out of FMP. That can save you LOTs of time (hours, even) alone.

          2.  FMP's JDBC driver supports the entire CRUD model: SELECT, INSERT, UPDATE, DELETE

          3.  You program JDBC code in a rich IDE and in Java not in FMP's "Script Workspace" (which may be OK for small stuff)

          4.  The JDBC code you create is cross platform (no vendor lock in)

          5.  The JDBC code you write is FAST. It's compiled Java code.

          6.  You're in charge of your code: you maintain it. It's your source code.

          7.  No proprietary, expensive, one-platform-only (FMP) plug-ins, plug-in costs, or plug-in bugs to deal with!

          8.  Once written, you can easily schedule the JDBC code to run automatically in Windows (Task Scheduler) or Mac (Cron).

           

          -----

           

          I recently did a test with JDBC using FMP and the same JDBC code using MySQL. Well, MySQL was 500% faster than FMP, but still the JDBC code against FileMaker was about 10 times faster than if I had tried to do the same thing in FMP using scripts alone (lots of looping).

           

          Regarding "UPDATEs", in JDBC, you would most likely use a pre-complied PreparedStatement. This is also wickedly fast.

           

          Let's get objective -- If you have a particular benchmark (say a JDBC INSERT test into FMP using some sample data) you'd like to run, let me know and post some sample data here. I'll post back my JDBC test results to this thread.

           

          HOPE THIS HELPS.

          • 2. Re: Fastest way to "put data" inside filemaker
            Vincent_L

            Interesting but I'm focussed on the raw speed of the insert, or update (once the data is already prepared).

            Do you think that this is faster than a filemaker native import ?

             

            My goal is to sync data that's coming from MySQL to filemaker. Before I dumped all the data from MySQL and import it in filemaker. Now, thank's to Filemaker ExecuteSQL, which is faster than filemaker export. I Dump all data from Filemaker before, compare it to all the data from MySQL, and only insert/update the difference with a Filemaker import.

             

            Also, do you think that JDBC is faster than ExecuteSQL ?

             

            How do you make JDBC work ? with command line ?

            • 3. Re: Fastest way to "put data" inside filemaker
              fmpdude

              I wrote a MySQL <---> FMP sync utility a few years ago. That sync utility took advantage of the JDBC's built-in MetaData class which lets you figure out all the tables and field types programmatically so nothing in the JDBC logic needs to be "hard-coded" for a particular table. So, in this case, there was a single "sync" program, which ran, generically, and did the sync.

               

              I found that ExecuteSQL and JDBC database-only things were about the same speed since at some point, they both rely (for SELECT, which is all ExecuteSQL supports) on the same FMP SQL "engine".

               

              But, I found that when you combine the looping that goes on in the JDBC logic (going through a ResultSet, like a "FoundSet" in FMP, for example) -- and keep that looping out of FMP,  and you combine all the other fast Java logic, JDBC code was significantly faster than FMP alone. Sometimes that difference was hours in FMP vs. minutes in Java/JDBC against the same FMP database!

               

              Additionally, ExecuteSQL is SELECT only so if you need to do INSERT, UPDATE, or DELETE, you need JDBC (or some other solution).

               

              ---

               

              JDBC is Java code. Once you write it, sure, you could run it from the command line, you could schedule it to run at any time or interval using Windows or Mac, etc.

               

              Best yet, you're in control. And, it's all free.

               

              I'll be happy to run a simple benchmark for you given some test data.  JDBC may or may not what you would want to do, but I'm very happy FMP gives you the choice to use it, albeit only on the same FMP machine (a silly (apparent) marketing restriction on the JDBC driver that I've never seen with any other JDBC driver, anywhere).

              • 4. Re: Fastest way to "put data" inside filemaker
                Vincent_L

                 

                I found that ExecuteSQL and JDBC were about the same speed since at some point, they both rely (for SELECT, which is all ExecuteSQL supports) on the same FMP SQL "engine".

                 

                 

                I use ExecuteSQL a lot, however I find it slow, especially with huge INs. and there's a problem, the first time you launch the query, it can be 10 times slower than the second time you run it because it seems that filemaker needs to cache the data somehow (which is stupid because you mostly need to query once)

                This, terrible phenomenon, lead me to suggest that Idea, which appear essential to me :

                ExecuteSQLonHost ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... })

                 

                Maybe, if the JDBC has not this caching issue (meaning if first query is as fast as second), I could create a function with BE plugin that will make Replace ExecuteSQL by doing the JDBC query instead.

                 

                So If you have a working system with JDBC installed, you could try to do a big select on you biggest table maybe involving many keys in a IN statement, do it with Filemaker ExecuteSQL one, then second time, and do the same thing with JDBC and time first and second time and see the difference.

                 

                Also, some pointers on how to make JDBC work would be great. I've FMS 15 setup with JDBC installed, and database allowed in JDBC.

                But it's not clear if I need to install drivers, because there's no driver download for 15 at filemekr downlaod maybe they're already installed. And then what to do, as I want to qury thru command line

                 

                Thanks

                • 5. Re: Fastest way to "put data" inside filemaker
                  PeterDoern

                  I'm interested in how this item from the Product Roadmap could be used to increase (apparent) speed:

                   

                  Variables in FileMaker data source references — Dynamically open hosted FileMaker files by specifying a variable when choosing a file path.

                   

                  Off the top of my head: three files in play: UI, Data 1 and Data 2. UI file reference points to Data 1. Large update to Data 2 in the background, then change UI file reference to Data 2. Large update to Data 1 in the background, then swap the file reference again in UI. Rinse and repeat.

                   

                  Naturally, I have no idea how well this would work in practice.

                  • 6. Re: Fastest way to "put data" inside filemaker
                    Vincent_L

                    Probably won't work as the variable will be evaluated on file opening, so you'd have to close and reopen the file on all clients

                    • 7. Re: Fastest way to "put data" inside filemaker
                      fmpdude

                      For testing, so that it matches what you have, and is relevant, please create a sample dataset and post here. Also, please include the INSERT or other test you want to do. Please keep it simple.

                       

                      To your other question -- The FMP JDBC driver (free, and included with FMP) is all you need. No ODBC installation or other tasks. You simply put that driver in your Java Classpath (simple to do in the IDE) and write JDBC code.

                       

                      Here's one of thousands of URLs with example JDBC code: JDBC - Sample, Example Code

                       

                      (Don't be put off by the code. It's actually quite simple to write.)

                       

                      To write JDBC code for FileMaker, you would only need to change your connection settings and any SQL that might have a slightly different syntax than another vendor's. For example, MySQL SELECT syntax is different than FMP's for the paging portions of SELECT.

                       

                      I can post typical FMP JDBC connection settings if you would like.

                       

                      ----

                       

                      I'll check in again later or tomorrow and will be happy to help you benchmark your sample, to-be-posted, data.

                      • 8. Re: Fastest way to "put data" inside filemaker
                        Vincent_L

                        Many Thanks FMPdude

                         

                        I can post typical FMP JDBC connection settings if you would like.

                         

                        That'd be great thank you.

                         

                        IDE ? do you have a free one that works on mac you'll recommend ?

                         

                         

                        For testing, so that it matches what you have, and is relevant, please create a sample dataset and post here. Also, please include the INSERT or other test you want to do.

                        I'll probably test it myself with my solution so It will be more relevant, I'll put my result here

                        • 9. Re: Fastest way to "put data" inside filemaker
                          wimdecorte

                          Vincent_L wrote:

                           

                          Also, do you think that JDBC is faster than ExecuteSQL ?

                           

                          I don't think I understand the question; ExecuteSQL() is an internal function whereas JDBC is an external API.  In what way are you comparing them?  They don't target the same thing.  ExecuteSQL() is to SELECT things using SQL syntax while inside of FM; JDBC lets you use SQL syntax to INSERT, UPDATE, SELECT,... while being outside of FM.

                           

                          Can you give an example of a workflow you'd like to do with both of them?

                          • 10. Re: Fastest way to "put data" inside filemaker
                            PeterDoern

                            Vincent_L wrote:

                             

                            Probably won't work as the variable will be evaluated on file opening, so you'd have to close and reopen the file on all clients

                            Ah, that makes a lot of sense.

                            • 11. Re: Fastest way to "put data" inside filemaker
                              fmpdude

                              Agreed, I asked for basically the same info.

                               

                              I wasn't sure how ExecuteSQL would work with the stated "putting data into FMP" workflow, either.

                              • 12. Re: Fastest way to "put data" inside filemaker
                                fmpdude

                                Sure, you could either use the free Eclipse IDE, the community edition of Intellij, or NetBeans. All these work on all OSes.

                                 

                                Look forward to seeing your results.

                                 

                                Please also post the details under which your tests were run, with some sample data if possible, so others can replicate and validate your results. (Always useful thing to do.)

                                 

                                Please let me know if you have any questions.

                                • 13. Re: Fastest way to "put data" inside filemaker
                                  Vincent_L

                                  ExecuteSQL is Slow, I need the fastest way, as said, and a bit explained in my linked idea, Making the SQL query ran by JDBC could be faster than ExecuteSQL itself due to the bad caching behavior it has.

                                  • 14. Re: Fastest way to "put data" inside filemaker
                                    fmpdude

                                    Oops, forgot to include a sample JDBC connection string.

                                     

                                    Assuming you have a database called OSGB, here's a sample connection string I use that works.

                                     

                                    jdbc:filemaker://localhost/OSGB

                                     

                                    You also need to pass the user name and password depending how you set that up in FMP's sharing preferences.

                                     

                                    (Note, that the IDE itself allows me complete access to the database from within the IDE itself to do queries, see/modify data, etc... VERY POWERFUL extra cool capability -- much, much, much more powerful and useful than the "Data Viewer".

                                     

                                    Here's the additional stuff (vs the Data Viewer) you can do with your FMP data in the IDE (beyond JDBC code)...This totally rocks!:

                                     

                                    1 2 Previous Next