9 Replies Latest reply on Dec 8, 2011 9:00 AM by petery009

    Do a calculation (non indexed) find on server VS local

    petery009

      Do a calculation(non indexed field) find on my local machine for 10,000 records takes about 2-3 seconds.

      Do the same on a filemaker server on my local network takes about 22-25 seconds.

       

      Does anyboday know why it's taking too long from server?

       

      By the way, we do know that do the find on a non indexed field takes way longer than the indexed find.

       

      Thanks!

        • 1. Re: Do a calculation (non indexed) find on server VS local
          Mike_Mitchell

          Hello, petery.

           

          Simple answer: When you're on the network, data has to move over the network.        

           

          I'm assuming this is the same database, so the only difference being the database is being hosted on Server. When you're on your local machine, everything happens without having to travel over the wire. When it's hosted on Server, you have to deal with the network bandwidth. That's usually the biggest bottleneck.

           

          Without more detail, it's hard to give specifics, but there are some things you can do as a general rule to optimize your performance:

           

          1) You've already noticed that indexing improves performance. If possible, find a way to index the field. (Unstored calculations are performance killers.) You can use auto-enter calculations to replace unstored calculations in many circumstances. You can also run a script as a batch job at night to populate the field rather than rely on populating it at runtime.

           

          2) Eliminate extra fields in the table where the unstored calculation occurs. The entire record has to be loaded over the wire, so, if you can remove extra fields, you'll improve performance. You can put some things in the interface layer with Conditional Formatting, or split them into another table, to accomplish this.

           

          3) Look closely at the Let function. If you have multiple occurrences of unstored portions of the calculation, the Let function can speed performance. For example, say you have something like this:

           

          Date ( Day ( Get ( CurrentDate )) ; 1 ; Year ( Get ( CurrentDate )))

           

          to fetch the first day of the current month. Trivial example, but here you're asking the system to calculate the current date twice. If, instead, you do this:

           

          Let ( todaysDate = Get ( CurrentDate ) ;

           

          Date ( Day ( todaysDate ) ; 1 ; Year ( todaysDate ))

           

          )

           

          you'll cut the number of times the Get ( CurrentDate ) function is called in half. Won't make a difference for 1000 records. Might make a BIG difference for 1,000,000 records.

           

          Those are just some ideas. If you want specific help with your calculation, post it and we'll see what we can do.

           

          HTH

           

          Mike

          • 2. Re: Do a calculation (non indexed) find on server VS local
            petery009

            Thank you , Mike

             

            Those are great suggestions!

             

            We will do a test on your suggestions, and post the result once it's done!

            • 3. Re: Do a calculation (non indexed) find on server VS local
              petery009

              1) The auto-enter calculations require that the field and referenced calculation field have to be in the same table. In our case, they are in 2 tables.

               

              2) Deleting the extra field in the table, or separate them to a different table does not work for my case (at least i don't know how).

               

              3) It's not in my case!

               

              My case is this:

                I have one TableA in FileA,

                           one TableB in FileB(as a external database to FileA).

                A Field TableA::FieldA is a calculation field (= TableB::FieldB).

               

              I need to do a search on TableA::FieldA when startup the FileA.

              Hope it all make sense to you!

               

              Thanks!

              • 4. Re: Do a calculation (non indexed) find on server VS local
                Mike_Mitchell

                Petery,

                 

                Statement 1 is not true.

                 

                In your Manage Database dialog in TableA, go to the Options dialog for FieldA. Select the Auto-Enter tab, then choose "Calculated Value". Set the calculation to be "TableB::FieldB".

                 

                Now, if you need it to be updated on certain conditions, then you can set those conditions. For example, you can say:

                 

                Case ( not IsEmpty ( TableA::trigger ) ; TableB::FieldB )

                 

                Uncheck the box that says, "Do not replace existing value". Set FieldA::trigger to auto-enter some data value (like, for example, "1"). Then, when you need it to refresh, just do a Replace on the field TableA::trigger with that same non-empty value.

                 

                This will allow you to refresh the stored value in FieldA with whatever is stored in related TableB::FieldB whenever the value in TableA::trigger is not empty (i.e., doesn't change).

                 

                HTH

                 

                Mike

                • 5. Re: Do a calculation (non indexed) find on server VS local
                  petery009

                  Yeah, you are right , Mike

                   

                  So i did like you said, now the problem is the TableA::FieldA does not automatically change when the TableB::FieldB changes.

                   

                  Also what can i do to update TableA::FieldA when the recordB (TableB::FieldB is on) is deleted?

                   

                  Thanks!

                  • 6. Re: Do a calculation (non indexed) find on server VS local
                    Mike_Mitchell

                    In the first case, you can use a Script Trigger to update the necessary value in Table A when the value in Table B is updated. Set an OnModify trigger on the field in whatever layout the user is on when that field is updated and refresh the value in Table A.

                     

                    In the second case, you can use Custom Menus to replace the Delete command for Table B to do the same thing.

                     

                    Of course, we probably ought to back up in this case and ask a very basic question:

                     

                    Why are we copying the value of a field in one table into a field into another table? The whole point of relational data modeling is not to do that.

                     

                    Perhaps we ought to be examining why we think we need to do that in the first place?

                     

                    Mike

                    • 7. Re: Do a calculation (non indexed) find on server VS local
                      petery009

                      HI Mike

                       

                      What we tried to do is this: There's TableA in FileA, and TableB in FileB

                       

                      2 tables in 2 different files, and whenever the value changes in TableB, we want to use calculation field in Table A to get the updated value from TableB

                       

                      currently, if we delete the record in FileB (FileA closed), it will leave the empty record in FileA, so every time the FileA restart, we run the script to find the empty record(searched by the calculation field) and delete all of them.

                       

                      Problem is too slow on the server. It was ok with small database <1000 records, but when it reaches to >10000 records, the time that spent is not acceptable)

                       

                      I will try to customize the Delete script i currently have,

                       

                      whenever i delete a record from FileB, i will open the FileA(if it's closed) and go to that TableA do a refresh there! Sounds like gonna work!!

                       

                      Really appreciated for your help!!

                      • 8. Re: Do a calculation (non indexed) find on server VS local
                        Mike_Mitchell

                        That sounds like it will work.

                         

                        However, I still think it's worth asking the question: Why do we need to duplicate the value of a field in another file? Why not just show the value in File B on the layout in File A? You can do a search in File A and it will be just as fast as searching in File B (within reason). In a relational model, there's usually not a good reason for copying data between tables. Unless I'm missing something ...

                         

                        Mike

                        • 9. Re: Do a calculation (non indexed) find on server VS local
                          petery009

                          Hi Mike

                           

                          You are right, we did this in a complex way. more explanations will beyond this question!

                           

                          Thanks!