7 Replies Latest reply on Feb 10, 2010 12:04 PM by SCOPe

    Set Variable Performance Hit

    SCOPe

      Title

      Set Variable Performance Hit

      Post

      Hello,

      First off here are some details of my current setup environment:

       


      FileMaker Pro Advanced 10.0v1
      Windows XP Professional Service Pack 3

      Database Details:

      • FileMaker Server 10
      • 24 Databases
      • 67 Average Connected Users
      • Majority of clients connected through Citrix Xenapp

       

      Question/Problem:

       

      I currently have a button that performs a script that does the following:

       

      1. Based on a "access level" variable in another database, it performs different finds. 
        1. This access level is assigned to a local variable within the script
        2. Then If/ElseIf statements are used to compare this variable to see which find to use.

      My problem is, when I run this script it has a 3-4second lag time for the variable to be assigned.

       

      Also to note, this information is in a separate database.

       

      So...

       

      1. Is there a faster way to improve this performance?
      2. Is there a better way of doing this?

       

       


        • 1. Re: Set Variable Performance Hit
          philmodjunk
            

          Variables are generally pretty fast. It's possible that the expression you use to compute the value assigned to the variable is taking a lot of time to evaluate. If you like, post the expression here (and if the expression references fields that are defined as calculations, or summary fields provide those details also.)

           

          References to summary fields that summarize large record sets and unstored calculations are two very likely explanations for your delay.

           

          PS. I'd also download the free updater to update your copy of filemaker to 10.0v3--that update fixes a few bugs and returns filemaker's ability to import from .dbf files.

          • 2. Re: Set Variable Performance Hit
            SCOPe
              

            PhilModJunk wrote:

            Variables are generally pretty fast. It's possible that the expression you use to compute the value assigned to the variable is taking a lot of time to evaluate. If you like, post the expression here (and if the expression references fields that are defined as calculations, or summary fields provide those details also.)

             

            References to summary fields that summarize large record sets and unstored calculations are two very likely explanations for your delay.

             


             

             

            Set Variable[$BIPAccess; Value:BIPAccess:BIP Access

             

            BIPAccess is a table reference to a different database that contains the "BIP Access" field

            • Also the relationship for BIPAccess to the current database is as follows:

            Current Database->"Projects Database"->BIPAccess Database

             

             

            BIP Access Field Info:

            • Type: Text
            • Storage: Indexing: None ( Just noticed this, would this effect the automatic indexing?)
              •  Automatically create indexes as needed



            • PhilModJunk wrote:

              PS. I'd also download the free updater to update your copy of filemaker to 10.0v3--that update fixes a few bugs and returns filemaker's ability to import from .dbf files.


              Downloading that now!





            • 3. Re: Set Variable Performance Hit
              philmodjunk
                

              Well that ruled out some obvious possibilities.

               

              Do your relationship details look like this?

               

              Current Database::Keyfield = Projects Database::keyfield

              Projects Database::keyfield = BIPAccess Database::keyfield

               

              I'm looking to see if they're "=" operators and whether there are any additional complexities here in terms of the key fields

               

              Are these three different files here, one for each database?

               

              Are the files already open at this point or is this the first script step to reference the file and cause them to open? If so, the delay may be the time that it is taking filemaker to open the files.

              • 4. Re: Set Variable Performance Hit
                SCOPe
                  

                PhilModJunk wrote:

                Well that ruled out some obvious possibilities.

                 

                Do your relationship details look like this?

                 

                Current Database::Keyfield = Projects Database::keyfield

                Projects Database::keyfield = BIPAccess Database::keyfield

                 

                I'm looking to see if they're "=" operators and whether there are any additional complexities here in terms of the key fields


                Yes the relationships are exactly as you defined them, all "=" relationships with no other fields involved.

                 


                PhilModJunk wrote:

                Are these three different files here, one for each database?

                 

                Are the files already open at this point or is this the first script step to reference the file and cause them to open? If so, the delay may be the time that it is taking filemaker to open the files.


                Yes each database is a separate file, unfortunately this is a setup that was done back in FileMaker 5-6 and instead of tables being setup, separate files were.

                 

                We are using an "opener file" to startup and open several databases, and these are all open well before this script is executed.

                 



                • 5. Re: Set Variable Performance Hit
                  SCOPe
                    

                  One other thing to add as well:

                   

                  If I perform this script, I see this 3-4 second delay. If I perform the script again, it's almost instant.

                   

                  This leads me to believe that it might be something in regards to indexing? Should this BIPAccess field be set to index all? I'll give that a try and let you know if this solves anything.

                   

                   

                   

                  **UPDATE**

                   

                  I changed the BIPAccess field to Index: All

                   

                  This didn't change the performance at all. Still a big delay when the script is first ran. Then after 1-2 times it's instant untill I shutdown FileMaker and reconnect.

                  • 6. Re: Set Variable Performance Hit
                    philmodjunk
                      

                    It's hard to say what else might be a factor without actually examining a copy of your files.

                     

                    "Yes each database is a separate file, unfortunately this is a setup that was done back in FileMaker 5-6 and instead of tables being setup, separate files were."

                     

                    That might be a clue. Old filemaker files often have very screwy external data source references and there could be a delay here even though you already have the files open. Have you checked to see what you have listed in Manage | External Data Sources... ? Your file might be trying a whole series of out of date file references before it resolves down to one that works. A reference that includes asterisks will be especially slow and should be edited to replace such references with relative path, IP Address or DSN references.

                    • 7. Re: Set Variable Performance Hit
                      SCOPe
                        

                      Interesting...

                       

                      So we have this .fp7 that is our "Opener Database" and it has External Data Sources defined as the following:

                       

                       

                      fmnet:/localIP/PROJECTS fmnet:/OutsideIP/PROJECTS

                       

                       

                      Where LocalIP and OutsideIP represent IPv4 IP's to the Filemaker Server.

                       

                       

                      This Database has a script that opens the "Projects" Database

                      This Database has all the other databases in it's External Data Sources as relative paths, as so:

                       

                      file:DatabaseName

                       

                      The Projects Database then has a "Startup" script that is called when this database is open, which opens all of the other databases.