6 Replies Latest reply on Oct 19, 2012 2:06 PM by disabled_JustinClose

    Boatloads of data, portal filtering, and how to get 1 record...

      Title

      Boatloads of data, portal filtering, and how to get 1 record...

      Post

           I am having some issues and confusion about how a portal operates, in relation to the total amount of data downloaded to the client.  Two questions really:  how do you get Filemaker to send you one whole entire record (I want to measure the size of a record)?  And, the real reason behind that question, how come my portal is downloading tons of data?

           FMS12, FMP12, file hosted on server

           I made a layout (dashboard kind of thing:  buttons to common layouts for the group, a list of upcoming things) with a portal on it to display some records.  I used a portal filter, because I have heard a number of sources say that portal filtering is now done on the server and it only transfers the resulting records.  It is not sorted (in the portal, nor actively by design anywhere else that I know of).

           This layout is based on TableA.  TO_B is based on TableA; there is a cartesian relation between TO_B and TO_A (id x id), since I am filtering at the portal and I need all possible records to be in the starting pool.  The portal filter is based on a two week period ("Date_CurrWk_Monday" is a custom function that returns the date of the Monday for the current week):

           "Visible when:
           TO_B::_Date ≥  Date_CurrWk_Monday and
           TO_B::_Date  ≤ Date_CurrWk_Monday + 14  "

           Essentially, I am just trying to show them a list of items that are coming due in the next two weeks.  But this layout is taking 45+ seconds to load.  So I wanted to investigate how much data was being sent...

           I have whittled the layout down to just this one visible portal.  But when I measure the total amount of data moved (using Wireshark) I get a result of 18MiB downloaded.  And I get this 18MiB result regardless of how  many records end up in the filtered portal.  I have narrowed the filter (altering the date range) to show only 20, 12, 7, or 1 record and the total transfer size is about the same.

           I will admit, Table A is very wide; about 700 fields.  And there are about 3100+ records there.  So I have been trying to measure a single record's worth, and had some mixed success.  (Any suggestions on definitive ways of doing this would be appreciated.  I have run into the various 'chunking' FM does:  25 records at a time in form view.  I have been trying form/list/table view layouts instead of portals and doing a find to get specific record counts.)

           Doing some math on record counts, though, yields something in the neighborhood of 3-5Kib per record.  There are a bunch of calculation fields, too, and depending on whether or not I scroll those into view the record size can increase to 100+Kib.  But on the portal (or anywhere) if the calc fields aren't shown, they shouldn't fire off, so that possible extra data shouldn't be an issue.  I think.

           I was thinking about doing a relationship (new TO) based on a global field, and populating that global field with a single record ID.  (This is actually part of a related discussion about how to filter the portal using this global field, a script, and some ExecuteSQL to get your record IDs.  A simple field would work for testing.)

           Anyone have any ideas on narrowing things down to a specific record easily?  Or why is my portal taking so long, moving so much data?

           Thanks,
           J
            

        • 1. Re: Boatloads of data, portal filtering, and how to get 1 record...
          philmodjunk
               

                    So I have been trying to measure a single record's worth, and had some mixed success.

               Use save a copy as to save a clone (empty copy) of your database.

               Note the size of your cloned file.

               Open the file, go to a layout based on your table and use a script to create a large block of new records--say 500?

               Have a script enter data that is "typical" into any text fields. (text fields can store variable amounts of data).

               Close the file and note the file size.

               Divide the increase in file size by 500.

               This should give you an approximate value for the size of one record. Note that this can only be an estimate if you have any fields of type text. A single text field can store truly massive amounts of data as is also true for container fields so the size of each record can be different from the next.

          • 2. Re: Boatloads of data, portal filtering, and how to get 1 record...
            philmodjunk

                 I also recomend trying out a return separated list of dates to take the place of your inequalities to see if your portal will update more quickly.

                 You can set up a custom function that takes two dates passed to it and it returns a list of dates from one date to the other. This return separated list of dates can then be your match field to your portal's table instead of using inequalities.

            • 3. Re: Boatloads of data, portal filtering, and how to get 1 record...

                   Hmmm...both excellent ideas.  I will give them a try.  Thanks!

                    

                   -- J

              • 4. Re: Boatloads of data, portal filtering, and how to get 1 record...

                     Phil,

                         Using your technique of a clone plus import, it appears that we have an average record size of ~15KB.  So, if my portal happened to be downloading ALL records it would have been 46MB-ish.  So, do you have any ideas of why the filtered portal was pulling down 18MB of data, regardless of how many records it was showing?

                     And a question about this custom function you recommend:  how do I get it to return a return separated list?  I am not seeing how to create a loop (I was thinking of using the start date, setting the end date, and incrementally looping through them, spitting out a date each time).  What did you have in mind for this function?

                     Thanks,

                     J

                • 5. Re: Boatloads of data, portal filtering, and how to get 1 record...
                  philmodjunk

                       I dont' have any access to technical info that would enable me to answer your question. Your description sounds like the portal filter is not applied on the server, but as part of your client session--meaning that all the realted data is sent to your client and then the filter omits those records that don't pass the filter--but I am only speculating from what you have described here in this thread.

                       Loops are one of the primary reasons why we create and use custom functions in the first place. Custom functions use recursive functions calls to loop.

                       Here's the custom fucntion I created to produce a return separated list of dates so that a portal on one of my layouts displays records from a specified date range:

                       //DateRangeList ( DateStart ; DateEnd )
                       //
                       //Returns a list of return separated dates from DateStart to DateEnd
                       //
                       //DateStart  :   Date--first date of list
                       //DateEnd    :   Date--last date of list
                       //
                       Case ( IsEmpty (DateStart ) ; "" ;
                                  IsEmpty ( DateEnd ) ; DateStart ;
                                  DateStart > DateEnd ; DateStart ;
                                  DateStart = DateEnd ; DateEnd ;
                                  List ( DateStart ; DateRangeList ( DateStart + 1 ; DateEnd ) )
                                )

                  • 6. Re: Boatloads of data, portal filtering, and how to get 1 record...

                         I was wondering if it was going to be recurrsion.  Thanks for the sample code.

                         Yes, it does seem that a blob of records is getting sent and then filtered.  (The size of the blob isn't EXACTLY the same each time, but very close.)  But, as I was mentioning originally, that seems to contradict what I have heard from FileMaker, Inc.  Oh well, the Truth is out There.  :)

                          

                         -- J