Managed File Transfer

 View Only
Expand all | Collapse all

Long running SQL Queries found in myFileGateway

  • 1.  Long running SQL Queries found in myFileGateway

    Posted Tue March 09, 2021 03:51 PM
    Hi all,

    While trying to resolve a performance issue with myFileGateway, we found very long running SQL queries. The customer is using MS SQL.

    Please refer to the attached SQL result_sql_checking.xlsx for the full query. The first queries is taking total_worker_time 983654062.

    Please advise if there is any way to improve the timing.

    Thank you.

    ------------------------------
    TIONG GHEE TAN
    ------------------------------

    #filetransfer
    #DataExchange

    Attachment(s)

    txt
    SQL Checking.txt   2 KB 1 version
    xlsx
    result sql checking.xlsx   87 KB 1 version


  • 2.  RE: Long running SQL Queries found in myFileGateway

    Posted Wed March 10, 2021 07:56 AM
    Hi Tiong Ghee Tan,

    All the tables seem to be the sqlserver-related tables used for some audit purposes (events, sessions etc.) except workflow_context.

    For workflow_context, you can improve the sql performance by optimizing the steps, decreasing the persistence, and redesigning the business processes. There are other advanced techniques to improve the workflow context sql performance, but this should be the first starting point.

    What version of B2BI/MSSQL are they using? Is MSSQL an on-prem-based software or cloud service?

    Kishore

    ------------------------------
    KALYAN PAVAN KISHORE CHAKRAVARTHULA
    ------------------------------

    Original Message


  • 3.  RE: Long running SQL Queries found in myFileGateway

    Posted Wed March 10, 2021 08:50 AM
    Edited by System Wed March 22, 2023 11:45 AM
    Hi Kishore,

    The customer is running B2B 6.1 and MS SQL 2019 (on-prem).

    Thank you for your advise.

    ------------------------------
    TIONG GHEE TAN
    ------------------------------

    Original Message