6 Replies Latest reply on Jun 23, 2011 11:17 AM by SwaroopVedula

    Efficient script

    SwaroopVedula

      Title

      Efficient script

      Post

      Hi,

      Hoping you might be able to help with ideas for writing efficient script for a specific task. The current script for the task described below takes 8 seconds when there is not much server traffic but up to 35 seconds when there is heavier server traffic.

      Three of the tables in my database and their fields (FM Pro Advanced 11), forming a many to many relationship are as follows:

      Abstracts: AbstractID (primary key), Title, Text, RandOrder, Number of Screen Responses (unstored, calculation field, may be empty for as yet unscreened abstracts). Records in this table are ordered by the RandOrder variable. Number of Screen Responses is an unstored, calculation field because the database has multiple users.

      Screeners: ScreenerID, AccountName, Last abstract screened, Current abstract

      Abstracts_Screeners Join table: AbstractScreenerID, AbstractID, ScreenerID, ScreenResponse, ScreenStartTime, ScreenEndTime

      Screen Layout: based on Abstracts, with a portal showing the related record from the Abstracts_Screeners Join table.

      Each abstract needs to be screened by exactly two different screeners. When a screener requests to screen an abstract, the script needs to do the following:

      1. Get AccountName for current user;

      2. Identify the RandOrder for the first abstract that has not been screened by at least 2 people;

      3. If the identified abstract has been previously screened by the current screener, identify the next abstract that has not been screened by at least 2 people (using the number of screen responses);

      4. Request a new record in the Abstracts_Screeners Join table, set the AbstractID for the abstract identified for screening, set the ScreenerID, set the ScreenStartTime;

      5. Display the abstract on the Screen Layout and the corresponding record from the Abstracts_Screeners Join table for the user to enter their screen response.

      Any ideas on the pseudocode and tips to make it efficient will be very much appreciated. Spent more than 2 days trying to make this work but still working to figure it out.

      Thank you!

      sved

        • 1. Re: Efficient script
          philmodjunk

          It would help to post your current script that is taking too long. That way we can better avoid suggesting a script that is the same or no better than what you are currently using.

          Is RandOrder a number field or a calculation?

          To post a script to the forum:

          1. You can upload a screen shot of your script by clicking the edit link to your original message and uploading it there
          2. You can upload a screen shot to a file share site and post the download link to a new response you post here.
          3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
          4. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
          5. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional look to your script.
          • 2. Re: Efficient script
            SwaroopVedula

            The script is pasted below. Thank you for the tips on printing the scripts. Helpful.

            The part of script that does not work well for me starts on line 45 below. Any thoughts will be appreciated. The names in the script may be different from those described in my original post, which I was trying to keep simple.

            Thank you.

            1. Set Variable [ $UserName; Value:Get ( AccountName ) ]
            2. Go to Layout [ “Screeners” (Screeners) ]
            3. Enter Find Mode [ ]
            4. Perform Find [ Specified Find Requests: Find Records; Criteria: Screeners::ScreenerUserName: “==$UserName” ] [ Restore ]
            5. If [ Screeners::LastAbstractScreened < 286 ]
            6. Set Variable [ $ScreenerID; Value:Screeners::ScreenerID ]
            7. Set Variable [ $LastAbstract; Value:Screeners::LastAbstractScreened ]
            8. Set Variable [ $CurrentAbstract; Value:$LastAbstract + 1 ]
            9. Go to Layout [ “PubLitAbstracts” (PubLitAbstracts) ]
            10. Enter Find Mode [ ]
            11. Perform Find [ Specified Find Requests: Find Records; Criteria: PubLitAbstracts::RandOrder: “==$CurrentAbstract” ] [ Restore ]
            12. Set Variable [ $AbstractIDvar; Value:PubLitAbstracts::AbstractID ]
            13. If [ PubLitAbstracts::FormAllocn = "Long" ]
            14. Set Variable [ $FormVar; Value:"Long" ]
            15. Else If [ PubLitAbstracts::FormAllocn = "Short" ]
            16. Set Variable [ $FormVar; Value:"Short" ]
            17. Else If [ PubLitAbstracts::FormAllocn = "Medium" ]
            18. Set Variable [ $FormVar; Value:"Medium" ]
            19. End If
            20. Go to Layout [ “Screeners” (Screeners) ]
            21. Show All Records
            22. Enter Find Mode [ ]
            23. Perform Find [ Specified Find Requests: Find Records; Criteria: Screeners::ScreenerUserName: “==$UserName” ] [ Restore ]
            24. Set Field [ Screeners::CurrentAbstract; $CurrentAbstract ]
            25. Commit Records/Requests [ No dialog ]
            26. Go to Layout [ “Abstracts_Screeners” (Abstracts_Screeners) ]
            27. New Record/Request
            28. Set Field [ Abstracts_Screeners::AbstractID; $AbstractIDvar ]
            29. Set Field [ Abstracts_Screeners::ScreenerID; $ScreenerID ]
            30. Set Field [ Abstracts_Screeners::AbstractScreenStartTime; Get ( CurrentHostTimeStamp ) ]
            31. If [ $FormVar = "Long" ]
            32. Go to Layout [ “TrainingAbstractsScreen_Long_C” (PubLitAbstracts) ]
            33. Else If [ $FormVar = "Short" ]
            34. Go to Layout [ “TrainingAbstractsScreen_Short_C” (PubLitAbstracts) ]
            35. Else If [ $FormVar = "Medium" ]
            36. Go to Layout [ “CompleteSetScreenAbstracts” (PubLitAbstracts) ]
            37. End If
            38. If [ $FormVar = "Long" ]
            39. Go to Field [ Abstracts_Screeners::LAbsScreenQ1 ]
            40. Else If [ $FormVar = "Short" ]
            41. Go to Field [ Abstracts_Screeners::SAbsScreenQ1 ]
            42. Else If [ $FormVar = "Medium" ]
            43. Go to Field [ Abstracts_Screeners::MAbsScreenQ1 ]
            44. End If
            45. Else If [ Screeners::LastAbstractScreened ≥ 286 ]
            46. Set Variable [ $ScreenerID; Value:Screeners::ScreenerID ]
            47. Go to Layout [ “PubLitAbstracts_ScreenResponses” (PubLitAbstracts) ]
            48. Enter Find Mode [ ]
            49. Perform Find [ Specified Find Requests: Omit Records; Criteria: PubLitAbstracts::NumberOfScreenResponses: “≥ 2” ] [ Restore ]
            50. Go to Record/Request/Page [ First ]
            51. Loop
            52. Exit Loop If [ IsEmpty ( PubLitAbstracts::NumberOfScreenResponses ) ]
            53. If [ PubLitAbstracts::NumberOfScreenResponses = 1 ]
            54. If [ Abstracts_Screeners::ScreenerID = $ScreenerID ]
            55. Go to Record/Request/Page [ Next; Exit after last ]
            56. End If
            57. Exit Loop If [ Abstracts_Screeners::ScreenerID ≠ $ScreenerID ]
            58. End If
            59. End Loop
            60. Set Variable [ $AbstractIDvar; Value:PubLitAbstracts::AbstractID ]
            61. Set Variable [ $CurrentScreenCompleteAbstract; Value:PubLitAbstracts::RandOrder ]
            62. If [ PubLitAbstracts::FormAllocn = "Long" ]
            63. Set Variable [ $FormVar; Value:"Long" ]
            64. Else If [ PubLitAbstracts::FormAllocn = "Short" ]
            65. Set Variable [ $FormVar; Value:"Short" ]
            66. Else If [ PubLitAbstracts::FormAllocn = "Medium" ]
            67. Set Variable [ $FormVar; Value:"Medium" ]
            68. End If
            69. Go to Layout [ “Screeners” (Screeners) ]
            70. Enter Find Mode [ ]
            71. Perform Find [ Specified Find Requests: Find Records; Criteria: Screeners::ScreenerUserName: “==$UserName” ] [ Restore ]
            72. Set Field [ Screeners::CurrentAbstract; $CurrentScreenCompleteAbstract ]
            73. Commit Records/Requests [ Skip data entry validation; No dialog ]
            74. Go to Layout [ “Abstracts_Screeners” (Abstracts_Screeners) ]
            75. New Record/Request
            76. Set Field [ Abstracts_Screeners::AbstractID; $AbstractIDvar ]
            77. Set Field [ Abstracts_Screeners::ScreenerID; $ScreenerID ]
            78. Set Field [ Abstracts_Screeners::AbstractScreenStartTime; Get ( CurrentHostTimeStamp ) ]
            79. If [ $FormVar = "Long" ]
            80. Go to Layout [ “TrainingAbstractsScreen_Long_C” (PubLitAbstracts) ]
            81. Show All Records
            82. Enter Find Mode [ ]
            83. Perform Find [ Specified Find Requests: Find Records; Criteria: PubLitAbstracts::AbstractID: “==$AbstractIDvar” AND PubLitAbstracts::RandOrder: “==$CurrentScreenCompleteAbstract” ] [ Restore ]
            84. Go to Field [ Abstracts_Screeners::LAbsScreenQ1 ]
            85. Else If [ $FormVar = "Short" ]
            86. Go to Layout [ “TrainingAbstractsScreen_Short_C” (PubLitAbstracts) ]
            87. Show All Records
            88. Enter Find Mode [ ]
            89. Perform Find [ Specified Find Requests: Find Records; Criteria: PubLitAbstracts::AbstractID: “==$AbstractIDvar” AND PubLitAbstracts::RandOrder: “==$CurrentScreenCompleteAbstract” ] [ Restore ]
            90. Go to Field [ Abstracts_Screeners::SAbsScreenQ1 ]
            91. Else If [ $FormVar = "Medium" ]
            92. Go to Layout [ “CompleteSetScreenAbstracts” (PubLitAbstracts) ]
            93. Show All Records
            94. Enter Find Mode [ ]
            95. Perform Find [ Specified Find Requests: Find Records; Criteria: PubLitAbstracts::AbstractID: “==$AbstractIDvar” AND PubLitAbstracts::RandOrder: “==$CurrentScreenCompleteAbstract” ] [ Restore ]
            96. Go to Field [ Abstracts_Screeners::MAbsScreenQ1 ]
            97. End If
            98. End If
            • 3. Re: Efficient script
              Kays

               

              before every "Perform Find" put "Set Error capture On".

              in lines 62 --> 68, why the IF...ELSE ... SET VARIABLE... !!, u could just use "Set Variable ($FormVar; PubLitAbstracts::FormAllocn ), the $FormVar will take whatever data existing in the "PubLitAbstracts::FormAllocn ", in this case u could change the whole 62 --> 68 line to just one line.

              K|Z

              • 4. Re: Efficient script
                SwaroopVedula

                Thank you, K|Z.

                PhilModJunk: Sorry I forgot to add that RandOrder is a number field.

                • 5. Re: Efficient script
                  philmodjunk

                  Lines 3, 10, 22, 48, 82, 88, 94 are redundant. You can remove them without changing the results you get. (If you use the restore option, you don't have to enter find mode before each Perform find.) This will likely not increase the speed at which the script executes by a noticeable amount, however.

                  Line 20 is redundant, you are already on that layout.

                  Line 21, 81, 87, 93 are redundant. Show All records has no effect here as your subsequent steps perform a find to pull up a different found set anyway.

                  Lines 13-19 can be simplified just like Kays has suggested for 62 - 68.

                  Lines 4, 23 and 71 perform the same find and I don't spot any steps that would change the results produced when this find is repeated. Instead of doing this, it's possible to perform the find only once by performing it in a separate hidden window or by using two different table occurrences of screeners with associated layouts for each. You can perform this find on one layout or window, then switch to the second layout or window for the second find. When you reach Line 23, switch back to return to the original found set, sort order and current record to modify the current abstract field without having to repeat the find.

                  Lines 31 - 44 can be combined into a single set of IF-Else If steps. Pair the go to layout and go to field steps together.

                  Line 45 can be changed to "Else" and the script will function the same, but with one less calculation to perform.

                  In Line 49, you specify search criteria for a table related to the current layout's table. If you can find a way (perhaps by using script triggers) to store this value in the current layout's table, this find will execute much faster--especially if the tables contain very large numbers of records.

                  Line 52 is redundant. Search citeria in Line 49 should eliminate any records that would cause the loop to exit on this step.

                  Loop in 51 - 59 would appear to trap you in an infinite loop if PubLitAbstracts::NumberOfScreenResponses = 2 and Abstracts_Screeners::ScreenerID = $ScreenerID for the first related record in Abstracts_Screeners

                  With a more sophisticated find on Line 49, the loop in 51 - 59 would become unecessary as you would no longer need to loop through the found set until you reach the first record where the field in the related record, Abstracts_Screeners::ScreenerID ≠ $ScreenerID.

                  Since the Long, Medium and Short layouts are all based on the same table occurrence, you can perform the find first (on any layout where PubLitAbstracts is specified in the layouts "show records from" drop down) then switch layouts. This won't execute any faster, but will shorten and simplify your script. You can also use the "layout name by calculation option" to change layouts in this script when selecting "long", "medium" or "short" layouts instead of using a combination of IF and Go to Layout steps.

                   

                   

                  • 6. Re: Efficient script
                    SwaroopVedula

                    Many thanks for your suggestions and for taking the time, PhilModJunk! I will post an update after revising my script based on your comments.