10 Replies Latest reply on Apr 9, 2013 5:43 AM by MarkAusten

    FM vs MS SQL Server

    taylorsharpe

      I'm spending weekends this month taking a Microsoft SQL Server developer class. I can say installation and setting things up sure are easier on FileMaker. And doing things like change a file's schema is a lot less daunting than in MS SQL Server. Some of the more complicated processes may be why MS SQL is more scalable, but this class just makes me appreciate FileMaker more and more.

       

      Some interesting things, Mathamaticians and Scientists may appreciate that FileMaker can handle larger numbers (10^400 verses 10^308). Also, it is interesting that in one of the most common numeric formats for SQL Server, DECIMAL, it has rouding error issues. If you take 1, divide it by 3 and then multiply it by 3, you get 0.99999999.... If you do this in FileMaker, you get the correct answer of 1. Tell this to a financial institution that does many many calculations and they will get worried about the accumulation of rouding errors, which is quite a valid concern.

       

      FileMaker can go backwards further on dates than SQL Server, but there is always ambiguity over which calendar date you are using, Gregorian or Julian. SQL Server does have a lot more field types, particularly number types. And while this may be beneficial to the underlying processing, it is mostly just limiting to the developer and reduces flexibility.

       

      Many of us know, one of the fairly unique features of FileMaker is having fields that are calculation fields. For those of us that work mostly in FileMaker, we tend to forget how wonderful this is. Granted there are issues with calculations resulting in performance problems if not properly developed, but it still is a nice feature that we take for granted.

       

      I always have a hard time remembering that MS SQL Server is just a backend and while it is great at quickly providing results, it does not help you when it comes to how you really want to see the information. That is not what a SQL Server developer does and they leave the front end to web developers or Access developers or other front end products. As a FileMaker solution, my clients want the entire solution and I have found that this is expected of FileMaker developers. No one expects a FileMaker developer to just do schema design and not anything with layouts. But that is exactly a SQL Server developer does and is expected commercially. I hope our FileMaker customers appreciate that a FileMaker developer does more than a SQL Server developer does for them.

       

      Well, I have completed 1 of the 4 weekly classes and will report back as I learn more. I was even daring enough to wear a FileMaker shirt there <grin>. The purpose of taking this class is to help more with clients that need FileMaker to integrate with SQL Server and this is becoming a common request. I have no intention of abandoning FileMaker as my primary development tool. But it is still good to know the competition and learn strengths, and weaknesses, of FileMaker so that I can make good software recommendations to business clients.

       

      One last comment... the class we are being taught in is from a book that just recently also came out as an eBook. So in this class of about 75, there must have been about 20 people there with iPads for going through the book with the instructor. These are die hard Microsoft developers and I was amazed that so many would have an iPad. If I could only do a quick demo on how easy it is to get a FileMaker database up and running on an iPad.

        • 1. Re: FM vs MS SQL Server
          mikebeargie

          Until filemaker opens up schema modification for playing around with a command driven context, SQL will always be superior for dealing with schema and mass-record changes, especially on massive data sets.

           

          IE, if you want to clear 1,000,000 records out of a filemaker database, how long does it take to "delete all"? In SQL it's almost instant using "DELETE".

           

          I realize that filemaker may never give us this access (there is an extreme amount of danger for accidental modification/deletion/loss of data), which goes back to the discussion of "the right tool for the right job".

           

          Filemaker accessing SQL via ESS is a very powerful tool that leverages benficial points of both systems, especially using the "other ExecuteSQL" (script step).

          • 2. Re: FM vs MS SQL Server
            MarkAusten

            You might want to qualify that "almost instant" statement, I've just deleted 6275 records from a single table in a large and complex SQL Server database and I can assure you that it wasn't even close to being "almost instant". Even well optimised it took a fraction over two and half minutes.

            • 3. Re: FM vs MS SQL Server
              taylorsharpe

              Funny you should mention the DELETE issue... it is like in FileMaker it deletes each record one at a time and rebuilds the index inbetween each delete.  Boy it takes forever to do a Delete All on a big table, especially remotely.  It would be nice if FIleMaker looks to see if you have done a Show All and you are doing a Delete All, that it just wipes everything instantly instead of trying to index things as it deletes. 

              • 4. Re: FM vs MS SQL Server
                mikebeargie

                I've deleted 1 million records from a single table (no relations) in SQL in under 15 seconds before. There were about 40 fields in said table, and about 5 indexes. The same test in filemaker on our fastest machine with a local file took around 3 hours to delete the same data. I know these are rough estimates, but there's no denying that it IS faster in SQL for certain functions.

                 

                I know there's SQL experts out there that sometimes will use "DROP" and "CREATE" to completely erase the table from the databse (faster than clearing records) and re-add the same table as a blank for performance.

                • 5. Re: FM vs MS SQL Server
                  LSNOVER

                  The power of SQL Server and Oracle are in their Stored Procedures.  With these tools you can create the equivalent of Calculated fields, and you can also build in a host of Business rules that are nearly impossible to circumvent NO MATTER what the interface is.  You only have to write these once at the database layer.  These functions are very fast if the database is setup correctly.  You DO have to be proactive in setting up the database.  They do not auto-index (though you could code this).  There is no interface per say. 

                   

                  I've done SQL development for years, and I almost ALWAY have to do the interface as well as the database part of the project, so it sounds like your dealing wiht more "corporate" types then independent developers.  Filemaker makes a great interface for SQL Dbs, but it's not anywhere nearly optimum as it could be.  Not sure if this is by design or not, but it is often frustrating.  I think FM could attract many more SQL database users and developers with a little more attetion to ESS.  

                   

                  Most databases are not optimized for Deletes.  However with a SQL Database, you can set a delete flag and then create a view that effectively hides all the "deleted" records, and then have a SQL Job that cleans out deleted records in the back ground periodically, withing impacting the user during high volume activity periods.  On the other hand, in Oracle, you can issue one command that will toast an entire multi-million record database in a fraction of a second.

                   

                  Both Filemaker and SQL Dbs have their pros and cons.  The more I can mix them and use the advantages of either, the better job I can do.   I used to be a total SQL snob, but I've come to really respect Filemaker over the years.  I used to be an Omnis snob, and thought SQL was too complicated to be bothered with.  The tools have gotten better, and the performance advantages are well worth the trouble to learn.

                   

                  If you enjoy the SQL Server course and Stored Procs in particular, you will really appreciate Oracle's PL/SQL.  Have fun!

                  • 6. Re: FM vs MS SQL Server
                    taylorsharpe

                    We haven't gotten to the stored procedures yet.  But I've been dabbling in Apex for Oracle, which uses PL/SQL and has been mostly confusing me.  I can edit it, but have a hard time creating things.  That is something else I'm supposed to learn later this year (oh my head hurts).  And while Stored Procedures are the SQL way of handling calculation fields, FileMakers method sure is easy and once applied, easy accessible to anyone in a layout the same way you show any other field. 

                     

                    Yes, you are correct, most of the people I've met in this group are corporate types and apparently quite specialized in their corporate environment.  For some reason, a bunch of them seem to already know this and are taking it as a refresher and others are DBA types getting a developer perspective.  Most interesting, but at least the guys I have sat around have helped explain things to me when I didn't understand.  So it has been a good class so far. 

                     

                    Omnis... I'm not familiar with it.  I'll have to look it up out of curiosity.  15 years ago or so I dabbled in 4D, which is about as old as FileMaker and has been relational from the beginning.  I found an interesting comparison between FileMaker 12, 4D 12, and SQL Server 2008 (apparently they haven't updated it for SQL 2012):

                     

                    http://database-management-systems.findthebest.com/compare/1-12-26/4D-4th-Dimension-vs-FileMaker-vs-Microsoft-SQL-Server

                     

                    Some of the things in this comparison are not great items to compare and a couple of them were wrong for FileMaker. 

                     

                    Thanks for your input, Lee!  Always fun to stretch the mind and good to hear from someone who has worked on several different database platforms. 

                    • 7. Re: FM vs MS SQL Server
                      LSNOVER

                      APEX is free, but it is really not intuitive at all, in my opinion.  It is getting better, but it is FAR from being a GUI.  It still carries over concepts from the old Oracle Forms.  You still have to do quite a bit of manual coding in Java, HTML etc. to really make a nice app.  and you don't get a nice WYSYWIG screen design.  I keep hoping they will make that leap, but it seems to be a foreign concept to Oracle.

                       

                      Omnis goes back to the early 80s.  It's still being updated and sold. It is a great product, very powerful, but it has a STEEP learning curve and the Web based server licenses are a bit expensive.  If your willing to learn it, you can do almost anything with it, but it will take you more time for most things than Filemaker.  4D was a startup competitor to Omnis.  I was one of the first to beta test 4D when it was called Silver Surfer or some such thing.  Guy Kawasaki was running things for awhile and he was very fun to deal with.  4D was just too slow and in those days not cross platform.  It's still floating around too.

                       

                      Filemaker Calcs are fast and easy.   But with Stored Procs, you can do much, much more.  You can update other tables, delete related records, and run entire sub programs.  Script Triggers at the table level would make this possible in Filemaker, but I think the concept will be hard to implement in the context that many Filemaker developers work in.

                       

                      The best thing about Oracle procedures is the scope and breadth of variables you can declare.  You can declare custom data types, you can define a variable from a row in a real table, you can define a variable from fields in other tables, and the variables can be multi-dimensional.   Think of repeating fields implemented as variables, but extended to handle full records with all the fields from a table.  All can be defined and used with just a few simple script steps.  Transact SQL has come a long ways as well, but it still requires much more work than PL/SQL. 

                       

                      It's great fun to learn this stuff and it will make your head hurt.  I'm sure you will pick up some new concepts you can apply to Filemaker as well.  Plus it will make using ExecuteSQL more interesting.

                      • 8. Re: FM vs MS SQL Server
                        MarkAusten

                        Indeed, if I had a table with no constraints at all, then millions of records in virtually no time at all would be expected. But realistically, how often does your schema have such tables? A more likely scenario is to have a number of related tables, it is after all a relational database, and then the deletion get hairy. The table I mentioned in my previous post had two child tables each of which also had two child tables and the deletion started with the grandchild tables, then the child tables and finally the parent. But even though there were no dependent records at each stage of the deletion, the deletion process on the child and parent tables still had to check for referential integrity and that's what slows the process down.

                         

                        Hence my comment about qualifying your statement although I quite agree that deletion in FileMaker is abysmally slow compared to just about everything. I go out of my way to avoid bulk deletes in FileMaker wherever possible.

                         

                        I have to confess to being one of the developers that uses 'DROP' and 'CREATE' extensively, especially in the early stages of development. I have a script that generates a script to drop all contraints, drop all tables, create all tables and then create the contraints. Very useful!

                         

                        Shame I can't do that in FileMaker.

                         

                        Yet!

                        • 9. Re: FM vs MS SQL Server
                          mikebeargie

                          In my industry (data mining related), a lot of the tables in my databanks are outside of the constraints of relationships, but are enormous in record count. So fast deletion helps immensely when we rebuild these databanks.

                           

                          Another thing I'd like to point out is that if you use the data separation model in filemaker, you can clone your data file with no records, which essentially performs a DELETE on all of your data tables.

                           

                          So for some of our data bank files before we switched to filemaker front end / ESS-SQL backend, we'd use that method and store our data bank files in separate fp7 files.

                          • 10. Re: FM vs MS SQL Server
                            MarkAusten

                            Data mining: In the which case you're fortunate enough not to have to worry about key constraints although have other worries instead. And you certainly don't want to be waiting around for hours whilst rebuilding the tables.

                             

                            To be honest I had forgotten about the data separation model but most of the big projects now use or will use the ESS-SQL backend. The small projects don't have such a deletion burden since one of the project criteria for the stuff I'm working on is that nothng can be deleted. Made inactive, yes, deleted, no. Which is another problem entirely.