10 Replies Latest reply on Jun 23, 2014 5:37 AM by malachydevlin

    MS Sql or Not.

    malachydevlin

      Hi,

      I am considering using ms_SQL for a full backend to my Filemaker frondend.

      My main reasons are:

      1 - so I can base layouts on advanced queries/views in the sql DB

      My reason is that I believe this would be much faster than using filemaker and omiting/finding records as the layout loads, especially as I could return only the columns & rows i need at the backend.

       

      2 - With the data in msSQL its easy for other applications to access that data as its a more common standard.

       

      So I was hoping for opinions on these 2 points and maybe some potential pitfalls "down the line".

      for example I al already seeing that the relationship graph & instances of tables can be a little unpredictable when connection to SQL.

      e.g I have a table occurance that linked to a back end SQL table but filemaker refused to see any records no matter what i did. So, I had to create another table instance which worked fine (to the exact same table), problem was I had to remap every layout and field on that layout to the new table instance.

      Im just afraid of causing too much hassle for myself.

      It would be easier to just use filemaker and accept points 1 & 2 as they are?

       

      Any experience would be appreciated.

        • 1. Re: MS Sql or Not.
          wimdecorte

          It may be a bad idea.

           

          Don't expect things to be much faster, so test that very carefully.

           

          On #2: what other applications do you have in mind?

          FMS supports all common standards (ODBC, JDBC, XML) and has a native PHP API, plus derived APIs for .NET, Ruby, Python,... so I would not necessarily agree with the "more common standard" argument.

          Depending on the type of access and the amount of records, these other applications may get better speeds from an MS SQL Server backend than that they get from FMS.

           

          It's not a trivial decision so I would do a very thorough proof-of-concept & benchmarking the current system before making that decision.

          • 2. Re: MS Sql or Not.
            CarlSchwarz

            Reason 1 speed:

            I'm finding on 21.5million records in a filemaker database instantly (connecting over a WAN 6mb down .5mb up).  Good design should give you these results, You need to find on indexed data.  If you are performing a find on unindexed calculations then script filemaker to pre-calcuate that data as it goes in and only do finds on indexed fields (if there's a UI responsiveness problem with that on data entry then pass the calculations to the server and free up the UI).

            Based on what you have said it seems like you may need to redesign the DB so that you are relating only to the records you need or something like that (extra tables, joins perhaps) and have it so you don't do a find when you go to a layout.

            From what you have said combining filemaker with ODBC to SQL doesn't seem like the best idea in this case if you can avoid it... and if you are passing lots of data back then it probably would be slow.

             

            As for point number 2:

            Use PHP to connect to Filemaker, the API is good and what the API lacks can be made up for with scripting from Filemaker.  PHP is super common so any developer should be able to work that API into a Java, .net, or web application.  The filemaker PHP API is fast.

            • 3. Re: MS Sql or Not.
              CarlSchwarz

              I had the database open so I thought I should show an example.  This is the db opened over a WAN and it's quicker than I expected since I thought there should be more internet lag atleast.

               

              Also on the flipside if you are working with SQL developers and that is all they know then maybe SQL would work better for you.

              • 4. Re: MS Sql or Not.
                malachydevlin

                Here is an extract from the filemaker article on PDF (introduction to ESS).

                 

                "ESS Design Goals: What It Isn’t The emphasis with ESS should be on integration.  The ESS feature set is not intended to allow FileMaker Pro to act as a “front end” to SQL data sources."

                • 5. Re: MS Sql or Not.
                  malachydevlin

                  Thanks a lot for this, much appreciated.

                  • 6. Re: MS Sql or Not.
                    beverly

                    HMMMMM...

                     

                    q1: do you have control of the MS_SQL such that you can

                         create views?

                         control access (permissions)?

                     

                    q2: (more of a comment) - you've found the doc that discourages this "front-end" usage by FM to SQL(s). Did the article give you the whys? It's really important to understand the under-the-hood for ESS and what FM is doing.

                     

                    So, here's my advice that I've been stating since there has been this "magical connection" between FM and SQL(s):

                     

                          

                    • use views that narrow the dataset down considerably, your ability to create these views may be crucial

                     

                          

                    • narrow down not only the # of rows/records, but also the number of columns/field in views

                     

                          

                    • never navigate to a List View or Table View layout based on an External SQL Source directly

                     

                          

                    • always script navigation such that the user is taken to a Form View in Find Mode for searches first on the SQL table occurrence

                     

                          

                    • then if the found data set is small, a list or table view can be displayed

                     

                          

                    • avoid making edits in List View or Table View, use the Form View with a single record for edits (including creation and deletion)

                     

                          

                    • be wary of network disconnections (as there is no Transaction/Rollback function directly to the SQL)

                     

                          

                    • it's possible to use FM as an ODBC source, too, and perhaps an application that can communicate to both FM and SQL in a web-based mashup-type solution. This is your "interface/front-end" for both databases.

                     

                          

                    • it shouldn't have to be said, but the SQL must have a unique identity to every row/record for use with ESS. This needs to be unique within the table, not null and not editable by FM. Yes, I've seen SQL dbs missing this <shudder>.

                     

                          

                    • be aware that this is possible, but you have to take precautions and that access to the SQL by other means may be important.

                     

                    Beverly Voth

                    FM developer

                    Web developer

                    SQL DBA

                    • 7. Re: MS Sql or Not.
                      LSNOVER

                      In it's current Form, ESS is not well suited to be an exclusive front end to a SQL database.

                       

                      You CAN make it work, but it will require a lot of work, and some tradeoffs.  If you have a very compelling reason to use SQL as your main DB, you can venture down that road.   I've been ALL THE WAY down the road using FM as a front end to Oracle.  I've ended up coming back to more of a Hybrid system to make things work optimally.

                       

                      For smaller systems, it actually can work quite well.  But you will need to have access to the SQL DB to construct things carefully to optimize use with Filemaker.

                       

                      Anything with relationships using SQL data in Filemaker is woefully slow, especially when doing finds.  So you will end up contructing "Flat" views to optimize searching, etc.

                       

                      In order to get the advantages of speed in SQL, you will need to use Stored Procedures and views extensively.  If your not comfortable with these features, you will be wasting your time.   Sorting speed with anything more than a few thousand records is REALLY, REALLY slow, because Filemaker must absorb the data into it's own environment first, and then sort as unindexed data in it's own context.

                       

                      With all that said, if you are dealing with large SQL dbs, and you can manage found set sizes in your interface well, you can do some amazing things using Filemaker and SQL.  Creating summarized data on large sets is MUCH faster in SQL than Filemaker.  Running complex calculations in Stored Procedures is MUCH faster in SQL.    SQL has true transactional integrity as well.

                       

                      Filemaker 13 has improved things for us somewhat.  We can now call Stored Procedures on a SQL server through the Filemaker server using the Perform Script on Server feature.  This negates the need to setup ODBC connections on client machines, which is a very big deal.  ESS/SQL work fairly well with WebDirect and FMGO.  There really is nothing else like WebDirect and FMGO out there, so using it with SQL data really opens ups some great possibilities.

                       

                      It's about managing your expectations at this point.  I would also enourage everyone who is using ESS with Filemaker to continue to ask FMI to enhance this feature set.   A little bit of work on issues like sorting, and dealing with Binary data would make a drastic improvement in the usability of Filemaker with SQL Databases.  Squeaky wheels get the oil!

                      • 8. Re: MS Sql or Not.
                        taylorsharpe

                        +! on Lee Snover's summary as well as Wim Decorte comment on your question #2 (FileMaker is compliant with most current common standards).  Many web developers are taught in class how to interact with a free database like MySQL and that is why so many web developers will say they know SQL, but not FileMaker, not realizing FileMaker is SQL compliant.  Just because SQL isn't in the "FileMaker" name doesn't mean it doesn't fully support SQL calls and this is just a lack of understanding by many web developers unfortunately.

                         

                        As Lee commented and supported by an understanding of ESS limitations, you will realize that FileMaker is a faster front end to its own data than an external data source. 

                         

                        But I see where you are coming from and I remember when ESS came out thinking, wow, the User Interface power of FileMaker with an amazingly fast data source.  But it just doesn't work that way. 

                         

                        Actually, FileMaker is extremely fast, but it lends itself to doing sloppy development work with lots of unstored calculations (which you can't even do in SQL schema) and poorly optimized relationship graphs.  A good developer will avoid these pitfalls, but as a developer myself, a lot of my job is cleaning up poor development work by previous staff.  If you design things well in FileMaker, you can get very good performance.  Probably FileMaker's only real limitation comapred to MS SQL Server is when you get into handling many hundreds or thousands of users (lots of simultanous transactions), then the SQL database can be scaled across more machines to perform better.  But aside from really large numbers of users, you should be able to get the same performance out of FileMaker if designed the same way as in SQL Server. 

                         

                        I think you are really looking to make FileMaker perform better.  If you are looking for better client performance, I suggest you pursue ExecuteSQL on FileMaker in conjunction with "Perform on Server".  I make a lot of reports go a lot faster by using virtual reporting from data returned by SQL generated on the server.  I return the results to an array table based on values in a $$ global variable.  It has its limitations in that you can't edit the data at that point, but if sure can make reporting go a lot faster than when processed on the client. 

                        • 9. Re: MS Sql or Not.
                          beverly

                          No, unfortunately it's lack of promotion of ODBC publishing of FM to the web. It's a very slick way and in many instances preferable over XML web publishing of FM. Custom web publishing in FM should always be spoken with these three methods:

                          • XML (using any web application that can deal with the raw source of several XML grammars)

                          • API for PHP (a specific XML grammar and classes canned for use with PHP)

                          • ODBC/JDBC (using any web apps that can deal with FM as the source)

                           

                          I have used all of these methods. I've also used a mashup of SQL db and FM db on the same site.

                           

                          -- sent from my iPhone4 --

                          Beverly Voth

                          --

                          • 10. Re: MS Sql or Not.
                            malachydevlin

                            Sorry for the delay to all of you, Ive been off for a while.

                            I want to thank your for your valuable input, its being very helpful.