11 Replies Latest reply on Feb 14, 2020 11:56 AM by Sinisa Mikor

    Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?

    Ellen Ashe
      Share This:

      Have timeout issues happening now, same issue we have seen in the past, on searches of the Process Definition Template and Application Object Template forms using the SRD:SR_L6_PDTsAOTs_SRD_Join formIn the past we've been able to archive data from the SRM:ProcessAOTSummaryData form and reconfigure the indexes to work more efficiently on the join form SRD:SR_L6_PDTsAOTs_SRD_Join in order to get searches working again.

       

      However, we seem to have reached a point where the number of records is still too high even though the data is being regularly archived out of the SRM:ProcessAOTSummaryData form.

      Has anyone else seen this issue or successfully resolved it?

       

       

        • 1. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
          Jason Miller

          There are many levels of joins being used in your query. Different, but similar, BMC doesn't recommend going crazy with regular sub classes in the CMDB for the same reason.

           

          What is it you are getting from this data?

           

          If there is no alternative and you need to pull data all of the way down to a level 6 join, it may be time to build some custom SQL views that can be better optimized. Join forms in Remedy can be powerful but they are not able to take advantage of all of the native DB features needed for extremely complex operations.

          • 2. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
            Ellen Ashe

            Hi Jason,

             

            I think your suggestion of a custom SQL view is likely what will need to happen in our case. 

             

            For both forms we are accessing them to modify a template.  On the Application Object Template we are searching for an existing AOT and when choosing the 'Add Target Data' the search calls that view and times out.  For the Process Definition Template it's just a search/load of a template that calls the view and times out.

             

            Thanks,

            Ellen

            • 3. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
              Nitesh Kumar

              Hi Ellen,

               

              Frankly talking I've not come across such issue though one probable solution what I am thinking that you can resolve this error by modifying the search to return fewer records if you are dealing with so big chunk of SRDs records. If the error occurs during a workflow search (can be look by taking client side API+SQL log), you might need to modify the workflow actions or adjust the server settings to return fewer records.Ask your database administrator for help, and review performance of the database. The performance of the AR System server during database updates depends on the performance of the database. Work closely with database support to ensure optimum database tuning.

               

              Regards,

              Nitesh

              • 4. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                Sinisa Mikor

                Hi Ellen,

                 

                is Set Fields action in active link SRM:SCB:AddTargetData_CheckIfRelateToOnlineSRD the one that times out while attempting to Add Target Data?

                If so, it uses the following qualification to determine whether there are any active SRDs using AOT you are attempting to modify.

                 

                    (

                          'L1_AOTOrProcess_InstanceID' = $AOTInstanceID$ OR

                          'L2_AOTOrProcess_InstanceID' = $AOTInstanceID$ OR

                          'L3_AOTOrProcess_InstanceID' = $AOTInstanceID$ OR

                          'L4_AOTOrProcess_InstanceID' = $AOTInstanceID$ OR

                          'L5_AOTOrProcess_InstanceID' = $AOTInstanceID$ OR

                          'L6_AOTOrProcess_InstanceID' = $AOTInstanceID$

                    ) AND

                    'TakeSRDOfflineFlag' = "Online"

                 

                You wouldn't happen to know how many records at each level, or in total it would have to process?  Splitting this action into a series of six may solve your issue because then only the amount of any single level should influence query

                 

                     1. IF

                               'L1_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                     2. IF

                               $z1D Char01$ = $NULL$ AND

                               'L2_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                     3. IF

                               $z1D Char01$ = $NULL$ AND

                               'L3_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                     4. IF

                               $z1D Char01$ = $NULL$ AND

                               'L4_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                     5. IF

                               $z1D Char01$ = $NULL$ AND

                               'L5_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                     6. IF

                               $z1D Char01$ = $NULL$ AND

                               'L6_AOTOrProcess_InstanceID' = $AOTInstanceID$ AND

                               'TakeSRDOfflineFlag' = "Online"

                          THEN

                               z1D Char01 = $SRD_Number$

                 

                2 of 2 people found this helpful
                • 5. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                  Jason Miller

                  Oh wow! It didn't occur to me that you are simply trying to use out of the box functionality. I figured you had some fancy report or custom workflow process.

                   

                  While I suppose you could create a custom view (per my initial recommendation) and replace/update the current OOTB workflow to check your custom view form, that is a bit of effort for something that should just work.

                   

                  Have you submitted this to BMC Support by any chance? I am curious if they had any feedback.

                  • 6. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                    Ellen Ashe

                    Hi Jason,

                    Yes, we are still trying to use the OOTB workflow at this point.  BMC's response was:

                    I'm afraid there are only three ways to improve this:

                    1.- To ask your DBA team to add indexing in the provided queries.     (Our DBA has already optimized the indexing)
                    2.- Adjust the archival days to a short rank                                            (We currently keep 2 years worth of data with our archiving)
                    3.- Delete the data manually (a bit risky) and not recommended.

                    Thanks,

                    Ellen

                    • 7. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                      Ellen Ashe

                      Hi Sinisa,

                       

                      Yes, it would be the SRM:SCB:AddTargetData_CheckIfRelateToOnllineSRD on AOT and SRD:PDT:VIS_CheckifRelatedToOnlineSRD on the PDT form. I haven't checked the number of records per level, but I'll check and try out your suggestion to see if we're able to use it that way with our amount of data successfully.

                       

                      We have 17 million records in the SRM_ProcessOATSummaryData from,  little over 12 million of them are Closed, but are still within the 2 year of archived data kept.  This is the form used in the join causing us the issues.

                       

                      Thanks,

                      Ellen

                      1 of 1 people found this helpful
                      • 8. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                        Jason Miller

                        I like where you are going with this. To take it even further (assuming Support doesn't give an acceptable solution and you need to find your own solution), if Ellen Ashe is going to start modifying BMC's OOTB code to make this work in their environment, I also recommend converting this into service filters vs. active link workflow. Filters are going to give better performance because they'll keep the processing on the server-side (where the network pipes between AR and DB should be very large/quick).

                         

                        Not having looked at the workflow behind the button until this morning, the first thing that comes to mind is to create a custom Display Only form to work as your "service processor" form. On that new form, create some filters that will search for online records similar to the OOTB active link (you might as well split up the work up like Sinisa Mikor suggested). Put those filters in a Filter Guide in the correct order and then have one filter on that form that fires on Service to execute the guide. Then all you need to do is disable the current SRM:SCB:AddTargetData_CheckIfRelateToOnllineSRD active link and create a replacement active link that will call your new service form and pass relevant data when the button is pressed. Your service should return a value to the 'z1D Char01' field via the new active link if any records were found "online".

                        1 of 1 people found this helpful
                        • 9. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                          Jason Miller

                          I just posted a reply that I had been working on for a little bit with what I believe is a better option what you were provided. IMHO the OOTB workflow should be updated to use filters instead of active links but you can do it yourself since that is unlikely to happen any time in the near future.

                          • 10. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                            Jason Miller

                            One thought about DB and performance... Many people make the assumption that their DB is already setup this way (or don't even know it should be) and I surprising have found this isn't always the case, make sure the DB files and the TLogs are stored on different LUNs that will not affect the performance of each other.

                             

                            When Remedy is first installed (assuming you didn't pre-create the DB and separate the files ahead of time) it will store the main DB file and transaction logs on the same disk (typically how your DBMS defaults for new DBs). This could be the case in your environment unless somebody purposely move the TLog file to a different disk. If the main DB file(s) are on the same disk as the TLogs, they are competing for I/O resources and TLogs are typically very active (depending on how your DB is setup).

                            1 of 1 people found this helpful
                            • 11. Re: Process Definition Template and Application Object Template search timeouts.  Does anyone have a solution?
                              Sinisa Mikor

                              Am I the only one who finds it strange that mentioned active link goes through up to seven levels of summary data form (L1 starts with two and each new level tries to add another join to that form), when it is basically only supposed to prevent changing target data while any SRD which uses that AOT it is still submissible?

                               

                              If all SRDs where AOT is used were (manually) offlined, regardless of how many unresolved processes had been started by them, modifying application target data would be allowed, so why does it even bother with active instances if it isn't aimed at preventing modification of target data (definition) as long as there are any not (yet) completed instances of that AOT present in any already activated process?

                               

                              After taking a look at how SRM:SR_Ln_PDIsAOIs were constructed, my previous suggestion does not look that helpful.  If the goal was not to allow modifying still active processes, looking for Template InstanceId of AOT with appropriate set of Status values in base form used in all those joins (SRM:ProcessAOTSummaryData) would produce a set of SR instance IDs in Root Request Instance ID so that all relevant service requests could be located in SRM:Request; else, while online SRDs can be matched to service requests from previous step, it is a lot easier to detect those by following associations between SRDs, PDTs and AOTs.