8 Replies Latest reply on Sep 3, 2013 1:17 PM by LSNOVER

    FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)

    crw030

      All.

       

      At my wits end - been working on this for days trying various things in both FM client/script and even trying to tackle from SQL Server side of things.

       

      My environment is:

      Filemaker Server Advanced 12.0v3 ,

      Filemaker Pro Clients,

      SQL Server 2008R2 for 2 very large Revenue tables.

      - table#1 averages 300k rows/year - 10 years of data

      - table#2 averages 600k rows/year - 10 years of data

       

      Problem Statement: Users are accustomed to having Grand Summary for Found Set within either RAW table. This worked great when all data was in FMS (about 1-2 years worth), but disk storage was climbing rapidly and application responsiveness was declining every month as more records were loaded.

       

      Observed Performance under different Trial Scenarios:

      Being a "SQL Guy" I moved both large tables to a SQL Server 2008R2 backend. Most everything is working great, except SUMMARY's of any type on the found set data are painfully slow. At a couple hundred its probably only 100x slower than when the data was on FMS - but at 30,000-100,000 its essentially unuseable.

       

      If I add Filemaker Summary Field Type - layouts take minutes to load and seem to recalculate randomly (even when the summary field is absent from the layout)

      If I use a SQL trick to calculate the summary on the SQL Server, it doesn't balance as users adjust the Found Set using Omit Record, Omit Multiple, Constrain Found Set.

       

      If I attempt to compute the summary manually (say with a script Get(Nth Record)) - I can see Filemaker querying 25 rows at a time by primary key - hence the application waits for thousands of queries the first time, but a subsequent recalc takes only moments. However every new FIND request appears to clear the cache and the whole process starts over again.

       

      If I perform an EXPORT to local disk, Filemaker appears to work in larger batches (looks to be a couple hundred), but appears to pull all columns of data, not just those I am requesting to export (bandwidth usage is excessive). However, once completed, a local query (Query against the Text output) is virtually instantaneous - but I am concerned how kludgy that would be to have setup on every client.

       

      My question: Has anyone experienced a similar problem and can recommend an approach to improve? I've fed SQL as many as 60,000 rowid's, requesting SUM(Revenue), but generating that list (from the current Found Set) takes MINUTES - whilst the SQL portion takes seconds.

       

      If I could change the batch size to 1000, and restrict the fields being retrieved for each row to the 1-2 numeric values I needed (during the summary phase), this would probably be ideal. However, I have been unable to find anything which explains the 25-row batch size being used by the client (or any way to change it).

       

      In cross-db h3ll.

      crw030

        • 1. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
          crw030

          *Update* - My memory has failed me - EXPORT to Local Disk is currently requesting in batches of 5000.  Still kludge, but wanted to make that correction.   The 200-ish must have been a different scenario, possibly related records, I dont recall properly now.

           

          EXPORT local is currently the fastest:   88 seconds to summarize 67.8k records.

          compared to :                 52 seconds "the old way" using Filemaker Summary Fields when the records were all in FMS.

          • 2. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
            crw030

            Oh dear, I've opened Pandora's box apparently.  No DEV experts want to touch this with a 10-foot pole

            • 3. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
              LSNOVER

              Hi CRV030:

               

              Sorry, many of us were at Devcon and I'm just getting home after spending time with a client.   The rub is that Filemaker is not very efficient doing summaries, even on it's own data, but ESPECIALLY with ESS data.   There are a number of ways to approach this issue, but Filemaker needs to address this internally to do it right.

               

              One is using imports like you seem to have done. 

               

              Two you can speed up the SQL table if you make a view with ONLY the columns you need for the report, as you saw, Filemaker does indeed cache the data, and it must cache ALL of the data before doing a summary in the Filemaker environment.   

               

              Three, is writing a custom SQL Server function that you can pass the record IDs to for the found set, and then getting the data back into Filmemaker.   Getting the found set on a large set can be tricky and slow in and of itself.  However, if you are dealing with found sets that you can easily reproduce with a standard select in SQL, you can get totals back very quickly.  I often setup a "Report Table" for each user and use it as an intermediary between stored procedures in SQL and Filemaker. 

               

              What we really need to do is petition Filemaker so they understand that the current limitations of ESS make some of these functions quite painful.  I believe they can be resolved, but it will take FM dedicating some resources to do. The more they hear from customers, the more likely they will make these changes.  So PLEASE write a note to FM and let them know your pain.

               

              In the mean time, please let me know if I can help you further.  Chat me privately, and I'll be happy to give you a call to discuss in more detail.

               

              Cheers!

              Lee Snover

              • 4. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
                steve.winter

                Hi

                 

                Thanks for your email. I'm currently on leave until Monday 9th of September with limited access to

                email.

                 

                If your message is urgent, please forward it to support@msdev.co.uk or call +44 777 852 4776.

                 

                In all other cases I'll reply as soon as I'm able.

                 

                Cheers

                Steve

                • 5. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
                  crw030

                  Thanks for the response Lee, for me its a little depressing - because I have bumped into "little things" that sometimes turn out to be kind of "big things" like this problem.  Can you elaborate on your comment regarding "make a view with ONLY the columns you need for the report", assuming I am returning only the select columns presented on the layout - is there a shortcut (for example - go to related records) from a simplified SQL view built on the same base table but with only the primary key and revenue $ ?  Or any option which would use a Filemaker TO: with reduced columns - but it would need to be smart enough to be linked to the current "Found Set" for the full table.

                   

                  Unfortunately, the "capability to compute Summaries on the Found Set" is ingrained in how the users are using the tool, but everything else about offloading the two giant revenue tables has been pretty positive.

                   

                  I am currently evaluating a third-party product to see if I can find a "work-around" which wouldn't require additional setup on the clients.  Currently it seems like the best performance I can get is essentially exporting all the fields needed to compute the summaries for all records in the found set.

                  • 6. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
                    LSNOVER

                    crw030:

                     

                    Can we get a real name? ;-) 

                     

                    I feel your pain.   ESS has such great potentional, but it needs some performance tuning.  Please do not get discouraged.   Write a letter to filemaker and let them know about your challenges.  In the mean time, there are ways to make things work.

                     

                    The "found set" is key to folks who use Filemaker.   We have a bit of a challenge in passing the found set to the SQL database, but it can be done, and I hope that it will become easier in a future release of Filemaker.  (Again, please talk, write, call the folks at Filemaker, they DO listen, but they have many prioirities and some things take time).    

                     

                    Here is basically how I handle this presently.

                    1. Build a list of unique ids for your found set in Filemaker.  Right now, the FASTEST way I've found is to setup a layout with ONLY the key field for the main file on it.  Go to this layout with a script and use the "COPY ALL RECORDS command", then paste into a Global, $variable, or utility record, you will have all of your unique keys in a single field CR delimited.

                     

                    2. If you have ODBC drivers setup on all your workstations, I would now call a stored procedure on your server directly to insert the unique ids into a utility record in your SQL database.    It should take the CR delimited list as a parameter, and have something to identify the particular user and the table you are working with.  If you do not, you can use a utlity record in ESS to put the data into a table.  Note that Filemaker currently has an approximately 16K character field limit in how much text can be put into a field via ESS, so you may have to break the Key data up into chunks.

                     

                    3. OK, you now have access to the found set Ids in SQL.  It's actually relatively quick.   From here you have options.  Some I have employed all utilize SQL Views.  A.) have your stored procedure update a "join" table of individual records from the data you just passed into it.   Create a view from your main table that joins to this table by ID and user.   B.) If your DB supports it and the number of keys is reasonable, you can delimit the list of keys and create a view using the "IN" clause.   I.e. select * from Maintable where key IN (Select max(keys) from utilitytable where user = x) 

                     

                    4. Write a Group By view that summarizes the data using your custom view that is delineated by your found set.

                     

                    5. Access this view in your FM database via ESS.

                     

                    You will find that this method is relatively fast.  There is some overhead for small sets of records, but for large sets of records, SQL can summarize the found set SO fast, it's not even a close race.  The added benefit is, the users can easily export the data from the summarized view, one record per summary, without jumping through hoops.

                     

                    There are some caveats.   You have to track these found sets by user and main table, and that requires you do some clean up and some extra work on the backend.  I have found this works best when I setup invididual logins for each user to the ESS table, and then have access to the SQL databases "user" parameter for filtering my views.    This is a discussion in and of itself, let me know if you want more details.  Otherwise you will have to do a "find" on the users summary records.  You can use their filemaker user name as a key in a relationship to the join table's user field, and use the Go to related records command to get there.

                     

                    This is just a summary of what I do without many details.  Hopefully it gets you a jumping off point.  If you need more help, just let me know. I'd be happy to give you a call or you can call me. 

                     

                    PLEASE let Filemaker know how important ESS is to you and the issues you are having with it.  I consider it a great feature set that is about 80% complete.   The last 20% could really allow Filemaker to fly with SQL data witout all the aggravations we currently have to endure.

                     

                    Cheers!

                    Lee Snover

                    • 7. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
                      crw030

                      Lee,

                       

                        Wanted to give you an update, at least for my combination of remote data on SQL server, FMSv12, and mostly remote Windows FMP clients.  I also validated the summary total was correct between all methods.

                       

                         1. my baseline was Filemaker Summary fields, all data on Filemaker Server.  Summary of 64,465 rows in 57 seconds.

                         2. my *best* so far, export revenue to local .txt file, setup local ODBC connection using .txt driver.  Export+Summary in 76 seconds.

                             -- Requires ODBC detection and automated setup using .vbs to configure registry settings and local paths prior to initial summary

                             -- Setup of  local requirements adds a few seconds on first summary.

                         3. CopyAll rowids (pk) to global, execute batch SQL to SQL result table and query sum_of_resulting_rows. 295 seconds.

                             -- Also would require local ODBC - which I haven't automated but I expect to be typically same effort.

                         4. Export rowid (local), Import to field, ODBC Execute SQL Query [batch] to SQL results, query sum_of_resulting_rows. 83 seconds.

                             -- I tried this in case saving sensitive revenue data locally needs to be avoided.

                             -- the "sensitive" data saved locally by (2) is pure decimal amounts without additional identifying info.  Method (4) avoids this and stores only a generic key.

                             -- for our purposes, single user workstations, (2) is still acceptable (and slightly advantageous) to (4)

                       

                         Since our users have single-user workstations, option 2 is still slightly advantageous to 4 with small exposure risk.  So I am pursuing option #2.

                       

                      I really appreciate you giving me a couple other options to try, and I will be letting my Filemaker Rep know of the criticality of ESS for our community, even if our 500 licenses are probably a "drop in the bucket".in the scheme of things.

                       

                      -Robert.

                      • 8. Re: FM12:ESS:Problem computing Grand Summary of ESS records (SQL Server)
                        LSNOVER

                        Hi Robert:

                         

                        Seems to be much overhead in getting the ODBC driver to work locally.  I hope we have some other options down the pike.  500 seats is nothing to sneeze at.  Please be pleasantly vocal with Filemaker.  I don't believe they fully realize the importance of this feature set.  I know they have MANY other priorities and it's hard to rise above the din, but if enough users speak up, this will move up the priority list.