1 2 Previous Next 20 Replies Latest reply on Aug 4, 2009 2:46 PM by jspurr02

    Need to get control of long running script

    jspurr02

      Title

      Need to get control of long running script

      Post

      I have a script that has now been running about 34 hours.  I stopped it after 8 hours and added the Freeze Window (and restarted where it left off), in hopes of speeding it up.  I figured it should have finished in 30-32 hours without the Freeze Window change (after 8 hours, it had completed 45,000 out of 162,000 records), so I had expected it to finish sooner.

       

      The problem is that I cannot get the FM window to come up, (even though it is in the task bar), and do not know how to get control of the script again.

       

      I would like to stop/pause so I can try to figure out how far it is.  How can I do this?

        • 1. Re: Need to get control of long running script
          mrvodka
            

          You can always kill the process but it can *possibly corrupt data.

           

          BTW have what is your script? Have you tried layouts with nothing on them?

           

          That seems like a very long tiem for me for such few records.

          • 2. Re: Need to get control of long running script
            jspurr02
              

            Thanks ... if I kill the process ... won't I lose the data?  It is not committed, yet.

             

            The script is migrating data from an import staging table to 7 other tables.

             

            These are items I suspect may be slow:

             

            --Two of the tables require uniqueness, so each input record processed does a find in 2 tables to make sure a duplicate record does not already exist.  (I imagine this could be slow)

            --There are a number of calculated fields in 2 of the tables, some are quite complex

             

            The abridged version of the script looks like this:

             

            Go to Layout [ “Import_Master_Table” (Import_Master) ] //** This is the staging import table **//
            Go to Record/Request/Page [ First ]

            Loop
            Set Variable [ $CN_FirstName; Value:Import_Master::First Name ]
            Set Variable [ $CN_LastName; Value:Import_Master::Last Name ]
            Set Variable [ $CN_ContactID; Value:Import_Master::ContactID ]

            (etc)


            Go to Layout [ “IM_Contacts” (IM_Contacts) ]
            Enter Find Mode [ ]

            Set Field [ IM_Contacts::ContactID; "==" & $CN_ContactID ]
            Perform Find [ ]
            Set Variable [ $CN_FoundCount; Value:Get ( FoundCount ) ]
            If [ $CN_FoundCount=0 ]
            --New Record/Request
            --Set Field [ IM_Contacts::First Name; $CN_FirstName ]
            --Set Field [ IM_Contacts::Last Name; $CN_LastName ]
            --Set Field [ IM_Contacts::ContactID; $CN_ContactID ]

            (etc)

            --Go to Layout [ “IM_Companies” (IM_Companies) ]
            Enter Find Mode [ ]
            Set Field [ IM_Companies::CompanyID; "==" & $CN_CompanyID ]
            Perform Find [ ]
            Set Variable [ $CN_FoundCount; Value:Get ( FoundCount ) ]
            If [ $CN_FoundCount=0 ]
            --New Record/Request
            --Set Field [ IM_Companies::CompanyName; $CN_CompanyName ]
            --Set Field [ IM_Companies::CompanyZip; $CN_CompanyZip ]
            --Set Field [ IM_Companies::CompanyPhone; $CN_CompanyPhone ]



            --Set Field [ IM_Companies::CompanyID; $CN_CompanyID ]

            (etc)

            Else

            --Go to Layout [ “IM_A” (IM_A) ]
            --New Record/Request
            --Set Field [ IM_A::ContactID; $CN_ContactID ]
            --Set Field [ IM_A::CompanyName; $CN_CompanyName ]
            --Set Field [ IM_A::CompanyID; $CN_CompanyID ]

            --Go to Layout [ “IM_B” (IM_B) ]
            --New Record/Request
            --Set Field [ IM_B::ContactID; $CN_ContactID ]
            --Set Field [ IM_B::CompanyZip; $CN_CompanyZip ]
            --Set Field [ IM_B::CompanyID; $CN_CompanyID ]

            --Go to Layout [ “IM_C” (IM_C) ]
            --New Record/Request
            --Set Field [ IM_C::ContactID; $CN_ContactID ]
            --Set Field [ IM_C::CompanyZip; $CN_CompanyPhone ]
            --Set Field [ IM_C::CompanyID; $CN_CompanyID ]


            End If

            Go to Layout [ “Import_Master_Table” (Import_Master) ]
            Go to Record/Request/Page [ Next; Exit after last ]
            End Loop

             

             





            • 3. Re: Need to get control of long running script
              jspurr02
                

              Note:  left out an EndIf right after the one that is near the bottom.

               

              • 4. Re: Need to get control of long running script
                comment_1
                  

                jspurr02 wrote:

                Two of the tables require uniqueness, so each input record processed does a find in 2 tables to make sure a duplicate record does not already exist.


                Why don't you validate the fields as Unique, Validate always - that will prevent duplicates from importing.


                • 5. Re: Need to get control of long running script
                  jspurr02
                    

                  Thanks - I was thinking about that at one point.  I could perhaps set import table field validation to be unique for ContactID, although I would have to keep all data in that staging table forever, since it is possible for subsequent imports to have duplicates from previous imports.  I was planning to flush the import staging table between imports.  I would have to modify the script to only migrate new data, then.

                   

                  I would still have to do the checking for the Company Table, since the import should allow contacts at multiple companies ... but the company table should only have one record for each company.

                   

                  Plus, I have a need to have the ability to log duplicates for diagnostic / root cause analysis (this is what the A B C tables are for, in part)

                   

                  I am still looking for a (relatively) safe way to get control of, or at least determine the progress of my in-progress script.  Any ideas?

                  • 6. Re: Need to get control of long running script
                    comment_1
                      

                    I don't see why you couldn't validate in the "real" tables - but I haven't examined your script too closely.

                     

                     

                    I am not quite sure what you mean by "control" - perhaps you could check a global field/variable, say every thousand records, and exit if it's true. Have a button that can set it true when you want to abort.

                    Similarly, you could update a global field re the progress at the same time.

                     

                    ---

                    This assumes you're looping - something I would try to avoid altogether.

                    • 7. Re: Need to get control of long running script
                      jspurr02
                        

                      Thanks for the response.

                       

                      Regarding my immediate problem:  When I say "control", I am being generic -- but this script is running 'out of control'.  In other words, it is now going on 37 hours, and I have no way to pause it or stop it gracefully that I know of.  I can't get a FMP window open, and it just keeps running and running.  I don't want to kill it, for fear it will corrupt the data -- also, early tests suggested it would run 30-32 hours, so it is not too far out of line yet.  Your suggestions are great ones ... for future runs of the script, but I need to deal with the current execution somehow.

                       

                      Regarding 'avoiding looping' ... what would be an alternate strategy for migrating 100,000+ records from an import staging table into 7 different tables?

                       

                       

                      • 8. Re: Need to get control of long running script
                        comment_1
                          

                        Can't you abort the script by pressing Esc or Command+period? If yes, what's the worst thing that could happen?

                         


                        jspurr02 wrote:
                        Regarding 'avoiding looping' ... what would be an alternate strategy for migrating 100,000+ records from an import staging table into 7 different tables?

                        7 imports.


                        • 9. Re: Need to get control of long running script
                          jspurr02
                            

                          "Esc" is how I stopped it at 8 hours ... it worked well ...  but then I had a window open.

                           

                          I went and added Freeze Window, thinking I would get a performance boost, but somewhere in the process, I lost the window.

                           

                          It shows in the task bar, but I can't get it open -- so, there is no place to do esc or command+.    :-(

                          • 10. Re: Need to get control of long running script
                            comment_1
                               It seems like a Windows issue to me (i.e. out of my jurisidiction).
                            • 11. Re: Need to get control of long running script
                              mrvodka
                                

                              I was going to respond to you earlier before I got caught up with other things to recommend exactly what comment has already told you. I also agree that imports is what you should probably use.

                               

                              As for your FM, can you right click on the FM app in the task bar and maximize?

                              • 12. Re: Need to get control of long running script
                                jspurr02
                                  

                                Hey Hey, Mr Vodka -- Thanks for the suggestion.  The server UI is set to 'group similar" in the task bar, so "maximize" is not a right-click option (go figure!!) --there was a FM Help window grouped with it.  I had previously assumed simply selecting it from the group should work (you know what they say about 'assume' ... in this case it only reflects on ME) -- but your statement inspired me to reconfigure the task bar ... and then "Maximize" was available, and it worked!

                                 

                                ... so I will let it run a little longer, and then esc, if need be.

                                 

                                Other Note ... importing directly to the differentg tables will require that I prep all the files externally, manually, in Excel.  One of the benefits I was hoping for out of a migration to FMP was to automate my processes.  I don't much care if it takes a bit longer if I don't have to continuously interact with it ... but 30+ hours is excessive.

                                • 13. Re: Need to get control of long running script
                                  comment_1
                                    

                                  jspurr02 wrote:
                                  importing directly to the differentg tables will require that I prep all the files externally, manually, in Excel. 

                                  That is not what I meant. You can import into a temp table, do all the required pre-processing there, then import from temp into the final destinations.


                                  • 14. Re: Need to get control of long running script
                                    jspurr02
                                      

                                    OK ... here is where my noob is showing:  I sorta thought that is what I was doing.  My code is near the top of this thread.  How would it be different?

                                     

                                    ("temp table" = Import_Master)

                                     

                                    I do have calculated fields in the target tables (IM_Contacts; IM_Companies) ... so I could move the calcs to Import_Master, and add more Set Variable/Set Field instructions to move the Calculated fields rather than calculate in the Targets ... would that be faster ... or do you mean something completely different that I am totally unfamiliar with?

                                    1 2 Previous Next