8 Replies Latest reply on Dec 16, 2016 8:29 AM by fmpdude

    Slow SQL/ESS import using ODBC from sqlServer.

    hulseymw@ornl.gov

      Our company moved from Oracle to Microsoft sql Server last year and I switched ODBC drivers accordingly.  Using the Filemaker recommended drivers from Actual Technologies, my SQL queries and general browsing of views is twice as slow on sqlServer compared to what I experienced using Oracle.  Queries (called from a macro) that took 15 minutes suddenly took 50-60 minutes.

       

      My database administrator (DBA) suggested I add the "hash" command to several of my join statements and this took 10 minutes off the time.  The DBA then contacted our Microsoft sqlServer rep who came for a visit.  They turned on some network and database monitoring and found that the issue is not with sqlServer or the network, but rather with Filemaker.  It appears there's a limitation as to how quickly Filemaker can accept the data as it's received from the server.  They've contacted Actual Technologies to see if the limitation is with the ODBC driver or Filemaker.

       

      Does anyone know of a similar support contact at Filemaker so I can pass that on to our Microsoft representative?  This is probably common to anyone connecting to a large sqlServer database so I'm hopeful any fix will benefit the whole Filemaker community.

       

      Thanks.

        • 1. Re: Slow SQL/ESS import using ODBC from sqlServer.
          Johan Hedman

          If you have a ODBC driver installed, create a System DNS on your FMS and have each table that you need be used inside your solution using ESS-technique. Then you do not need to do any SQL-queries and each table behave almost like a ordinary FM table inside your solution.

          https://www.filemaker.com/downloads/documentation/techbrief_intro_ess.pdf

          • 2. Re: Slow SQL/ESS import using ODBC from sqlServer.
            hulseymw@ornl.gov

            Our company DBA, Microsoft Engineer, and ODBC vendor engineer finally had a chance to discuss.  While the Microsoft engineer helped streamline some things on our server, the ODBC engineer described FMP's handling of ODBC data sources as made for ease of use and not performance.  There are some caching issues in play between FWP and the sqlServer when a table occurrence is defined and data is browsed on a layout.  His suggestion was to use the SQL import script steps to import the data into FMP instead, then browse as a local table.  In my case, the import still took a long time until I turned off the "perform auto-enter calculations and lookups when importing" option.  Unchecking this made a massive speed improvement during import.  A 30-40 minute import was reduced to 8-10 minutes.  I then scripted the calculations after the import was completed.  I hope this helps others in the same situation.

            • 3. Re: Slow SQL/ESS import using ODBC from sqlServer.
              beverly

              Absolutely good advice!

               

               

              Sent from miPhone

              • 4. Re: Slow SQL/ESS import using ODBC from sqlServer.
                hulseymw@ornl.gov

                The ODBC engineer suggested a similar approach to Johan's, that is, use FileMaker Server (FMS) to run the SQL query on a schedule, importing the found set into a native FMP table on FMS, then have clients access that.  FMP clients can access the FMS table faster than sqlServer and it reduces the number of records vs. the original SQL table.  This works in situations where live SQL data is not needed.  Unfortunately, I do not have access to a FMS environment and must run the client version directly against the SQL table, so my suggestions are focused there.

                • 5. Re: Slow SQL/ESS import using ODBC from sqlServer.
                  beverly

                  you can run the query "locally", but you cannot schedule (middle of the night) like you can with FMServer. so if you do not have access to setting up a Schedule, strongly suggest the idea to whomever does!

                  beverly

                  • 6. Re: Slow SQL/ESS import using ODBC from sqlServer.
                    fmpdude

                    Have you considered using JDBC? FileMaker includes the JDBC driver for free in the download for accessing FileMaker itself

                     

                    For SQL Server, however, you can just as easily get the free MS SQL Server JDBC driver right off the MS website. You can then also do queries, using a JDBC connection, in real-time against SQL Server using tools like "RazorSQL" which works with any JDBC-enabled database (every database).

                     

                    It's something to test.

                     

                    I find JDBC with FileMaker is about the same speed as the ExecuteSQL within FileMaker. Not great, but not terrible.

                     

                    And, if you need to work with data between SQL Server & FileMaker and the ODBC is too slow, try writing (or hiring someone to write) Java code using JDBC (using, in this case, both FileMaker & SQL Server JDBC drivers) to do the data work. Once you switch to JDBC you have full INSERT, APPEND, UPDATE and DELETE.

                     

                    Java is incredibly fast.

                     

                    HOPE THIS HELPS.

                    • 7. Re: Slow SQL/ESS import using ODBC from sqlServer.
                      hulseymw@ornl.gov

                      fmpdude,

                       

                      I'm very interested in trying your approach, but I haven't been able to figure out how to get JDBC setup and configured to get FMP talk to our sqlServer tables.  My only background is ODBC so I'm familiar with those config interfaces, but JDBC seems to have a language of it's own.  Is there a good resource for config and implementation you'd recommend, other than the documentation on FMP's website?  Their JDBC guidance seems targeted at using FMP as the data source, not a sqlServer location.

                       

                      Thanks.

                      • 8. Re: Slow SQL/ESS import using ODBC from sqlServer.
                        fmpdude

                        Wow, after several years of my posting stuff about JDBC, the first taker!!!!

                         

                        Yes, I'll be glad to help.

                         

                        First, the "J" in JDBC stands for Java. So, you need to write a (usually small) Java program (TONS of examples online) to interact with SQL Server. To interact with SQL Server, step 1, you download the free JDBC driver from MS and put that in your Java Classpath -- usually in a Java IDE.

                         

                        The good news is that whether you're using JDBC for SQL Server, Oracle, or for FMP, the basic JDBC code is more or less the same--except possibly for some SQL extensions SQL Server supports like RegExp. But that's the SQL itself, not the code.

                         

                        Connecting to SQL Server using "Trusted Authentication" is also trivial using JDBC, assuming you have that setup. I can post a sample of what that JDBC connection URL looks like if you need it.

                         

                        -----

                         

                        Now, if you also want to interact with FMP, say, to transfer data one way or the other, you would also use the free JDBC driver FMP has. Like with SQL Server, you would put the FMP JDBC driver in your Java Classpath - again, usually in a Java IDE.

                         

                        As with FMP plug-ins, using JDBC gives you complete CRUD capabilities --INSERTS, UPDATES, QUERIES, and DELETEs  (if SQL Server permissions allow these, of course).

                         

                        The basic idea with JDBC is, in code, you:

                         

                        1. Create a connection

                        2. Create a statement (your SQL)

                        3. (Optionally) Get/Use DB MetaData

                        4. Get a ResultSet back or run a PreparedStatement (pre-compiled SQL)

                        5. Do stuff with the ResultSet.

                        6. Whatever else...

                         

                        ------

                         

                        As listed above, JDBC also supports getting DBMetaData which is extremely useful especially for data migration. Using the DB Metadata, you can determine (programmatically in your code) the table names, field names, and field types for both databases. For data migration between SQL Server and FMP, I create a method (procedure/function) in my Java JDBC code to "translate" data types I got from the DBMetaData. FMP knows some types that may be (probably are) different on SQL server.

                         

                        My longest JDBC program is about 425 lines, but it's not complicated code.

                         

                        --------

                         

                        A free Java IDE like Eclipse is probably all you need. Plug-ins for Eclipse (unlike FMP) are free.

                         

                        Let me know if you have other questions or if you want to post your code, I'll try to help, OK?

                         

                        HOPE THIS HELPS.